[jira] [Created] (CALCITE-6416) Remove unnecessary SUBSTRING rewrite in SparkSqlDialect

2024-05-22 Thread xiong duan (Jira)
xiong duan created CALCITE-6416:
---

 Summary: Remove unnecessary SUBSTRING rewrite in SparkSqlDialect
 Key: CALCITE-6416
 URL: https://issues.apache.org/jira/browse/CALCITE-6416
 Project: Calcite
  Issue Type: Improvement
  Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
 Fix For: 1.38.0


SparkSqlDialect have a unnecessary rewrite about SUBSTRING func.

In CALCITE-3247, We handle the SUBSTRING rewrite in HiveSqlDialect.

In CALCITE-3072, We handle the SUBSTRING rewrite in SparkSqlDialect.

In CALCITE-5677, We refactor the SUBSTRING as the default behaviour and remove 
the SUBSTRING rewrite in HiveSqlDialect. This PR will remove the Spark.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)


[Question] Left Semi Join and SqlToRelConverter

2024-05-22 Thread JinxTheKid
Hi all,

I'm exploring extensions to Calcites parser and saw that the Babel parser
impl has some nice things I want to work with, namely support for
Spark-style LEFT_SEMI_JOINs syntax. Using this parser works for me, but I
noticed that when using SqlToRelConverter, converting LEFT_SEMI_JOIN is not
implemented. This seems strange to me, since there is both a
JoinType.LEFT_SEMI_JOIN, and a RelJoinType.SEMI. I'm aware that I could
always write the query using a different syntax (EXISTS, or IN), but was
curious if there is a "Calcite way" of achieving my desired behavior before
writing my own logic. Is there a way to convert queries with
LEFT_SEMI_JOINs into a RelNode, and is this gap intentional?

Thanks,
Logan


[jira] [Created] (CALCITE-6415) Invalid unparse for TIMESTAMP with HiveSqlDialect

2024-05-22 Thread xiong duan (Jira)
xiong duan created CALCITE-6415:
---

 Summary: Invalid unparse for TIMESTAMP with HiveSqlDialect
 Key: CALCITE-6415
 URL: https://issues.apache.org/jira/browse/CALCITE-6415
 Project: Calcite
  Issue Type: Improvement
  Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
 Fix For: 1.38.0


When parsing
{code:java}
SELECT CAST("2023-11-10" AS TIMESTAMP) {code}
The unparsed Hive SQL query gives:
{code:java}
SELECT CAST("2023-11-10" AS TIMESTAMP(0))  {code}
 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)


RE: Re: CoreFilters.FILTER_REDUCE_EXPRESSIONS issue

2024-05-22 Thread jeffreywinter
> You say that
>…
> isn't valid SQL. What's wrong with it? It looks valid and correct to
> me.

Right, this was phrased poorly. I assume this is valid ANSI SQL, but it is not 
for the versions of Oracle and SQL Server I’m currently working with. To this 
point I haven’t run into any issues with the generated SQL that I haven’t been 
able to readily work around.

It’s unclear to me what the approach is to handling this sort of situation. Do 
I simply remove that rule when targeting these specific data sources? Can the 
rule be configured in some manner to produce the SQL valid for those targets?

Thanks

On 2024/05/21 18:32:42 Julian Hyde wrote:
> You say that
>
> SELECT code, CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END AS absval
> FROM TEST
> WHERE CASE WHEN code = 'test_val' THEN ABS(val) > 0 ELSE FALSE END
>
> isn't valid SQL. What's wrong with it? It looks valid and correct to
> me. (I acknowledge that it's neither pretty nor optimal.)
>
> To make the query more pretty and optimal, it would be nice if Calcite
> did a couple of simplifications:
>
> 1. Simplify CASE to AND. "CASE WHEN code = 'test_val' THEN ABS(val) >
> 0 ELSE FALSE END" --> "code = 'test_val' AND ABS(val) > 0". This
> rewrite is valid only if the expressions are pure, can't throw, and
> NULL values don't matter, all true in this case.
>
> 2. Reduce constants. "CASE WHEN code = 'test_val' THEN ABS(val) ELSE
> NULL END AS absval" becomes "ABS(val) AS absval" because the WHERE
> clause has ensured that "code = 'test_val'" is always true.
>
> I believe there's a rule for 2 but not for 1.
>
> Julian
>
> On Tue, May 21, 2024 at 11:18 AM  wrote:
> >
> > I'm having an issue with CoreFilters.FILTER_REDUCE_EXPRESSIONS. I'm 
> > wondering if this is a problem with the rule, or with how I've set up my 
> > logical tree.
> >
> > Conceptually I am trying to use the equivalent of a computed column in a 
> > WHERE. Since this isn't legal:
> >
> > SELECT code, CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END AS 
> > absval
> > FROM TEST
> > WHERE absval > 0
> >
> > I need to move that same CASE logic into the WHERE clause, so:
> >
> > SELECT code, CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END AS 
> > absval
> > FROM TEST
> > WHERE CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END > 0
> >
> > I'm able to set up my logical RelNode tree and generate exactly this SQL 
> > directly from the logical tree. The builder logic has:
> >
> > RelNode relNode = builder
> > .projectPlus(builder.alias(caseNode, "ABSVAL"))
> > .filter(builder.greaterThan(caseNode, builder.literal(0)))
> > .build();
> >
> > However, when I try to generate a physical tree from this, I can see the 
> > FILTER_REDUCE_EXPRESSIONS rule coerses the CASE statement in the filter 
> > into a BOOLEAN expression, and it produces:
> >
> > SELECT code, CASE WHEN code = 'test_val' THEN ABS(val) ELSE NULL END AS 
> > absval
> > FROM TEST
> > WHERE CASE WHEN code = 'test_val' THEN ABS(val) > 0 ELSE FALSE END
> >
> > Which isn't even valid SQL. If I remove that rule from the planner 
> > altogether:
> >
> > planner.removeRule(FILTER_REDUCE_EXPRESSIONS)
> >
> > The SQL looks correct (effectively the same as that translated directly 
> > from the logical tree above.)
>