[jira] [Commented] (CALCITE-6516) AVG to SUM+COUNT transformation inserts invalid CAST call
[
https://issues.apache.org/jira/browse/CALCITE-6516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17877683#comment-17877683
]
Leonid Chistov commented on CALCITE-6516:
-
[~julianhyde]
I haven't found any related Jira issues.
I propose to fix current issue just by removing `ensureType` call. I cannot
imagine any real need to make this call.
https://github.com/apache/calcite/pull/3940
> AVG to SUM+COUNT transformation inserts invalid CAST call
> -
>
> Key: CALCITE-6516
> URL: https://issues.apache.org/jira/browse/CALCITE-6516
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
>
> As described in https://issues.apache.org/jira/browse/CALCITE-4911.
> In Calcite:
> {code:java}
> select avg(comm) as a from "scott".emp{code}
> This SQL physical plan is:
> {noformat}
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
> expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
> expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
> expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
> EnumerableTableScan(table=[[scott, EMP]]){noformat}
> Problem here is that cast of SUM result to DECIMAL(7, 2) is not correct and
> may cause overflow error at runtime or result in not expected data truncation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6516) AVG to SUM+COUNT transformation inserts invalid CAST call
[
https://issues.apache.org/jira/browse/CALCITE-6516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17871575#comment-17871575
]
Leonid Chistov commented on CALCITE-6516:
-
[~mbudiu]
Yes, thanks, with latest main I am able to reproduce it with following test:
{code:java}
select avg(comm2), sum(comm2), count(comm2) from (select cast(comm * 70 as
decimal(7, 2)) as comm2 from "scott".emp);
+--+---++
| EXPR$0 | EXPR$1| EXPR$2 |
+--+---++
| 38500.00 | 154000.00 | 4 |
+--+---++
(1 row)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
expr#8=[CAST($t7):DECIMAL(7, 2)], EXPR$0=[$t8], EXPR$1=[$t5], EXPR$2=[$t1])
EnumerableAggregate(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[70], expr#9=[*($t6, $t8)],
expr#10=[CAST($t9):DECIMAL(7, 2)], COMM2=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan {code}
Execution result is:
{code:java}
java.lang.ArithmeticException: Value 154000.00 cannot be represented as a
DECIMAL(7, 2) {code}
[~suibianwanwan33]
Yes, I agree with your analysis. I am also not sure ATM if fix that you propose
can create other problems.
> AVG to SUM+COUNT transformation inserts invalid CAST call
> -
>
> Key: CALCITE-6516
> URL: https://issues.apache.org/jira/browse/CALCITE-6516
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: Leonid Chistov
>Priority: Major
>
> As described in https://issues.apache.org/jira/browse/CALCITE-4911.
> In Calcite:
> {code:java}
> select avg(comm) as a from "scott".emp{code}
> This SQL physical plan is:
> {noformat}
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
> expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
> expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
> expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
> EnumerableTableScan(table=[[scott, EMP]]){noformat}
> Problem here is that cast of SUM result to DECIMAL(7, 2) is not correct and
> may cause overflow error at runtime or result in not expected data truncation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6516) AVG to SUM+COUNT transformation inserts invalid CAST call
[
https://issues.apache.org/jira/browse/CALCITE-6516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17871575#comment-17871575
]
Leonid Chistov edited comment on CALCITE-6516 at 8/7/24 8:38 AM:
-
[~mbudiu]
Yes, thanks, with latest main I am able to reproduce it with following test:
{code:java}
select avg(comm2), sum(comm2), count(comm2) from (select cast(comm * 70 as
decimal(7, 2)) as comm2 from "scott".emp);
+--+---++
| EXPR$0 | EXPR$1| EXPR$2 |
+--+---++
| 38500.00 | 154000.00 | 4 |
+--+---++
(1 row)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
expr#8=[CAST($t7):DECIMAL(7, 2)], EXPR$0=[$t8], EXPR$1=[$t5], EXPR$2=[$t1])
EnumerableAggregate(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[70], expr#9=[*($t6, $t8)],
expr#10=[CAST($t9):DECIMAL(7, 2)], COMM2=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan {code}
Execution result is:
{code:java}
java.lang.ArithmeticException: Value 154000.00 cannot be represented as a
DECIMAL(7, 2) {code}
[~suibianwanwan33]
Yes, I agree with your analysis. I am also not sure ATM if fix that you propose
can create other problems.
was (Author: JIRAUSER298393):
[~mbudiu]
Yes, thanks, with latest main I am able to reproduce it with following test:
{code:java}
select avg(comm2), sum(comm2), count(comm2) from (select cast(comm * 70 as
decimal(7, 2)) as comm2 from "scott".emp);
+--+---++
| EXPR$0 | EXPR$1| EXPR$2 |
+--+---++
| 38500.00 | 154000.00 | 4 |
+--+---++
(1 row)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
expr#8=[CAST($t7):DECIMAL(7, 2)], EXPR$0=[$t8], EXPR$1=[$t5], EXPR$2=[$t1])
EnumerableAggregate(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[70], expr#9=[*($t6, $t8)],
expr#10=[CAST($t9):DECIMAL(7, 2)], COMM2=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan {code}
Execution result is:
{code:java}
java.lang.ArithmeticException: Value 154000.00 cannot be represented as a
DECIMAL(7, 2) {code}
[~suibianwanwan33]
Yes, I agree with your analysis. I am also not sure ATM if fix that you propose
can create other problems.
> AVG to SUM+COUNT transformation inserts invalid CAST call
> -
>
> Key: CALCITE-6516
> URL: https://issues.apache.org/jira/browse/CALCITE-6516
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: Leonid Chistov
>Priority: Major
>
> As described in https://issues.apache.org/jira/browse/CALCITE-4911.
> In Calcite:
> {code:java}
> select avg(comm) as a from "scott".emp{code}
> This SQL physical plan is:
> {noformat}
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
> expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
> expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
> expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
> EnumerableTableScan(table=[[scott, EMP]]){noformat}
> Problem here is that cast of SUM result to DECIMAL(7, 2) is not correct and
> may cause overflow error at runtime or result in not expected data truncation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6516) AVG to SUM+COUNT transformation inserts invalid CAST call
[
https://issues.apache.org/jira/browse/CALCITE-6516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17871575#comment-17871575
]
Leonid Chistov edited comment on CALCITE-6516 at 8/7/24 8:38 AM:
-
[~mbudiu]
Yes, thanks, with latest main I am able to reproduce it with following test:
{code:java}
select avg(comm2), sum(comm2), count(comm2) from (select cast(comm * 70 as
decimal(7, 2)) as comm2 from "scott".emp);
+--+---++
| EXPR$0 | EXPR$1| EXPR$2 |
+--+---++
| 38500.00 | 154000.00 | 4 |
+--+---++
(1 row)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
expr#8=[CAST($t7):DECIMAL(7, 2)], EXPR$0=[$t8], EXPR$1=[$t5], EXPR$2=[$t1])
EnumerableAggregate(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[70], expr#9=[*($t6, $t8)],
expr#10=[CAST($t9):DECIMAL(7, 2)], COMM2=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan {code}
Execution result is:
{code:java}
java.lang.ArithmeticException: Value 154000.00 cannot be represented as a
DECIMAL(7, 2) {code}
[~suibianwanwan33]
Yes, I agree with your analysis. I am also not sure ATM if fix that you propose
can create other problems.
was (Author: JIRAUSER298393):
[~mbudiu]
Yes, thanks, with latest main I am able to reproduce it with following test:
{code:java}
select avg(comm2), sum(comm2), count(comm2) from (select cast(comm * 70 as
decimal(7, 2)) as comm2 from "scott".emp);
+--+---++
| EXPR$0 | EXPR$1| EXPR$2 |
+--+---++
| 38500.00 | 154000.00 | 4 |
+--+---++
(1 row)
!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
expr#8=[CAST($t7):DECIMAL(7, 2)], EXPR$0=[$t8], EXPR$1=[$t5], EXPR$2=[$t1])
EnumerableAggregate(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[70], expr#9=[*($t6, $t8)],
expr#10=[CAST($t9):DECIMAL(7, 2)], COMM2=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan {code}
Execution result is:
{code:java}
java.lang.ArithmeticException: Value 154000.00 cannot be represented as a
DECIMAL(7, 2) {code}
[~suibianwanwan33]
Yes, I agree with your analysis. I am also not sure ATM if fix that you propose
can create other problems.
> AVG to SUM+COUNT transformation inserts invalid CAST call
> -
>
> Key: CALCITE-6516
> URL: https://issues.apache.org/jira/browse/CALCITE-6516
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: Leonid Chistov
>Priority: Major
>
> As described in https://issues.apache.org/jira/browse/CALCITE-4911.
> In Calcite:
> {code:java}
> select avg(comm) as a from "scott".emp{code}
> This SQL physical plan is:
> {noformat}
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
> expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
> expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
> expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
> EnumerableTableScan(table=[[scott, EMP]]){noformat}
> Problem here is that cast of SUM result to DECIMAL(7, 2) is not correct and
> may cause overflow error at runtime or result in not expected data truncation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6516) AVG to SUM+COUNT transformation inserts invalid CAST call
[
https://issues.apache.org/jira/browse/CALCITE-6516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17871272#comment-17871272
]
Leonid Chistov commented on CALCITE-6516:
-
Unfortunately Calcite's Enumerable executor seems to treat DECIMAL -> DECIMAL
casts as no-op, so it is not so easy to write such test.
We will have to use some external query execution engine to reproduce the
actual problem.
Can you give a hint on what are the best practices to write such tests in
Calcite infrastructure?
> AVG to SUM+COUNT transformation inserts invalid CAST call
> -
>
> Key: CALCITE-6516
> URL: https://issues.apache.org/jira/browse/CALCITE-6516
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: Leonid Chistov
>Priority: Major
>
> As described in https://issues.apache.org/jira/browse/CALCITE-4911.
> In Calcite:
> {code:java}
> select avg(comm) as a from "scott".emp{code}
> This SQL physical plan is:
> {noformat}
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
> expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
> expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
> expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
> EnumerableTableScan(table=[[scott, EMP]]){noformat}
> Problem here is that cast of SUM result to DECIMAL(7, 2) is not correct and
> may cause overflow error at runtime or result in not expected data truncation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6516) AVG to SUM+COUNT transformation inserts invalid CAST call
[
https://issues.apache.org/jira/browse/CALCITE-6516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17871138#comment-17871138
]
Leonid Chistov commented on CALCITE-6516:
-
What I observe here is that we rewrite
{code:java}
AVG(comm){code}
to (I simplified it a bit by removing nullability-related handling):
{code:java}
CAST(DIV(CAST(SUM(comm) as DECIMAL(7, 2)), COUNT(COMM)) as DECIMAL(7, 2)){code}
Here the inner cast of SUM(comm) to DECIMAL(7, 2) is problematic and may cause
overflow (I assume it is evident without dataset).
The outer cast is safe.
> AVG to SUM+COUNT transformation inserts invalid CAST call
> -
>
> Key: CALCITE-6516
> URL: https://issues.apache.org/jira/browse/CALCITE-6516
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: Leonid Chistov
>Priority: Major
>
> As described in https://issues.apache.org/jira/browse/CALCITE-4911.
> In Calcite:
> {code:java}
> select avg(comm) as a from "scott".emp{code}
> This SQL physical plan is:
> {noformat}
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
> expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
> expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
> expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
> EnumerableTableScan(table=[[scott, EMP]]){noformat}
> Problem here is that cast of SUM result to DECIMAL(7, 2) is not correct and
> may cause overflow error at runtime or result in not expected data truncation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6516) AVG to SUM+COUNT transformation inserts invalid CAST call
Leonid Chistov created CALCITE-6516:
---
Summary: AVG to SUM+COUNT transformation inserts invalid CAST call
Key: CALCITE-6516
URL: https://issues.apache.org/jira/browse/CALCITE-6516
Project: Calcite
Issue Type: Bug
Affects Versions: 1.37.0
Reporter: Leonid Chistov
As described in https://issues.apache.org/jira/browse/CALCITE-4911.
In Calcite:
{code:java}
select avg(comm) as a from "scott".emp{code}
This SQL physical plan is:
{noformat}
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
EnumerableTableScan(table=[[scott, EMP]]){noformat}
Problem here is that cast of SUM result to DECIMAL(7, 2) is not correct and may
cause overflow error at runtime or result in not expected data truncation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6115) Interval type specifier with zero fractional second precision does not pass validation
[
https://issues.apache.org/jira/browse/CALCITE-6115?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17785543#comment-17785543
]
Leonid Chistov commented on CALCITE-6115:
-
See also SqlIntervalQualifier::evaluateIntervalLiteralAsDayToSecond.
It creates invalid pattern if `fractionalSecondPrecision` equals zero.
> Interval type specifier with zero fractional second precision does not pass
> validation
> --
>
> Key: CALCITE-6115
> URL: https://issues.apache.org/jira/browse/CALCITE-6115
> Project: Calcite
> Issue Type: Bug
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> Consider interval expression
> {code:java}
> interval '1' second(1, 0) {code}
> Calcite SQL validator considers it as not correct, since it uses following
> lower bound for fractional seconds precision:
> {code:java}
> public static final int MIN_INTERVAL_FRACTIONAL_SECOND_PRECISION = 1;{code}
> In order to reproduce this issue one can add following test cast to
> SqlValidatorTest.java:
> {code:java}
> @Test void testSecondIntervalExpression() {
> expr("interval '1' second(1, 0)").columnType("INTERVAL SECOND(1, 0) NOT
> NULL");
> } {code}
> and get an error:
> {code:java}
> Interval fractional second precision '0' out of range for INTERVAL SECOND(1,
> 0) {code}
> However, SQL standard say:
> {code:java}
> An , if specified, shall be greater
> than or equal to 0 (zero)
> and shall not be greater than the implementation-defined maximum. If SECOND
> is specified
> and is not specified, then an
> precision> of 6 is implicit. {code}
> Consequently, MIN_INTERVAL_FRACTIONAL_SECOND_PRECISION should be equal to 0
> to make Calcite behavior consistent with SQL specification.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6115) Interval type specifier with zero fractional second precision does not pass validation
Leonid Chistov created CALCITE-6115:
---
Summary: Interval type specifier with zero fractional second
precision does not pass validation
Key: CALCITE-6115
URL: https://issues.apache.org/jira/browse/CALCITE-6115
Project: Calcite
Issue Type: Bug
Reporter: Leonid Chistov
Assignee: Leonid Chistov
Consider interval expression
{code:java}
interval '1' second(1, 0) {code}
Calcite SQL validator considers it as not correct, since it uses following
lower bound for fractional seconds precision:
{code:java}
public static final int MIN_INTERVAL_FRACTIONAL_SECOND_PRECISION = 1;{code}
In order to reproduce this issue one can add following test cast to
SqlValidatorTest.java:
{code:java}
@Test void testSecondIntervalExpression() {
expr("interval '1' second(1, 0)").columnType("INTERVAL SECOND(1, 0) NOT
NULL");
} {code}
and get an error:
{code:java}
Interval fractional second precision '0' out of range for INTERVAL SECOND(1, 0)
{code}
However, SQL standard say:
{code:java}
An , if specified, shall be greater than
or equal to 0 (zero)
and shall not be greater than the implementation-defined maximum. If SECOND is
specified
and is not specified, then an of 6 is implicit. {code}
Consequently, MIN_INTERVAL_FRACTIONAL_SECOND_PRECISION should be equal to 0 to
make Calcite behavior consistent with SQL specification.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6114) RexExecutor fails on interval expressions with fractional second parts
Leonid Chistov created CALCITE-6114:
---
Summary: RexExecutor fails on interval expressions with fractional
second parts
Key: CALCITE-6114
URL: https://issues.apache.org/jira/browse/CALCITE-6114
Project: Calcite
Issue Type: Bug
Affects Versions: 1.35.0
Reporter: Leonid Chistov
Consider query like:
{code:java}
select interval 1.234 second as inr
from "scott".emp {code}
When trying to run expression reduce step on that query, RexExecutor fails with
following error:
{code:java}
Exception in thread "main" java.lang.RuntimeException: while resolving method
'multiply[class java.math.BigDecimal, long]' in class class
org.apache.calcite.runtime.SqlFunctions
at org.apache.calcite.linq4j.tree.Types.lookupMethod(Types.java:318)
at org.apache.calcite.linq4j.tree.Expressions.call(Expressions.java:449)
at
org.apache.calcite.adapter.enumerable.RexImpTable$BinaryImplementor.implementSafe(RexImpTable.java:2797)
at
org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.genValueStatement(RexImpTable.java:3691)
at
org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.implement(RexImpTable.java:3643)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1184)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:101)
at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:1060)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:101)
at org.apache.calcite.rex.RexLocalRef.accept(RexLocalRef.java:77)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:253)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:247)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:899)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateProjects(RexToLixTranslator.java:201)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateProjects(RexToLixTranslator.java:209)
{code}
The reason why expression reduction step is required for such expressions is
that interval expressions are internally translated to multiplication
expressions like:
{code:java}
*(1.234:decimal32(4, 3), 1000:interval_second(2, 6)) {code}
In order to reproduce the issue, one can add following test case to the
"misc.iq" file and run CoreQuidemTest:
{code:java}
!ok
# Interval expressions
select interval 1.234 second as inr
from "scott".emp;
+---+--++-+
| INR |
+---+--++-+
| 1.234 |
+---+--++-+
(1 rows) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5523) RelToSql converter generates GROUP BY clause with window expression
[
https://issues.apache.org/jira/browse/CALCITE-5523?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17766056#comment-17766056
]
Leonid Chistov commented on CALCITE-5523:
-
[~jiajunbernoulli]
That is quite interesting since documentation that I was able to find
[https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Analytic-Functions.html#GUID-527832F7-63C0-4445-8C16-307FA5084056]
seems to say that it should not be supported.
But I don't have access to Oracle, so I cannot check.
And also in your example it is a HAVING clause, not GROUP BY clause.
> RelToSql converter generates GROUP BY clause with window expression
> ---
>
> Key: CALCITE-5523
> URL: https://issues.apache.org/jira/browse/CALCITE-5523
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Leonid Chistov
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
>
> Wrong SQL code is generated when aggregation is done on the field being a
> result of window expression evaluation.
> Example can be demonstrated by adding following code to
> RelToSqlConverterTest.java:
> {code:java}
> @Test void testConvertWindowGroupByToSql() {
> String query = "SELECT * FROM ("
> + "SELECT rank() over (order by \"hire_date\") \"rank\" FROM
> \"employee\""
> + ") GROUP BY \"rank\"";
> String expected ="SELECT * FROM ("
> + "SELECT rank() over (order by \"hire_date\") AS \"$0\" FROM
> \"employee\""
> + ") GROUP BY \"$0\"";
> sql(query).ok(expected);
> }
> {code}
> Generated SQL code will look like:
> {code:java}
> SELECT RANK() OVER (ORDER BY hire_date) AS rank
> FROM foodmart.employee
> GROUP BY RANK() OVER (ORDER BY hire_date){code}
> This is incorrect - window expressions are not allowed in GROUP BY clause by
> SQL standard and Calcite itself would produce following error message if this
> SQL code would be passed as input:
> {code:java}
> Windowed aggregate expression is illegal in GROUP BY clause {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5957) Valid DATE '1945-2-2' is not accepted due to regression
[ https://issues.apache.org/jira/browse/CALCITE-5957?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17765469#comment-17765469 ] Leonid Chistov commented on CALCITE-5957: - [~zstan] Sorry, don't know why it happened, it was not intentional. > Valid DATE '1945-2-2' is not accepted due to regression > --- > > Key: CALCITE-5957 > URL: https://issues.apache.org/jira/browse/CALCITE-5957 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.35.0 >Reporter: Runkang He >Assignee: Evgeny Stanilovsky >Priority: Blocker > Labels: pull-request-available > Fix For: avatica-1.24.0 > > Attachments: image-2023-08-27-19-09-33-284.png > > Time Spent: 10m > Remaining Estimate: 0h > > DATE '1945-2-2' is a valid date. In CALCITE-5923 when we turn on the result > check of `testCastStringToDateTime`, we find that Calcite accepted DATE > '1945-2-2' before CALCITE-5678 but not afterwards, so this is a regression > that we need to fix. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-5957) Valid DATE '1945-2-2' is not accepted due to regression
[ https://issues.apache.org/jira/browse/CALCITE-5957?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov reassigned CALCITE-5957: --- Assignee: Evgeny Stanilovsky (was: Leonid Chistov) > Valid DATE '1945-2-2' is not accepted due to regression > --- > > Key: CALCITE-5957 > URL: https://issues.apache.org/jira/browse/CALCITE-5957 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.35.0 >Reporter: Runkang He >Assignee: Evgeny Stanilovsky >Priority: Blocker > Labels: pull-request-available > Fix For: avatica-1.24.0 > > Attachments: image-2023-08-27-19-09-33-284.png > > Time Spent: 10m > Remaining Estimate: 0h > > DATE '1945-2-2' is a valid date. In CALCITE-5923 when we turn on the result > check of `testCastStringToDateTime`, we find that Calcite accepted DATE > '1945-2-2' before CALCITE-5678 but not afterwards, so this is a regression > that we need to fix. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-5957) Valid DATE '1945-2-2' is not accepted due to regression
[ https://issues.apache.org/jira/browse/CALCITE-5957?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov reassigned CALCITE-5957: --- Assignee: Leonid Chistov (was: Evgeny Stanilovsky) > Valid DATE '1945-2-2' is not accepted due to regression > --- > > Key: CALCITE-5957 > URL: https://issues.apache.org/jira/browse/CALCITE-5957 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.35.0 >Reporter: Runkang He >Assignee: Leonid Chistov >Priority: Blocker > Labels: pull-request-available > Fix For: avatica-1.24.0 > > Attachments: image-2023-08-27-19-09-33-284.png > > Time Spent: 10m > Remaining Estimate: 0h > > DATE '1945-2-2' is a valid date. In CALCITE-5923 when we turn on the result > check of `testCastStringToDateTime`, we find that Calcite accepted DATE > '1945-2-2' before CALCITE-5678 but not afterwards, so this is a regression > that we need to fix. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-5502) RelToSql converter generates where clause with window expression
[
https://issues.apache.org/jira/browse/CALCITE-5502?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov reassigned CALCITE-5502:
---
Assignee: Leonid Chistov
> RelToSql converter generates where clause with window expression
>
>
> Key: CALCITE-5502
> URL: https://issues.apache.org/jira/browse/CALCITE-5502
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> Wrong SQL code is generated when Filter (or Calc) node contains window
> expression.
> Example can be demonstrated by adding following code to
> RelToSqlConverterTest.java:
> {code:java}
> @Test void testWindowedFilter() {
> final RelBuilder builder = relBuilder();
> final RelNode root = builder
> .scan("DEPT")
> .filter(
> builder.lessThan(
> builder.aggregateCall(SqlStdOperatorTable.MAX,
> builder.field("DEPTNO"))
> .over()
> .partitionBy(builder.field("DNAME"))
> .toRex(),
> builder.literal(1)
> )
> )
> .build();
> final String expectedSql = "?";
> assertThat(toSql(root), isLinux(expectedSql));
> } {code}
> Generated SQL code will look like:
> {code:java}
> SELECT *
> FROM \"scott\".\"DEPT\"
> WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED
> PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code}
> This is incorrect - window expressions are not allowed in WHERE clause by SQL
> standard and Calcite itself would produce following error message if this SQL
> code would be passed as input:
> {code:java}
> Windowed aggregate expression is illegal in WHERE clause {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5952) SemiJoinJoinTransposeRule should check if JoinType supports pushing predicates into its inputs
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5952:
Summary: SemiJoinJoinTransposeRule should check if JoinType supports
pushing predicates into its inputs (was: Semi-Join incorrectly reordered with
Left-Join by SemiJoinJoinTransposeRule)
> SemiJoinJoinTransposeRule should check if JoinType supports pushing
> predicates into its inputs
> --
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761846#comment-17761846
]
Leonid Chistov commented on CALCITE-5952:
-
[~rubenql]
Yes, in your example we push semi-join to the left side of the left join.
When I was writing "should not push SemiJoin to the right (left) input of the
Left (Right) Join", I thought that everyone would read it as:
"should not push to the right input of Left Join and to the left input of the
Right Join".
=
My new proposal is "SemiJoinJoinTransposeRule should check if JoinType does
support pushing predicates into its inputs"
=
> perhaps it would be nice to add these examples of Left/Right Join where the
>rule can be applied
OK
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761825#comment-17761825
]
Leonid Chistov commented on CALCITE-5952:
-
[~rubenql]
Maybe just "SemiJoinJoinTransposeRule should not push SemiJoin to the right
(left) input of the Left (Right) Join"
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5952:
Labels: pull-request-available (was: )
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761757#comment-17761757
]
Leonid Chistov commented on CALCITE-5952:
-
[~rubenql]
I have prepared a Pull Request: [https://github.com/apache/calcite/pull/3407]
and added the runtime test also. But I don't really like it since it looks like
this approach will force test authors to implement tests for optimization rules
twice - one test for plan correctness and other for runtime correctness.
Also, there is a new email thread about the same question
[https://lists.apache.org/thread/r4yhn77m92fodmz8xm6k40sfd130w7hh,] which ends
with an idea that we probably should establish proper infrastructure for
specific rules testing in Quidem test.
WDYT?
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17758074#comment-17758074
]
Leonid Chistov commented on CALCITE-5952:
-
[~rubenql]
Can you suggest which Calcite infrastructure to use to write such test (or
where to look for the analogous examples) that will do the planning and execute
the plan?
I have tried to look on Quidem tests, but they seem to be more
frontend-oriented, I didn't find a way to make them run with specific
optimization enabled.
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17757955#comment-17757955
]
Leonid Chistov edited comment on CALCITE-5952 at 8/23/23 10:41 AM:
---
[~rubenql]
The expected result is mentioned in issue description (I have called it
"original plan", meaning that plan that is constructed by RelBuilder is not
supposed to be changed by SEMI_JOIN_JOIN_TRANSPOSE):
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
was (Author: JIRAUSER298393):
[~rubenql]
The expected result is mentioned in issue description (I have called it
"original plan", meaning that plan that is constructed by RelBuilder is not
supposed to be changed by SEMI_JOIN_JOIN_TRANSPOSE):
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17757955#comment-17757955
]
Leonid Chistov edited comment on CALCITE-5952 at 8/23/23 10:41 AM:
---
[~rubenql]
The expected result is mentioned in issue description (I have called it
"original plan", meaning that plan that is constructed by RelBuilder is not
supposed to be changed by SEMI_JOIN_JOIN_TRANSPOSE):
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
was (Author: JIRAUSER298393):
[~rubenql]
The expected result is mentioned in issue description (I have called it
"original plan", meaning that plan that is constructed by RelBuilder is not
supposed to be changed by SEMI_JOIN_JOIN_TRANSPOSE):
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17757955#comment-17757955
]
Leonid Chistov commented on CALCITE-5952:
-
[~rubenql]
The expected result is mentioned in issue description (I have called it
"original plan", meaning that plan that is constructed by RelBuilder is not
supposed to be changed by SEMI_JOIN_JOIN_TRANSPOSE):
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> This is not correct - in general case it is not correct to push semi-join to
> right side of left-join.
> The reason is the following:
> Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
> will have *nulls* for *DEPT* columns in the result of left-join and they will
> be rejected by the top semi-join.
> But if we push semi-join to RHS of left-join, we are going to see rows from
> *EMP* with *nulls* on the *DEPT* side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5952:
Description:
The following test will fail if added to RelOptRulesTest.java
{code:java}
@Test void testCanNotPushSemiJoinToRightJoinBranch() {
final Function relFn = b -> b
.scan("EMP")
.scan("DEPT")
.join(JoinRelType.LEFT,
b.equals(
b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO"))
)
.scan("BONUS")
// semi join only relates to RHS fields of left join
.semiJoin(b.equals(
b.field(2, 0, "DNAME"),
b.field(2, 1, "JOB")))
.build();
relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
} {code}
Produced plan will look like:
{code:java}
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalJoin(condition=[=($1, $4)], joinType=[semi])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
Which is different from the original plan:
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
This is not correct - in general case it is not correct to push semi-join to
right side of left-join.
The reason is the following:
Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
will have *nulls* for *DEPT* columns in the result of left-join and they will
be rejected by the top semi-join.
But if we push semi-join to RHS of left-join, we are going to see rows from
*EMP* with *nulls* on the *DEPT* side in the final result.
was:
The following test will fail if added to RelOptRulesTest.java
{code:java}
@Test void testCanNotPushSemiJoinToRightJoinBranch() {
final Function relFn = b -> b
.scan("EMP")
.scan("DEPT")
.join(JoinRelType.LEFT,
b.equals(
b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO"))
)
.scan("BONUS")
.semiJoin(b.equals(
b.field(2, 0, "DNAME"),
b.field(2, 1, "JOB")))
.build();
relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
} {code}
Produced plan will look like:
{code:java}
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalJoin(condition=[=($1, $4)], joinType=[semi])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
Which is different from the original plan:
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
This is not correct - in general case it is not correct to push semi-join to
right side of left-join.
The reason is the following:
Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
will have *nulls* for *DEPT* columns in the result of left-join and they will
be rejected by the top semi-join.
But if we push semi-join to RHS of left-join, we are going to see rows from
*EMP* with *nulls* on the *DEPT* side in the final result.
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> // semi join only relates to RHS fields of left join
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)]
[jira] [Updated] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5952:
Description:
The following test will fail if added to RelOptRulesTest.java
{code:java}
@Test void testCanNotPushSemiJoinToRightJoinBranch() {
final Function relFn = b -> b
.scan("EMP")
.scan("DEPT")
.join(JoinRelType.LEFT,
b.equals(
b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO"))
)
.scan("BONUS")
.semiJoin(b.equals(
b.field(2, 0, "DNAME"),
b.field(2, 1, "JOB")))
.build();
relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
} {code}
Produced plan will look like:
{code:java}
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalJoin(condition=[=($1, $4)], joinType=[semi])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
Which is different from the original plan:
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
This is not correct - in general case it is not correct to push semi-join to
right side of left-join.
The reason is the following:
Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows
will have *nulls* for *DEPT* columns in the result of left-join and they will
be rejected by the top semi-join.
But if we push semi-join to RHS of left-join, we are going to see rows from
*EMP* with *nulls* on the *DEPT* side in the final result.
was:
The following test will fail if added to RelOptRulesTest.java
{code:java}
@Test void testCanNotPushSemiJoinToRightJoinBranch() {
final Function relFn = b -> b
.scan("EMP")
.scan("DEPT")
.join(JoinRelType.LEFT,
b.equals(
b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO"))
)
.scan("BONUS")
.semiJoin(b.equals(
b.field(2, 0, "DNAME"),
b.field(2, 1, "JOB")))
.build();
relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
} {code}
Produced plan will look like:
{code:java}
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalJoin(condition=[=($1, $4)], joinType=[semi])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
Which is different from the original plan:
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
This is not correct - in general case it is not correct to push semi-join to
right side of left-join.
The reason is the following:
Consider rows from `EMP` that have no matching rows in DEPT. These rows will
have `nulls` for `DEPT` columns in the result of left-join and they will be
rejected by the top semi-join.
But if we push semi-join to RHS of left-join, we are going to see rows from
`EMP` with `nulls` on the `DEPT` side in the final result.
> Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
> ---
>
> Key: CALCITE-5952
> URL: https://issues.apache.org/jira/browse/CALCITE-5952
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> The following test will fail if added to RelOptRulesTest.java
> {code:java}
> @Test void testCanNotPushSemiJoinToRightJoinBranch() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.equals(
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO"))
> )
> .scan("BONUS")
> .semiJoin(b.equals(
> b.field(2, 0, "DNAME"),
> b.field(2, 1, "JOB")))
> .build();
> relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
> } {code}
> Produced plan will look like:
> {code:java}
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalJoin(condition=[=($1, $4)], joinType=[semi])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalTableScan(table=[[scott, BONUS]]) {code}
> Which is different from the original plan:
> {code:java}
> LogicalJoin(condition=[=($9, $12)], joinType=[semi])
> LogicalJoin(condition=[=($7, $8)], joinType=[left])
> LogicalTableScan(table=[[scott, EMP]])
>
[jira] [Created] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
Leonid Chistov created CALCITE-5952:
---
Summary: Semi-Join incorrectly reordered with Left-Join by
SemiJoinJoinTransposeRule
Key: CALCITE-5952
URL: https://issues.apache.org/jira/browse/CALCITE-5952
Project: Calcite
Issue Type: Bug
Affects Versions: 1.35.0
Reporter: Leonid Chistov
Assignee: Leonid Chistov
The following test will fail if added to RelOptRulesTest.java
{code:java}
@Test void testCanNotPushSemiJoinToRightJoinBranch() {
final Function relFn = b -> b
.scan("EMP")
.scan("DEPT")
.join(JoinRelType.LEFT,
b.equals(
b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO"))
)
.scan("BONUS")
.semiJoin(b.equals(
b.field(2, 0, "DNAME"),
b.field(2, 1, "JOB")))
.build();
relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged();
} {code}
Produced plan will look like:
{code:java}
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalJoin(condition=[=($1, $4)], joinType=[semi])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
Which is different from the original plan:
{code:java}
LogicalJoin(condition=[=($9, $12)], joinType=[semi])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
LogicalTableScan(table=[[scott, BONUS]]) {code}
This is not correct - in general case it is not correct to push semi-join to
right side of left-join.
The reason is the following:
Consider rows from `EMP` that have no matching rows in DEPT. These rows will
have `nulls` for `DEPT` columns in the result of left-join and they will be
rejected by the top semi-join.
But if we push semi-join to RHS of left-join, we are going to see rows from
`EMP` with `nulls` on the `DEPT` side in the final result.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714124#comment-17714124
]
Leonid Chistov edited comment on CALCITE-5646 at 4/19/23 9:11 PM:
--
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
{*}UPDATE{*}: Having said this, I cannot come up with join condition that turns
to FALSE and does not look stupid.
Example would be something like coalesce(x, FALSE) or coalesce(a.x * b.y,
FALSE).
was (Author: JIRAUSER298393):
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
UPDATE: Having said this, I cannot come up with join condition that turns to
FALSE and does not look stupid.
Example would be something like coalesce(x, FALSE) or coalesce(a.x * b.y,
FALSE).
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714310#comment-17714310
]
Leonid Chistov commented on CALCITE-5646:
-
Sorry, I don't really understand your point. It looks like you propose new
potential optimization rule "pushdown of IS NULL filter from Join condition",
while this Jira Issue describes bug in "pushdown of IS NOT NULL filter from
Join condition"".
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714124#comment-17714124
]
Leonid Chistov edited comment on CALCITE-5646 at 4/19/23 9:09 PM:
--
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
UPDATE: Having said this, I cannot come up with join condition that turns to
FALSE and does not look stupid.
Example would be something like coalesce(x, FALSE) or coalesce(a.x * b.y,
FALSE).
was (Author: JIRAUSER298393):
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714124#comment-17714124
]
Leonid Chistov commented on CALCITE-5646:
-
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17713237#comment-17713237
]
Leonid Chistov commented on CALCITE-5646:
-
I agree that {{Strong class}} API is not perfectly clear and you have to read
the code to understand its semantics.
But for me it is out of scope for current Jira Issue, TBH.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5402) RelToSql generates invalid code if left and right side field names clash
[
https://issues.apache.org/jira/browse/CALCITE-5402?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov reassigned CALCITE-5402:
---
Assignee: Leonid Chistov
> RelToSql generates invalid code if left and right side field names clash
>
>
> Key: CALCITE-5402
> URL: https://issues.apache.org/jira/browse/CALCITE-5402
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Minor
>
> Wrong SQL code is generated when left and right side of a semi-join have
> fields with same name.
> Generated condition looks like `FIELD` = `rhs table`.`FIELD`, where left
> `FIELD` is resolved to be the same as `rhs table`.`FIELD` during query
> execution, but not a reference to an outer table of correlated subquery, as
> was intended.
> Examples of tests that would fail if added to RelToSqlConverterTest.java:
> {code:java}
> @Test void testSemiJoinNameClash() {
> final RelBuilder builder = relBuilder();
> final RelNode root = builder
> .scan("DEPT")
> .project(builder.field("DEPTNO"), builder.field("DNAME"))
> .scan("EMP")
> .filter(
> builder.call(SqlStdOperatorTable.GREATER_THAN,
> builder.field("JOB"),
> builder.literal((short) 10)))
> .project(builder.field("DEPTNO"))
> .join(
> JoinRelType.SEMI, builder.equals(
> builder.field(2, 0, "DEPTNO"),
> builder.field(2, 1, "DEPTNO")))
> .project(builder.field("DEPTNO"))
> .build();
> final String expectedSql = "SELECT \"DEPTNO\"\n"
> + "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
> + "FROM \"scott\".\"DEPT\"\n"
> + "WHERE EXISTS (SELECT 1\n"
> + "FROM (SELECT \"EMPNO\"\n"
> + "FROM \"scott\".\"EMP\"\n"
> + "WHERE \"JOB\" > 10) AS \"t1\"\n"
> + "WHERE \"DEPT\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
> assertThat(toSql(root), isLinux(expectedSql));
> } {code}
>
> {code:java}
> @Test void testSemiJoinWithSameTable() {
> final RelBuilder builder = relBuilder();
> final RelNode root = builder
> .scan("EMP")
> .project(builder.field("DEPTNO"), builder.field("EMPNO"))
> .scan("EMP")
> .filter(
> builder.call(SqlStdOperatorTable.GREATER_THAN,
> builder.field("JOB"),
> builder.literal((short) 10)))
> .project(builder.field("EMPNO"))
> .join(
> JoinRelType.SEMI, builder.equals(
> builder.field(2, 0, "EMPNO"),
> builder.field(2, 1, "EMPNO")))
> .project(builder.field("DEPTNO"))
> .build();
> final String expectedSql = "SELECT \"DEPTNO\"\n"
> + "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
> + "FROM \"scott\".\"EMP\"\n"
> + "WHERE EXISTS (SELECT 1\n"
> + "FROM (SELECT \"EMPNO\"\n"
> + "FROM \"scott\".\"EMP\"\n"
> + "WHERE \"JOB\" > 10) AS \"t1\"\n"
> + "WHERE \"EMP\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
> assertThat(toSql(root), isLinux(expectedSql));
> } {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17712458#comment-17712458
]
Leonid Chistov edited comment on CALCITE-5646 at 4/14/23 6:51 PM:
--
[~julianhyde]
I am not sure that this a bug in Strong.
I think that following thing had happened:
* Strong class had a Javadoc comment "A predicate is strong (or
null-rejecting) if it is UNKNOWN if *any* of its inputs is UNKNOWN."
* Actual implementation of class methods works different way:
** {{public static boolean isStrong(RexNode e)}} method checks whether it is
true, that if we set *all* inputs to UNKNOWN, we will get UNKNOWN as the
expression result
** Other methods that accept explicit set of inputs via bitset, check whether
it is true, that if we set *all* inputs *from specified set* to UNKNOWN, we
will get UNKNOWN as the expression result
* It seems that JoinDeriveIsNotNullFilterRule assumed behavior described in
Javadoc, but not the real one
was (Author: JIRAUSER298393):
[~julianhyde]
I am not sure that this a bug in Strong.
I think that following thing had happened:
* Strong class had a Javadoc comment "A predicate is strong (or
null-rejecting) if it is UNKNOWN if *any* of its inputs is UNKNOWN."
* Actual implementation of class methods works different way:
** {\{public static boolean isStrong(RexNode e)}} method checks whether it is
true, that if we set *all* inputs to UNKNOWN, we will get UNKNOWN as the
expression result
** Other methods that accept explicit set of inputs via bitset, check whether
it is true, that if we set *all* inputs from specified bit set to UNKNOWN, we
will get UNKNOWN as the expression result
* It seems that JoinDeriveIsNotNullFilterRule assumed behavior described in
Javadoc, but not the real one
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17712458#comment-17712458
]
Leonid Chistov commented on CALCITE-5646:
-
[~julianhyde]
I am not sure that this a bug in Strong.
I think that following thing had happened:
* Strong class had a Javadoc comment "A predicate is strong (or
null-rejecting) if it is UNKNOWN if *any* of its inputs is UNKNOWN."
* Actual implementation of class methods works different way:
** {\{public static boolean isStrong(RexNode e)}} method checks whether it is
true, that if we set *all* inputs to UNKNOWN, we will get UNKNOWN as the
expression result
** Other methods that accept explicit set of inputs via bitset, check whether
it is true, that if we set *all* inputs from specified bit set to UNKNOWN, we
will get UNKNOWN as the expression result
* It seems that JoinDeriveIsNotNullFilterRule assumed behavior described in
Javadoc, but not the real one
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5646:
Description:
Consider query
{code:java}
select t1.deptno from empnullables t1 inner join
empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
transformed to query plan
{code:java}
LogicalProject(DEPTNO=[$7])
LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
joinType=[inner])
LogicalFilter(condition=[IS NOT NULL($1)])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalFilter(condition=[IS NOT NULL($1)])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
It is not valid to deduce that join keys from the both sides cannot have null
values. All that we can deduce from the join condition, is that they cannot be
null in the same time.
was:
Consider query
{code:java}
select t1.deptno from empnullables t1 inner join
empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
transformed to query plan
{code:java}
LogicalProject(DEPTNO=[$7])
LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
joinType=[inner])
LogicalFilter(condition=[IS NOT NULL($1)])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalFilter(condition=[IS NOT NULL($1)])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
It is not valid to deduce that join keys from the both sides cannot have null
values, all we can deduce from the join condition, is that they cannot be null
in the same time.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5639) RexSimplify should remove IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17712303#comment-17712303
]
Leonid Chistov commented on CALCITE-5639:
-
I have updated PR with more general solution and also created separate issue
for the problem mentioned above:
https://issues.apache.org/jira/browse/CALCITE-5646
> RexSimplify should remove IS NOT NULL check when LIKE comparison is present
> ---
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 40m
> Remaining Estimate: 0h
>
> Consider query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5646:
Labels: pull-request-available (was: )
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values, all we can deduce from the join condition, is that they cannot be
> null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
Leonid Chistov created CALCITE-5646:
---
Summary: JoinDeriveIsNotNullFilterRule incorrectly handles
COALESCE in join condition
Key: CALCITE-5646
URL: https://issues.apache.org/jira/browse/CALCITE-5646
Project: Calcite
Issue Type: Bug
Affects Versions: 1.34.0
Reporter: Leonid Chistov
Assignee: Leonid Chistov
Consider query
{code:java}
select t1.deptno from empnullables t1 inner join
empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
transformed to query plan
{code:java}
LogicalProject(DEPTNO=[$7])
LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
joinType=[inner])
LogicalFilter(condition=[IS NOT NULL($1)])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalFilter(condition=[IS NOT NULL($1)])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
It is not valid to deduce that join keys from the both sides cannot have null
values, all we can deduce from the join condition, is that they cannot be null
in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5639) RexSimplify should remove IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17711387#comment-17711387
]
Leonid Chistov edited comment on CALCITE-5639 at 4/12/23 1:34 PM:
--
[~julianhyde]
I can try to do that.
After looking on the {{class Strong}} I think that there is some confusion
between class JavaDoc and JavaDoc (and behavior) of actual implementation:
* Class JavaDoc says "A predicate is strong (or null-rejecting) if it is
UNKNOWN if *any* of its inputs is UNKNOWN"
* {{isStrong}} method JavaDoc says "Returns whether a given expression is
strong."
* {{isNull}} method JavaDoc says "Returns whether the analyzed expression will
definitely return null if *all* of a given set of input columns are null"
{\{isNull}} method is used by {{isStrong}} method to do the job and it seems
that we silently change {{strongness}} definition.
For example, if we have an expression COALESCE(A, B), it will be NULL if *all*
inputs are NULL, but it is not true to say that it will be NULL, if *any* of
inputs are null.
And it seems that JoinDeriveIsNotNullFilterRule optimization rule relies on
*any* style of _strongness_ definition (which does not correspond to real
behavior) and thus may lead to wrong result.
So, it seems that some other Jira issues need to be created to clarify {{Strong
class }} behavior.
I am not sure yet, whether fix for current issue will be dependent on this
clarification or not.
was (Author: JIRAUSER298393):
[~julianhyde]
I can try to do that.
After looking on the {{class Strong}} I think that there is some confusion
between class JavaDoc and JavaDoc (and behavior) of actual implementation:
* Class JavaDoc says "A predicate is strong (or null-rejecting) if it is
UNKNOWN if *any* of its inputs is UNKNOWN"
* {{isStrong}} method JavaDoc says "Returns whether a given expression is
strong."
* {{isNull}} method JavaDoc says "Returns whether the analyzed expression will
definitely return null if *all* of a given set of input columns are null"
{{ sNull}} method is used by {{isStrong}} method to do the job and it seems
that we silently change {{strongness}} definition.
For example, if we have an expression COALESCE(A, B), it will be NULL if *all*
inputs are NULL, but it is not true to say that it will be NULL, if *any* of
inputs are null.
And it seems that JoinDeriveIsNotNullFilterRule optimization rule relies on
*any* style of _strongness_ definition (which does not correspond to real
behavior) and thus may lead to wrong result.
So, it seems that some other Jira issues need to be created to clarify {{Strong
class }} behavior.
I am not sure yet, whether fix for current issue will be dependent on this
clarification or not.
> RexSimplify should remove IS NOT NULL check when LIKE comparison is present
> ---
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5639) RexSimplify should remove IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17711387#comment-17711387
]
Leonid Chistov edited comment on CALCITE-5639 at 4/12/23 1:34 PM:
--
[~julianhyde]
I can try to do that.
After looking on the {{class Strong}} I think that there is some confusion
between class JavaDoc and JavaDoc (and behavior) of actual implementation:
* Class JavaDoc says "A predicate is strong (or null-rejecting) if it is
UNKNOWN if *any* of its inputs is UNKNOWN"
* {{isStrong}} method JavaDoc says "Returns whether a given expression is
strong."
* {{isNull}} method JavaDoc says "Returns whether the analyzed expression will
definitely return null if *all* of a given set of input columns are null"
{{isNull}} method is used by {{isStrong}} method to do the job and it seems
that we silently change {{strongness}} definition.
For example, if we have an expression COALESCE(A, B), it will be NULL if *all*
inputs are NULL, but it is not true to say that it will be NULL, if *any* of
inputs is null.
And it seems that JoinDeriveIsNotNullFilterRule optimization rule relies on
*any* style of _strongness_ definition (which does not correspond to real
behavior) and thus may lead to wrong result.
So, it seems that some other Jira issues need to be created to clarify {{Strong
class }} behavior.
I am not sure yet, whether fix for current issue will be dependent on this
clarification or not.
was (Author: JIRAUSER298393):
[~julianhyde]
I can try to do that.
After looking on the {{class Strong}} I think that there is some confusion
between class JavaDoc and JavaDoc (and behavior) of actual implementation:
* Class JavaDoc says "A predicate is strong (or null-rejecting) if it is
UNKNOWN if *any* of its inputs is UNKNOWN"
* {{isStrong}} method JavaDoc says "Returns whether a given expression is
strong."
* {{isNull}} method JavaDoc says "Returns whether the analyzed expression will
definitely return null if *all* of a given set of input columns are null"
{\{isNull}} method is used by {{isStrong}} method to do the job and it seems
that we silently change {{strongness}} definition.
For example, if we have an expression COALESCE(A, B), it will be NULL if *all*
inputs are NULL, but it is not true to say that it will be NULL, if *any* of
inputs are null.
And it seems that JoinDeriveIsNotNullFilterRule optimization rule relies on
*any* style of _strongness_ definition (which does not correspond to real
behavior) and thus may lead to wrong result.
So, it seems that some other Jira issues need to be created to clarify {{Strong
class }} behavior.
I am not sure yet, whether fix for current issue will be dependent on this
clarification or not.
> RexSimplify should remove IS NOT NULL check when LIKE comparison is present
> ---
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5639) RexSimplify should remove IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17711387#comment-17711387
]
Leonid Chistov commented on CALCITE-5639:
-
[~julianhyde]
I can try to do that.
After looking on the {{class Strong}} I think that there is some confusion
between class JavaDoc and JavaDoc (and behavior) of actual implementation:
* Class JavaDoc says "A predicate is strong (or null-rejecting) if it is
UNKNOWN if *any* of its inputs is UNKNOWN"
* {{isStrong}} method JavaDoc says "Returns whether a given expression is
strong."
* {{isNull}} method JavaDoc says "Returns whether the analyzed expression will
definitely return null if *all* of a given set of input columns are null"
{{ sNull}} method is used by {{isStrong}} method to do the job and it seems
that we silently change {{strongness}} definition.
For example, if we have an expression COALESCE(A, B), it will be NULL if *all*
inputs are NULL, but it is not true to say that it will be NULL, if *any* of
inputs are null.
And it seems that JoinDeriveIsNotNullFilterRule optimization rule relies on
*any* style of _strongness_ definition (which does not correspond to real
behavior) and thus may lead to wrong result.
So, it seems that some other Jira issues need to be created to clarify {{Strong
class }} behavior.
I am not sure yet, whether fix for current issue will be dependent on this
clarification or not.
> RexSimplify should remove IS NOT NULL check when LIKE comparison is present
> ---
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5639) RexSimplify not removes IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17710942#comment-17710942
]
Leonid Chistov commented on CALCITE-5639:
-
https://github.com/apache/calcite/pull/3143
> RexSimplify not removes IS NOT NULL check when LIKE comparison is present
> -
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Minor
>
> Consider query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5639) RexSimplify not removes IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5639:
Labels: pull-request-available (was: )
> RexSimplify not removes IS NOT NULL check when LIKE comparison is present
> -
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Minor
> Labels: pull-request-available
>
> Consider query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5639) RexSimplify not removes IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5639:
Priority: Minor (was: Major)
> RexSimplify not removes IS NOT NULL check when LIKE comparison is present
> -
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Minor
>
> Consider query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5639) RexSimplify not removes IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5639:
Description:
Consider query like
{code:java}
select r_reason_desc from reason
where r_reason_desc is not null and r_reason_desc like '%child%'
{code}
where "r_reason_desc" is a nullable field.
When RexSimplify::simplifyFilterPredicates is called on that conjunction of
expressions, expression is not simplified, meaning that redundant "is not
null" check is not removed.
In the same time, if query like
{code:java}
select r_reason_desc from reason
where r_reason_desc is not null and r_reason_desc >= 'A'{code}
is passed to optimizer, redundant "is not null" check is eliminated.
was:
Consider query like
{code:java}
select r_reason_desc from reason
where r_reason_desc is not null and r_reason_desc like '%child%'
{code}
where "r_reason_desc" is a nullable field.
When RexSimplify::simplifyFilterPredicates is called on that conjunction of
expressions, expression is not simplified, meaning that redundant "is not
null" check is not removed.
In the same time, if query like
{code:java}
select r_reason_desc from reason
where r_reason_desc is not null and r_reason_desc >= 'A'{code}
is passed to optimizer, redundant "is not null" check is eliminated.
> RexSimplify not removes IS NOT NULL check when LIKE comparison is present
> -
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> Consider query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5639) RexSimplify not removes IS NOT NULL check when LIKE comparison is present
[
https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5639:
Description:
Consider query like
{code:java}
select r_reason_desc from reason
where r_reason_desc is not null and r_reason_desc like '%child%'
{code}
where "r_reason_desc" is a nullable field.
When RexSimplify::simplifyFilterPredicates is called on that conjunction of
expressions, expression is not simplified, meaning that redundant "is not
null" check is not removed.
In the same time, if query like
{code:java}
select r_reason_desc from reason
where r_reason_desc is not null and r_reason_desc >= 'A'{code}
is passed to optimizer, redundant "is not null" check is eliminated.
was:
Consider query likeselect r_reason_desc from reason where r_reason_desc is not
null and r_reason_desc like '%child%' desc is not null and r_reason_desc like
'%child%'
where `r_reason_desc` is a nullable field.
When `RexSimplify::simplifyFilterPredicates` is called on that conjunction of
expressions, expression is not simplified, meaning that redundant `is not
null` check is not removed.
In the same time, if query like
{code:java}
select r_reason_desc from reason
where r_reason_desc is not null and r_reason_desc like '%child%' {code}
is passed to optimizer, redundant `is not null` check is eliminated.
> RexSimplify not removes IS NOT NULL check when LIKE comparison is present
> -
>
> Key: CALCITE-5639
> URL: https://issues.apache.org/jira/browse/CALCITE-5639
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> Consider query like
>
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc like '%child%'
> {code}
>
> where "r_reason_desc" is a nullable field.
> When RexSimplify::simplifyFilterPredicates is called on that conjunction of
> expressions, expression is not simplified, meaning that redundant "is not
> null" check is not removed.
> In the same time, if query like
> {code:java}
> select r_reason_desc from reason
> where r_reason_desc is not null and r_reason_desc >= 'A'{code}
> is passed to optimizer, redundant "is not null" check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5639) RexSimplify not removes IS NOT NULL check when LIKE comparison is present
Leonid Chistov created CALCITE-5639:
---
Summary: RexSimplify not removes IS NOT NULL check when LIKE
comparison is present
Key: CALCITE-5639
URL: https://issues.apache.org/jira/browse/CALCITE-5639
Project: Calcite
Issue Type: Bug
Affects Versions: 1.34.0
Reporter: Leonid Chistov
Assignee: Leonid Chistov
Consider query likeselect r_reason_desc from reason where r_reason_desc is not
null and r_reason_desc like '%child%' desc is not null and r_reason_desc like
'%child%'
where `r_reason_desc` is a nullable field.
When `RexSimplify::simplifyFilterPredicates` is called on that conjunction of
expressions, expression is not simplified, meaning that redundant `is not
null` check is not removed.
In the same time, if query like
{code:java}
select r_reason_desc from reason
where r_reason_desc is not null and r_reason_desc like '%child%' {code}
is passed to optimizer, redundant `is not null` check is eliminated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5523) RelToSql converter generates GROUP BY clause with window expression
[
https://issues.apache.org/jira/browse/CALCITE-5523?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17687963#comment-17687963
]
Leonid Chistov commented on CALCITE-5523:
-
[~jiajunbernoulli] These issues look similar, but for CALCITE-4491 there were
valid examples of nested aggregator supported by some databases.
I wonder if any database really support window functions inside GROUP BY. If
the answer is no, then we can avoid check `supportsNestedAggregations()` check.
> RelToSql converter generates GROUP BY clause with window expression
> ---
>
> Key: CALCITE-5523
> URL: https://issues.apache.org/jira/browse/CALCITE-5523
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Leonid Chistov
>Assignee: Jiajun Xie
>Priority: Major
>
> Wrong SQL code is generated when aggregation is done on the field being a
> result of window expression evaluation.
> Example can be demonstrated by adding following code to
> RelToSqlConverterTest.java:
> {code:java}
> @Test void testConvertWindowGroupByToSql() {
> String query = "SELECT * FROM ("
> + "SELECT rank() over (order by \"hire_date\") \"rank\" FROM
> \"employee\""
> + ") GROUP BY \"rank\"";
> String expected ="SELECT * FROM ("
> + "SELECT rank() over (order by \"hire_date\") AS \"$0\" FROM
> \"employee\""
> + ") GROUP BY \"$0\"";
> sql(query).ok(expected);
> }
> {code}
> Generated SQL code will look like:
> {code:java}
> SELECT RANK() OVER (ORDER BY hire_date) AS rank
> FROM foodmart.employee
> GROUP BY RANK() OVER (ORDER BY hire_date){code}
> This is incorrect - window expressions are not allowed in GROUP BY clause by
> SQL standard and Calcite itself would produce following error message if this
> SQL code would be passed as input:
> {code:java}
> Windowed aggregate expression is illegal in GROUP BY clause {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] (CALCITE-5518) RelToSql converter generates invalid order of ROLLUP fields
[ https://issues.apache.org/jira/browse/CALCITE-5518 ]
Leonid Chistov deleted comment on CALCITE-5518:
-
was (Author: JIRAUSER298393):
The source of this bug is the following:
{code:java}
private List generateGroupList(Builder builder,
List selectList, Aggregate aggregate, List groupList) {
.
switch (aggregate.getGroupType()) {
..
case ROLLUP:
return ImmutableList.of(
SqlStdOperatorTable.ROLLUP.createCall(SqlParserPos.ZERO,
groupKeys));{code}
Here we create ROLLUP SQL clause ignoring the "rolling up order" of original
Aggregate.
Grouping sets "\{0,1}, \{0}, {}" and "\{0, 1}, \{1}, {}" are both classified as
ROLLUP-s and they have equal list of grouping keys (0, 1), but different SQL
needs to be generated for them.
> RelToSql converter generates invalid order of ROLLUP fields
> ---
>
> Key: CALCITE-5518
> URL: https://issues.apache.org/jira/browse/CALCITE-5518
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Major
>
> RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
> classified as `ROLLUP` grouping but with order of rollup not matching order
> of grouping fields.
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testGroupingSetsRollupNonNaturalOrder() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
> + " (\"brand_name\"), ())\n";
> final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
> + "FROM \"foodmart\".\"product\"\n"
> + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
> sql(query)
> .withPostgresql().ok(expected);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
> grouping, but right after that we generate SQL code as if grouping sets were
> \{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5518) RelToSql converter generates invalid order of ROLLUP fields
[
https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17686961#comment-17686961
]
Leonid Chistov edited comment on CALCITE-5518 at 2/10/23 9:00 AM:
--
[~julianhyde] no, it is not a duplicate, unfortunately.
CALCITE-5416 is about wrong processing of ROLLUP clause under ORDER BY clause
in some situations.
Current issue is about wrong processing of some cases of ROLLUP (regardless of
having or not having subsequent ordering).
The source of current bug is the following:
{code:java}
private List generateGroupList(Builder builder,
List selectList, Aggregate aggregate, List groupList) {
.
switch (aggregate.getGroupType()) {
..
case ROLLUP:
return ImmutableList.of(
SqlStdOperatorTable.ROLLUP.createCall(SqlParserPos.ZERO,
groupKeys));{code}
Here we create ROLLUP SQL clause ignoring the "rolling up order" of original
Aggregate.
Grouping sets "\{0,1}, \{0}, {}" and "\{0, 1}, \{1}, {}" are both classified as
ROLLUP-s and they have equal list of grouping keys (0, 1), but different SQL
needs to be generated for them.
P.S.
It looks like a fix branch for CALCITE-5416 was updated according to your
review ([https://github.com/apache/calcite/pull/2997).
|https://github.com/apache/calcite/pull/2997)]
Would you mind to have a look once you have a time for that? :)
was (Author: JIRAUSER298393):
[~julianhyde] no, it is not a duplicate, unfortunately.
CALCITE-5416 is about wrong processing of ROLLUP clause under ORDER BY clause
in some situations.
Current issue is about wrong processing of some cases of ROLLUP (regardless of
having or not having subsequent ordering).
P.S.
It looks like a fix branch for CALCITE-5416 was updated according to your
review ([https://github.com/apache/calcite/pull/2997).
|https://github.com/apache/calcite/pull/2997)]
Would you mind to have a look once you have a time for that? :)
> RelToSql converter generates invalid order of ROLLUP fields
> ---
>
> Key: CALCITE-5518
> URL: https://issues.apache.org/jira/browse/CALCITE-5518
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Major
>
> RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
> classified as `ROLLUP` grouping but with order of rollup not matching order
> of grouping fields.
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testGroupingSetsRollupNonNaturalOrder() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
> + " (\"brand_name\"), ())\n";
> final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
> + "FROM \"foodmart\".\"product\"\n"
> + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
> sql(query)
> .withPostgresql().ok(expected);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
> grouping, but right after that we generate SQL code as if grouping sets were
> \{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5518) RelToSql converter generates invalid order of ROLLUP fields
[
https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17686964#comment-17686964
]
Leonid Chistov commented on CALCITE-5518:
-
The source of this bug is the following:
{code:java}
private List generateGroupList(Builder builder,
List selectList, Aggregate aggregate, List groupList) {
.
switch (aggregate.getGroupType()) {
..
case ROLLUP:
return ImmutableList.of(
SqlStdOperatorTable.ROLLUP.createCall(SqlParserPos.ZERO,
groupKeys));{code}
Here we create ROLLUP SQL clause ignoring the "rolling up order" of original
Aggregate.
Grouping sets "\{0,1}, \{0}, {}" and "\{0, 1}, \{1}, {}" are both classified as
ROLLUP-s and they have equal list of grouping keys (0, 1), but different SQL
needs to be generated for them.
> RelToSql converter generates invalid order of ROLLUP fields
> ---
>
> Key: CALCITE-5518
> URL: https://issues.apache.org/jira/browse/CALCITE-5518
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Major
>
> RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
> classified as `ROLLUP` grouping but with order of rollup not matching order
> of grouping fields.
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testGroupingSetsRollupNonNaturalOrder() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
> + " (\"brand_name\"), ())\n";
> final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
> + "FROM \"foodmart\".\"product\"\n"
> + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
> sql(query)
> .withPostgresql().ok(expected);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
> grouping, but right after that we generate SQL code as if grouping sets were
> \{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5518) RelToSql converter generates invalid order of ROLLUP fields
[
https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17686961#comment-17686961
]
Leonid Chistov commented on CALCITE-5518:
-
[~julianhyde] no, it is not a duplicate, unfortunately.
CALCITE-5416 is about wrong processing of ROLLUP clause under ORDER BY clause
in some situations.
Current issue is about wrong processing of some cases of ROLLUP (regardless of
having or not having subsequent ordering).
P.S.
It looks like a fix branch for CALCITE-5416 was updated according to your
review ([https://github.com/apache/calcite/pull/2997).
|https://github.com/apache/calcite/pull/2997)]
Would you mind to have a look once you have a time for that? :)
> RelToSql converter generates invalid order of ROLLUP fields
> ---
>
> Key: CALCITE-5518
> URL: https://issues.apache.org/jira/browse/CALCITE-5518
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Major
>
> RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
> classified as `ROLLUP` grouping but with order of rollup not matching order
> of grouping fields.
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testGroupingSetsRollupNonNaturalOrder() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
> + " (\"brand_name\"), ())\n";
> final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
> + "FROM \"foodmart\".\"product\"\n"
> + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
> sql(query)
> .withPostgresql().ok(expected);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
> grouping, but right after that we generate SQL code as if grouping sets were
> \{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5523) RelToSql converter generates GROUP BY clause with window expression
Leonid Chistov created CALCITE-5523:
---
Summary: RelToSql converter generates GROUP BY clause with window
expression
Key: CALCITE-5523
URL: https://issues.apache.org/jira/browse/CALCITE-5523
Project: Calcite
Issue Type: Bug
Components: jdbc-adapter
Reporter: Leonid Chistov
Wrong SQL code is generated when aggregation is done on the field being a
result of window expression evaluation.
Example can be demonstrated by adding following code to
RelToSqlConverterTest.java:
{code:java}
@Test void testConvertWindowGroupByToSql() {
String query = "SELECT * FROM ("
+ "SELECT rank() over (order by \"hire_date\") \"rank\" FROM \"employee\""
+ ") GROUP BY \"rank\"";
String expected ="SELECT * FROM ("
+ "SELECT rank() over (order by \"hire_date\") AS \"$0\" FROM
\"employee\""
+ ") GROUP BY \"$0\"";
sql(query).ok(expected);
}
{code}
Generated SQL code will look like:
{code:java}
SELECT RANK() OVER (ORDER BY hire_date) AS rank
FROM foodmart.employee
GROUP BY RANK() OVER (ORDER BY hire_date){code}
This is incorrect - window expressions are not allowed in GROUP BY clause by
SQL standard and Calcite itself would produce following error message if this
SQL code would be passed as input:
{code:java}
Windowed aggregate expression is illegal in GROUP BY clause {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5519) RelToSql converter fails when aggregate under filter with correlation variable
Leonid Chistov created CALCITE-5519:
---
Summary: RelToSql converter fails when aggregate under filter with
correlation variable
Key: CALCITE-5519
URL: https://issues.apache.org/jira/browse/CALCITE-5519
Project: Calcite
Issue Type: Bug
Reporter: Leonid Chistov
The following test case would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testExistsWithAggregateBelowFilter() {
String query = "select \"product_name\" from \"product\" "
+ "where exists (select 1 from ("
+ "select b.\"customer_id\" as customer_id, max(b.\"unit_sales\") as umax
"
+ "from \"sales_fact_1997\"b "
+ "group by b.\"customer_id\") where umax > \"product_id\")";
String expected = "?";
sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
} {code}
The exception is:
{code:java}
java.lang.UnsupportedOperationException
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.getAliasContext(SqlImplementor.java:977)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:650)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1096)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:798)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:772)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:426)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:139)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:147)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:216)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:204)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:180)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:745)
at
Suppressed: java.lang.Throwable: Error while converting RelNode to SqlNode:
LogicalFilter(condition=[>($1, $cor0.product_id)])
LogicalAggregate(group=[{0}], UMAX=[MAX($1)])
LogicalProject(CUSTOMER_ID=[$2], unit_sales=[$7])
JdbcTableScan(table=[[foodmart, sales_fact_1997]]) {code}
What happens is that for tree
{code:java}
LogicalProject(product_name=[$3])
LogicalFilter(condition=[EXISTS({
LogicalFilter(condition=[>($1, $cor0.product_id)])
LogicalAggregate(group=[{0}], UMAX=[MAX($1)])
LogicalProject(CUSTOMER_ID=[$2], unit_sales=[$7])
JdbcTableScan(table=[[foodmart, sales_fact_1997]])
})], variablesSet=[[$cor0]])
JdbcTableScan(table=[[foodmart, product]]) {code}
with LogicalAggregate under LogicalFilter with correlation variable reference,
we construct a Context that is not capable of providing AliasContext.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5519) RelToSql converter fails when aggregate under filter with correlation variable
[
https://issues.apache.org/jira/browse/CALCITE-5519?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5519:
Component/s: jdbc-adapter
> RelToSql converter fails when aggregate under filter with correlation variable
> --
>
> Key: CALCITE-5519
> URL: https://issues.apache.org/jira/browse/CALCITE-5519
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Minor
>
> The following test case would fail if added to RelToSqlConverterTest.java:
> {code:java}
> @Test void testExistsWithAggregateBelowFilter() {
> String query = "select \"product_name\" from \"product\" "
> + "where exists (select 1 from ("
> + "select b.\"customer_id\" as customer_id, max(b.\"unit_sales\") as
> umax "
> + "from \"sales_fact_1997\"b "
> + "group by b.\"customer_id\") where umax > \"product_id\")";
> String expected = "?";
> sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
> } {code}
> The exception is:
> {code:java}
> java.lang.UnsupportedOperationException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.getAliasContext(SqlImplementor.java:977)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:650)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1096)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:798)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:772)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:426)
> at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
> Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:568)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:139)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:147)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:216)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:204)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:180)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:745)
> at
>
> Suppressed: java.lang.Throwable: Error while converting RelNode to
> SqlNode:
> LogicalFilter(condition=[>($1, $cor0.product_id)])
> LogicalAggregate(group=[{0}], UMAX=[MAX($1)])
> LogicalProject(CUSTOMER_ID=[$2], unit_sales=[$7])
> JdbcTableScan(table=[[foodmart, sales_fact_1997]]) {code}
> What happens is that for tree
> {code:java}
> LogicalProject(product_name=[$3])
> LogicalFilter(condition=[EXISTS({
> LogicalFilter(condition=[>($1, $cor0.product_id)])
> LogicalAggregate(group=[{0}], UMAX=[MAX($1)])
> LogicalProject(CUSTOMER_ID=[$2], unit_sales=[$7])
> JdbcTableScan(table=[[foodmart, sales_fact_1997]])
> })], variablesSet=[[$cor0]])
> JdbcTableScan(table=[[foodmart, product]]) {code}
> with LogicalAggregate under LogicalFilter with correlation variable
> reference, we construct a Context that is not capable of providing
> AliasContext.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5519) RelToSql converter fails when aggregate under filter with correlation variable
[
https://issues.apache.org/jira/browse/CALCITE-5519?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5519:
Affects Version/s: 1.32.0
> RelToSql converter fails when aggregate under filter with correlation variable
> --
>
> Key: CALCITE-5519
> URL: https://issues.apache.org/jira/browse/CALCITE-5519
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Minor
>
> The following test case would fail if added to RelToSqlConverterTest.java:
> {code:java}
> @Test void testExistsWithAggregateBelowFilter() {
> String query = "select \"product_name\" from \"product\" "
> + "where exists (select 1 from ("
> + "select b.\"customer_id\" as customer_id, max(b.\"unit_sales\") as
> umax "
> + "from \"sales_fact_1997\"b "
> + "group by b.\"customer_id\") where umax > \"product_id\")";
> String expected = "?";
> sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
> } {code}
> The exception is:
> {code:java}
> java.lang.UnsupportedOperationException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.getAliasContext(SqlImplementor.java:977)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:650)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1096)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:798)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:772)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:426)
> at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
> Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:568)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:139)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:147)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:216)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:204)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:180)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:745)
> at
>
> Suppressed: java.lang.Throwable: Error while converting RelNode to
> SqlNode:
> LogicalFilter(condition=[>($1, $cor0.product_id)])
> LogicalAggregate(group=[{0}], UMAX=[MAX($1)])
> LogicalProject(CUSTOMER_ID=[$2], unit_sales=[$7])
> JdbcTableScan(table=[[foodmart, sales_fact_1997]]) {code}
> What happens is that for tree
> {code:java}
> LogicalProject(product_name=[$3])
> LogicalFilter(condition=[EXISTS({
> LogicalFilter(condition=[>($1, $cor0.product_id)])
> LogicalAggregate(group=[{0}], UMAX=[MAX($1)])
> LogicalProject(CUSTOMER_ID=[$2], unit_sales=[$7])
> JdbcTableScan(table=[[foodmart, sales_fact_1997]])
> })], variablesSet=[[$cor0]])
> JdbcTableScan(table=[[foodmart, product]]) {code}
> with LogicalAggregate under LogicalFilter with correlation variable
> reference, we construct a Context that is not capable of providing
> AliasContext.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5518) RelToSql converter generates invalid order of ROLLUP fields
Leonid Chistov created CALCITE-5518:
---
Summary: RelToSql converter generates invalid order of ROLLUP
fields
Key: CALCITE-5518
URL: https://issues.apache.org/jira/browse/CALCITE-5518
Project: Calcite
Issue Type: Bug
Components: jdbc-adapter
Affects Versions: 1.32.0
Reporter: Leonid Chistov
RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
classified as `ROLLUP` grouping but with order of rollup not matching order of
grouping fields.
This can be demonstrated by the following test, that would fail if added to
RelToSqlConverterTest class:
{code:java}
@Test void testGroupingSetsRollupNonNaturalOrder() {
final String query = "select \"product_class_id\", \"brand_name\"\n"
+ "from \"product\"\n"
+ "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
+ " (\"brand_name\"), ())\n";
final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
sql(query)
.withPostgresql().ok(expected);
}{code}
As the result we get the following SQL code:
{code:java}
SELECT product_class_id, brand_name
FROM foodmart.product
GROUP BY ROLLUP(product_class_id, brand_name){code}
While the correct code would be:
{code:java}
SELECT product_class_id, brand_name
FROM foodmart.product
GROUP BY ROLLUP(brand_name, product_class_id){code}
Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
grouping, but right after that we generate SQL code as if grouping sets were
\{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5502) RelToSql converter generates where clause with window expression
[
https://issues.apache.org/jira/browse/CALCITE-5502?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5502:
Description:
Wrong SQL code is generated when Filter (or Calc) node contains window
expression.
Example can be demonstrated by adding following code to
RelToSqlConverterTest.java:
{code:java}
@Test void testWindowedFilter() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("DEPT")
.filter(
builder.lessThan(
builder.aggregateCall(SqlStdOperatorTable.MAX,
builder.field("DEPTNO"))
.over()
.partitionBy(builder.field("DNAME"))
.toRex(),
builder.literal(1)
)
)
.build();
final String expectedSql = "?";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
Generated SQL code will look like:
{code:java}
SELECT *
FROM \"scott\".\"DEPT\"
WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code}
This is incorrect - window expressions are not allowed in WHERE clause by SQL
standard and Calcite itself would produce following error message if this SQL
code would be passed as input:
{code:java}
Windowed aggregate expression is illegal in WHERE clause {code}
was:
Wrong SQL code is generated when Filter (or Calc) node contains window
expression.
Example can be demonstrated by adding following code to
RelToSqlConverterTest.java:
{code:java}
@Test void testWindowedFilter() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("DEPT")
.filter(
builder.lessThan(
builder.aggregateCall(SqlStdOperatorTable.MAX,
builder.field("DEPTNO"))
.over()
.partitionBy(builder.field("DNAME"))
.toRex(),
builder.literal(1)
)
)
.build();
final String expectedSql = "?";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
Generated SQL code will look like:
{code:java}
SELECT *
FROM \"scott\".\"DEPT\"
WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code}
This is incorrect - window expressions are not allowed in WHERE clause by SQL
standard and Calcite itself would produce following error message if this SQL
code would passed as input:
{code:java}
Windowed aggregate expression is illegal in WHERE clause {code}
> RelToSql converter generates where clause with window expression
>
>
> Key: CALCITE-5502
> URL: https://issues.apache.org/jira/browse/CALCITE-5502
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Major
>
> Wrong SQL code is generated when Filter (or Calc) node contains window
> expression.
> Example can be demonstrated by adding following code to
> RelToSqlConverterTest.java:
> {code:java}
> @Test void testWindowedFilter() {
> final RelBuilder builder = relBuilder();
> final RelNode root = builder
> .scan("DEPT")
> .filter(
> builder.lessThan(
> builder.aggregateCall(SqlStdOperatorTable.MAX,
> builder.field("DEPTNO"))
> .over()
> .partitionBy(builder.field("DNAME"))
> .toRex(),
> builder.literal(1)
> )
> )
> .build();
> final String expectedSql = "?";
> assertThat(toSql(root), isLinux(expectedSql));
> } {code}
> Generated SQL code will look like:
> {code:java}
> SELECT *
> FROM \"scott\".\"DEPT\"
> WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED
> PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code}
> This is incorrect - window expressions are not allowed in WHERE clause by SQL
> standard and Calcite itself would produce following error message if this SQL
> code would be passed as input:
> {code:java}
> Windowed aggregate expression is illegal in WHERE clause {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5502) RelToSql converter generates where clause with window expression
[
https://issues.apache.org/jira/browse/CALCITE-5502?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5502:
Description:
Wrong SQL code is generated when Filter (or Calc) node contains window
expression.
Example can be demonstrated by adding following code to
RelToSqlConverterTest.java:
{code:java}
@Test void testWindowedFilter() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("DEPT")
.filter(
builder.lessThan(
builder.aggregateCall(SqlStdOperatorTable.MAX,
builder.field("DEPTNO"))
.over()
.partitionBy(builder.field("DNAME"))
.toRex(),
builder.literal(1)
)
)
.build();
final String expectedSql = "?";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
Generated SQL code will look like:
{code:java}
SELECT *
FROM \"scott\".\"DEPT\"
WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code}
This is incorrect - window expressions are not allowed in WHERE clause by SQL
standard and Calcite itself would produce following error message if this SQL
code would passed as input:
{code:java}
Windowed aggregate expression is illegal in WHERE clause {code}
was:
Wrong SQL code is generated when Filter (or Calc) node contains window
expression.
Example can be demonstrated by adding following code to
RelToSqlConverterTest.java:
{code:java}
@Test void testWindowedFilter() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("DEPT")
.filter(
builder.lessThan(
builder.aggregateCall(SqlStdOperatorTable.MAX,
builder.field("DEPTNO"))
.over()
.partitionBy(builder.field("DNAME"))
.toRex(),
builder.literal(1)
)
)
.build();
final String expectedSql = "?";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
Generated SQL code will look like:
{code:java}
SELECT *
FROM \"scott\".\"DEPT\"
WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code}
This is incorrect - window expressions are not required in WHERE clause by SQL
standard and Calcite itself would produce following error message if this SQL
code would passed as input:
{code:java}
Windowed aggregate expression is illegal in WHERE clause {code}
> RelToSql converter generates where clause with window expression
>
>
> Key: CALCITE-5502
> URL: https://issues.apache.org/jira/browse/CALCITE-5502
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Major
>
> Wrong SQL code is generated when Filter (or Calc) node contains window
> expression.
> Example can be demonstrated by adding following code to
> RelToSqlConverterTest.java:
> {code:java}
> @Test void testWindowedFilter() {
> final RelBuilder builder = relBuilder();
> final RelNode root = builder
> .scan("DEPT")
> .filter(
> builder.lessThan(
> builder.aggregateCall(SqlStdOperatorTable.MAX,
> builder.field("DEPTNO"))
> .over()
> .partitionBy(builder.field("DNAME"))
> .toRex(),
> builder.literal(1)
> )
> )
> .build();
> final String expectedSql = "?";
> assertThat(toSql(root), isLinux(expectedSql));
> } {code}
> Generated SQL code will look like:
> {code:java}
> SELECT *
> FROM \"scott\".\"DEPT\"
> WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED
> PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code}
> This is incorrect - window expressions are not allowed in WHERE clause by SQL
> standard and Calcite itself would produce following error message if this SQL
> code would passed as input:
> {code:java}
> Windowed aggregate expression is illegal in WHERE clause {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5502) RelToSql converter generates where clause with window expression
Leonid Chistov created CALCITE-5502:
---
Summary: RelToSql converter generates where clause with window
expression
Key: CALCITE-5502
URL: https://issues.apache.org/jira/browse/CALCITE-5502
Project: Calcite
Issue Type: Bug
Components: jdbc-adapter
Affects Versions: 1.32.0
Reporter: Leonid Chistov
Wrong SQL code is generated when Filter (or Calc) node contains window
expression.
Example can be demonstrated by adding following code to
RelToSqlConverterTest.java:
{code:java}
@Test void testWindowedFilter() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("DEPT")
.filter(
builder.lessThan(
builder.aggregateCall(SqlStdOperatorTable.MAX,
builder.field("DEPTNO"))
.over()
.partitionBy(builder.field("DNAME"))
.toRex(),
builder.literal(1)
)
)
.build();
final String expectedSql = "?";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
Generated SQL code will look like:
{code:java}
SELECT *
FROM \"scott\".\"DEPT\"
WHERE (MAX(\"DEPTNO\") OVER (PARTITION BY \"DNAME\" RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)) < 1 {code}
This is incorrect - window expressions are not required in WHERE clause by SQL
standard and Calcite itself would produce following error message if this SQL
code would passed as input:
{code:java}
Windowed aggregate expression is illegal in WHERE clause {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5416) RelToSql converter generates invalid code when merging rollup and sort clauses
[
https://issues.apache.org/jira/browse/CALCITE-5416?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17642870#comment-17642870
]
Leonid Chistov edited comment on CALCITE-5416 at 12/3/22 4:53 PM:
--
[~jiajunbernoulli]
I somehow failed to see this test, but I would be brave enough to say that it
looks incorrect for me.
Order of fields in the ROLLUP clause does matter, we cannot just change it
without changing the semantics of ROLLUP.
It looks like in general case there is really no way to generate such clause
with a single SELECT statement.
Even *Other Considerations When using ROLLUP* section of the
[https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html] article
proposes that we use second SELECT clause for the sort that does not match the
order of fields in ROLLUP.
was (Author: JIRAUSER298393):
[~jiajunbernoulli]
I somehow failed to see this test, but I would be brave enough to say that it
looks incorrect for me.
Order of fields in the ROLLUP clause does matter, we cannot just change it
without changing the semantics of ROLLUP.
It looks like in general case there is really no way to generate such clause
with a single SELECT statement.
Even *Other Considerations When using ROLLUP* of the
[https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html] article
proposes that we use second SELECT clause for the sort that does not match the
order of fields in ROLLUP.
> RelToSql converter generates invalid code when merging rollup and sort clauses
> --
>
> Key: CALCITE-5416
> URL: https://issues.apache.org/jira/browse/CALCITE-5416
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Minor
>
> For SQL dialects (MySQL, Hive, MsSQL) that do not support "GROUP BY
> ROLLUP(...)" syntax, but do support "GROUP BY ... WITH ROLLUP" syntax
> instead, wrong code is generated by RelToSqlConverter in the following
> situation:
> * There is an Aggregate node with ROLLUP grouping
> * It has a parent Sort node with an order of fields different from the order
> of fields in ROLLUP Aggregation
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testSelectQueryWithGroupByRollupOrderByReversed() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by rollup(\"product_class_id\", \"brand_name\")\n"
> + "order by 2, 1";
> final String expectedMysql = "SELECT `product_class_id`, `brand_name`\n"
> + "FROM `foodmart`.`product`\n"
> + "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP";
> sql(query)
> .withMysql().ok(expectedMysql);
> }
> {code}
> As the result we get the following SQL code:
> {code:java}
> SELECT `product_class_id`, `brand_name
> FROM `foodmart`.`product
> GROUP BY `brand_name`, `product_class_id` WITH ROLLUP {code}
> It can be observed that order of fields of aggregation was changed to match
> the order of fields in ORDER clause, thus changing the semantics of the
> ROLLUP clause itself.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5416) RelToSql converter generates invalid code when merging rollup and sort clauses
[
https://issues.apache.org/jira/browse/CALCITE-5416?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17642870#comment-17642870
]
Leonid Chistov commented on CALCITE-5416:
-
[~jiajunbernoulli]
I somehow failed to see this test, but I would be brave enough to say that it
looks incorrect for me.
Order of fields in the ROLLUP clause does matter, we cannot just change it
without changing the semantics of ROLLUP.
It looks like in general case there is really no way to generate such clause
with a single SELECT statement.
Even *Other Considerations When using ROLLUP* of the
[https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html] article
proposes that we use second SELECT clause for the sort that does not match the
order of fields in ROLLUP.
> RelToSql converter generates invalid code when merging rollup and sort clauses
> --
>
> Key: CALCITE-5416
> URL: https://issues.apache.org/jira/browse/CALCITE-5416
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Minor
>
> For SQL dialects (MySQL, Hive, MsSQL) that do not support "GROUP BY
> ROLLUP(...)" syntax, but do support "GROUP BY ... WITH ROLLUP" syntax
> instead, wrong code is generated by RelToSqlConverter in the following
> situation:
> * There is an Aggregate node with ROLLUP grouping
> * It has a parent Sort node with an order of fields different from the order
> of fields in ROLLUP Aggregation
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testSelectQueryWithGroupByRollupOrderByReversed() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by rollup(\"product_class_id\", \"brand_name\")\n"
> + "order by 2, 1";
> final String expectedMysql = "SELECT `product_class_id`, `brand_name`\n"
> + "FROM `foodmart`.`product`\n"
> + "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP";
> sql(query)
> .withMysql().ok(expectedMysql);
> }
> {code}
> As the result we get the following SQL code:
> {code:java}
> SELECT `product_class_id`, `brand_name
> FROM `foodmart`.`product
> GROUP BY `brand_name`, `product_class_id` WITH ROLLUP {code}
> It can be observed that order of fields of aggregation was changed to match
> the order of fields in ORDER clause, thus changing the semantics of the
> ROLLUP clause itself.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5412) JDBC adapter incorrectly generates CUBE for MySQL 5
[ https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17642390#comment-17642390 ] Leonid Chistov commented on CALCITE-5412: - Extracted ROLLUP/order bug into separate ticket https://issues.apache.org/jira/browse/CALCITE-5416 > JDBC adapter incorrectly generates CUBE for MySQL 5 > --- > > Key: CALCITE-5412 > URL: https://issues.apache.org/jira/browse/CALCITE-5412 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.32.0 >Reporter: Leonid Chistov >Priority: Trivial > > CUBE clause seems to be not supported in MYSQL5 in any form > ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but > there is no check for that in Calcite. > Thus, SQL statement with CUBE clause may be erroneously generated by JDBC > adapter for MySQL 5 dialect, causing syntax error issue later on. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5412) JDBC adapter incorrectly generates CUBE for MySQL 5
[ https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov updated CALCITE-5412: Description: CUBE clause seems to be not supported in MYSQL5 in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but there is no check for that in Calcite. Thus, SQL statement with CUBE clause may be erroneously generated by JDBC adapter for MySQL 5 dialect, causing syntax error issue later on. was:CUBE clause seems to be not supported in MYSQL5.x in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we don’t check it. > JDBC adapter incorrectly generates CUBE for MySQL 5 > --- > > Key: CALCITE-5412 > URL: https://issues.apache.org/jira/browse/CALCITE-5412 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.32.0 >Reporter: Leonid Chistov >Priority: Trivial > > CUBE clause seems to be not supported in MYSQL5 in any form > ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but > there is no check for that in Calcite. > Thus, SQL statement with CUBE clause may be erroneously generated by JDBC > adapter for MySQL 5 dialect, causing syntax error issue later on. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5412) JDBC adapter incorrectly generates CUBE for MySQL 5
[ https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov updated CALCITE-5412: Description: CUBE clause seems to be not supported in MYSQL5.x in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we don’t check it. (was: There are currently several dialects supported by Calcite which share a logic of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, MsSQL, Hive and Spark. Note: actually Spark should not be on this list anymore, see https://issues.apache.org/jira/browse/CALCITE-5411 Following issues exist regarding current implementation of this logic: * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check 'hasTrickyRollup' does not take into account possibility of different order of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose information about order of fields in 'ORDER BY' clause in the conversion under this check. * CUBE clause seems to be not supported in MYSQL5.x in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we don’t check it. * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax support, thus we always generate SQL code for such clause, as if it was supported) > JDBC adapter incorrectly generates CUBE for MySQL 5 > --- > > Key: CALCITE-5412 > URL: https://issues.apache.org/jira/browse/CALCITE-5412 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.32.0 >Reporter: Leonid Chistov >Priority: Minor > > CUBE clause seems to be not supported in MYSQL5.x in any form > ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we > don’t check it. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5412) JDBC adapter incorrectly generates CUBE for MySQL 5
[ https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov updated CALCITE-5412: Priority: Trivial (was: Minor) > JDBC adapter incorrectly generates CUBE for MySQL 5 > --- > > Key: CALCITE-5412 > URL: https://issues.apache.org/jira/browse/CALCITE-5412 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.32.0 >Reporter: Leonid Chistov >Priority: Trivial > > CUBE clause seems to be not supported in MYSQL5.x in any form > ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we > don’t check it. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5412) JDBC adapter incorrectly generates CUBE for MySQL 5
[ https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov updated CALCITE-5412: Summary: JDBC adapter incorrectly generates CUBE for MySQL 5 (was: ROLLUP/CUBE non-standard syntax support logic is buggy) > JDBC adapter incorrectly generates CUBE for MySQL 5 > --- > > Key: CALCITE-5412 > URL: https://issues.apache.org/jira/browse/CALCITE-5412 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.32.0 >Reporter: Leonid Chistov >Priority: Minor > > There are currently several dialects supported by Calcite which share a logic > of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax > instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, > MsSQL, Hive and Spark. > Note: actually Spark should not be on this list anymore, see > https://issues.apache.org/jira/browse/CALCITE-5411 > Following issues exist regarding current implementation of this logic: > * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check > 'hasTrickyRollup' does not take into account possibility of different order > of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose > information about order of fields in 'ORDER BY' clause in the conversion > under this check. > * CUBE clause seems to be not supported in MYSQL5.x in any form > ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we > don’t check it. > * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax > support, thus we always generate SQL code for such clause, as if it was > supported -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5416) RelToSql converter generates invalid code when merging rollup and sort clauses
Leonid Chistov created CALCITE-5416:
---
Summary: RelToSql converter generates invalid code when merging
rollup and sort clauses
Key: CALCITE-5416
URL: https://issues.apache.org/jira/browse/CALCITE-5416
Project: Calcite
Issue Type: Bug
Affects Versions: 1.32.0
Reporter: Leonid Chistov
For SQL dialects (MySQL, Hive, MsSQL) that do not support "GROUP BY
ROLLUP(...)" syntax, but do support "GROUP BY ... WITH ROLLUP" syntax instead,
wrong code is generated by RelToSqlConverter in the following situation:
* There is an Aggregate node with ROLLUP grouping
* It has a parent Sort node with an order of fields different from the order
of fields in ROLLUP Aggregation
This can be demonstrated by the following test, that would fail if added to
RelToSqlConverterTest class:
{code:java}
@Test void testSelectQueryWithGroupByRollupOrderByReversed() {
final String query = "select \"product_class_id\", \"brand_name\"\n"
+ "from \"product\"\n"
+ "group by rollup(\"product_class_id\", \"brand_name\")\n"
+ "order by 2, 1";
final String expectedMysql = "SELECT `product_class_id`, `brand_name`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP";
sql(query)
.withMysql().ok(expectedMysql);
}
{code}
As the result we get the following SQL code:
{code:java}
SELECT `product_class_id`, `brand_name
FROM `foodmart`.`product
GROUP BY `brand_name`, `product_class_id` WITH ROLLUP {code}
It can be observed that order of fields of aggregation was changed to match the
order of fields in ORDER clause, thus changing the semantics of the ROLLUP
clause itself.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5412) ROLLUP/CUBE non-standard syntax support logic is buggy
[ https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17642143#comment-17642143 ] Leonid Chistov commented on CALCITE-5412: - [~julianhyde] Sure, will do, thanks for review. About the "GROUPING SETS" I was probably not specific enough, I meant that we have an issue with all three dialects mentioned above: * For HIVE it seems to be supported only in the 'postfix' format "GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ))." * For MySQL5.7 it seems to be not supported at all * For MsSQL - I actually start to wonder if this dialect assumes all versions of SQL Server up to the current one (2022). If this is true, then we have another bug: we don't specify that it (at least new versions) supports ROLLUP(...) and CUBE(...) syntax in addition to non-standard WITH ROLLUP and WITH CUBE. > ROLLUP/CUBE non-standard syntax support logic is buggy > -- > > Key: CALCITE-5412 > URL: https://issues.apache.org/jira/browse/CALCITE-5412 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.32.0 >Reporter: Leonid Chistov >Priority: Minor > > There are currently several dialects supported by Calcite which share a logic > of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax > instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, > MsSQL, Hive and Spark. > Note: actually Spark should not be on this list anymore, see > https://issues.apache.org/jira/browse/CALCITE-5411 > Following issues exist regarding current implementation of this logic: > * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check > 'hasTrickyRollup' does not take into account possibility of different order > of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose > information about order of fields in 'ORDER BY' clause in the conversion > under this check. > * CUBE clause seems to be not supported in MYSQL5.x in any form > ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we > don’t check it. > * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax > support, thus we always generate SQL code for such clause, as if it was > supported -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5412) ROLLUP/CUBE non-standard syntax support logic is buggy
[ https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov updated CALCITE-5412: Description: There are currently several dialects supported by Calcite which share a logic of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, MsSQL, Hive and Spark. Note: actually Spark should not be on this list anymore, see https://issues.apache.org/jira/browse/CALCITE-5411 Following issues exist regarding current implementation of this logic: * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check 'hasTrickyRollup' does not take into account possibility of different order of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose information about order of fields in 'ORDER BY' clause in the conversion under this check. * CUBE clause seems to be not supported in MYSQL5.x in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we don’t check it. * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax support, thus we always generate SQL code for such clause, is it was supported was: There are currently several dialects supported by Calcite which share a logic of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, MsSQL, Hive and Spark. Note: actually Spark should not be on this list anymore, see https://issues.apache.org/jira/browse/CALCITE-5411 Following issues exist regarding current implementation of this logic: * There is a bug in Result RelToSqlConverter::visit(Sort e). Check 'hasTrickyRollup' does not take into account possibility of different order of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose information about order of fields in 'ORDER BY' clause in the conversion under this check. * CUBE clause seems to be not supported in MYSQL5.x in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we don’t check it. * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax support, thus we always generate SQL code for such clause, is it was supported > ROLLUP/CUBE non-standard syntax support logic is buggy > -- > > Key: CALCITE-5412 > URL: https://issues.apache.org/jira/browse/CALCITE-5412 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.32.0 >Reporter: Leonid Chistov >Priority: Minor > > There are currently several dialects supported by Calcite which share a logic > of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax > instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, > MsSQL, Hive and Spark. > Note: actually Spark should not be on this list anymore, see > https://issues.apache.org/jira/browse/CALCITE-5411 > Following issues exist regarding current implementation of this logic: > * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check > 'hasTrickyRollup' does not take into account possibility of different order > of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose > information about order of fields in 'ORDER BY' clause in the conversion > under this check. > * CUBE clause seems to be not supported in MYSQL5.x in any form > ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we > don’t check it. > * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax > support, thus we always generate SQL code for such clause, is it was supported -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5412) ROLLUP/CUBE non-standard syntax support logic is buggy
[ https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov updated CALCITE-5412: Description: There are currently several dialects supported by Calcite which share a logic of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, MsSQL, Hive and Spark. Note: actually Spark should not be on this list anymore, see https://issues.apache.org/jira/browse/CALCITE-5411 Following issues exist regarding current implementation of this logic: * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check 'hasTrickyRollup' does not take into account possibility of different order of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose information about order of fields in 'ORDER BY' clause in the conversion under this check. * CUBE clause seems to be not supported in MYSQL5.x in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we don’t check it. * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax support, thus we always generate SQL code for such clause, as if it was supported was: There are currently several dialects supported by Calcite which share a logic of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, MsSQL, Hive and Spark. Note: actually Spark should not be on this list anymore, see https://issues.apache.org/jira/browse/CALCITE-5411 Following issues exist regarding current implementation of this logic: * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check 'hasTrickyRollup' does not take into account possibility of different order of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose information about order of fields in 'ORDER BY' clause in the conversion under this check. * CUBE clause seems to be not supported in MYSQL5.x in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we don’t check it. * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax support, thus we always generate SQL code for such clause, is it was supported > ROLLUP/CUBE non-standard syntax support logic is buggy > -- > > Key: CALCITE-5412 > URL: https://issues.apache.org/jira/browse/CALCITE-5412 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.32.0 >Reporter: Leonid Chistov >Priority: Minor > > There are currently several dialects supported by Calcite which share a logic > of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax > instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, > MsSQL, Hive and Spark. > Note: actually Spark should not be on this list anymore, see > https://issues.apache.org/jira/browse/CALCITE-5411 > Following issues exist regarding current implementation of this logic: > * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check > 'hasTrickyRollup' does not take into account possibility of different order > of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose > information about order of fields in 'ORDER BY' clause in the conversion > under this check. > * CUBE clause seems to be not supported in MYSQL5.x in any form > ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we > don’t check it. > * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax > support, thus we always generate SQL code for such clause, as if it was > supported -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5412) ROLLUP/CUBE non-standard syntax support logic is buggy
Leonid Chistov created CALCITE-5412: --- Summary: ROLLUP/CUBE non-standard syntax support logic is buggy Key: CALCITE-5412 URL: https://issues.apache.org/jira/browse/CALCITE-5412 Project: Calcite Issue Type: Bug Affects Versions: 1.32.0 Reporter: Leonid Chistov There are currently several dialects supported by Calcite which share a logic of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5, MsSQL, Hive and Spark. Note: actually Spark should not be on this list anymore, see https://issues.apache.org/jira/browse/CALCITE-5411 Following issues exist regarding current implementation of this logic: * There is a bug in Result RelToSqlConverter::visit(Sort e). Check 'hasTrickyRollup' does not take into account possibility of different order of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose information about order of fields in 'ORDER BY' clause in the conversion under this check. * CUBE clause seems to be not supported in MYSQL5.x in any form ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we don’t check it. * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax support, thus we always generate SQL code for such clause, is it was supported -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5411) Update Spark Dialect to support ROLLUP & CUBE aggregate functions.
[
https://issues.apache.org/jira/browse/CALCITE-5411?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5411:
Labels: pull-request-available (was: )
> Update Spark Dialect to support ROLLUP & CUBE aggregate functions.
> --
>
> Key: CALCITE-5411
> URL: https://issues.apache.org/jira/browse/CALCITE-5411
> Project: Calcite
> Issue Type: Task
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
>
> Support for standard
> {code:java}
> GROUP BY { group_expression |
> { ROLLUP | CUBE | GROUPING SETS }
> (grouping_set [ , ...]) } [ , ... ] {code}
> syntax was added to Spark several releases ago in addition to the previously
> supported non-standard syntax
> {code:java}
> GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH { ROLLUP |
> CUBE } ] {code}
> See:
> [https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html]
> We need to update *SparkSqlDialect* implementation to reflect these changes
> in supported SQL syntax.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5411) Update Spark Dialect to support ROLLUP & CUBE aggregate functions.
[
https://issues.apache.org/jira/browse/CALCITE-5411?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5411:
Description:
Support for standard
{code:java}
GROUP BY { group_expression |
{ ROLLUP | CUBE | GROUPING SETS }
(grouping_set [ , ...]) } [ , ... ] {code}
syntax was added to Spark several releases ago in addition to the previously
supported non-standard syntax
{code:java}
GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH { ROLLUP |
CUBE } ] {code}
See:
[https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html]
We need to update *SparkSqlDialect* implementation to reflect these changes in
supported SQL syntax.
was:
Support for standard
{code:java}
GROUP BY { group_expression |
{ ROLLUP | CUBE | GROUPING SETS }
(grouping_set [ , ...]) } [ , ... ] {code}
syntax was added to Spark several releases ago in addition to the previously
supported non-standard syntax
{code:java}
GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH { ROLLUP |
CUBE } ] {code}
See:
[https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html]
We need to update `SparkSqlDialect` implementation to reflect these changes in
supported SQL syntax.
> Update Spark Dialect to support ROLLUP & CUBE aggregate functions.
> --
>
> Key: CALCITE-5411
> URL: https://issues.apache.org/jira/browse/CALCITE-5411
> Project: Calcite
> Issue Type: Task
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> Support for standard
> {code:java}
> GROUP BY { group_expression |
> { ROLLUP | CUBE | GROUPING SETS }
> (grouping_set [ , ...]) } [ , ... ] {code}
> syntax was added to Spark several releases ago in addition to the previously
> supported non-standard syntax
> {code:java}
> GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH { ROLLUP |
> CUBE } ] {code}
> See:
> [https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html]
> We need to update *SparkSqlDialect* implementation to reflect these changes
> in supported SQL syntax.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5411) Update Spark Dialect to support ROLLUP & CUBE aggregate functions.
[
https://issues.apache.org/jira/browse/CALCITE-5411?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5411:
Description:
Support for standard
{code:java}
GROUP BY { group_expression |
{ ROLLUP | CUBE | GROUPING SETS }
(grouping_set [ , ...]) } [ , ... ] {code}
syntax was added to Spark several releases ago in addition to the previously
supported non-standard syntax
{code:java}
GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH { ROLLUP |
CUBE } ] {code}
See:
[https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html]
We need to update `SparkSqlDialect` implementation to reflect these changes in
supported SQL syntax.
was:
Support for standard
GROUP BY \{ group_expression | { ROLLUP | CUBE | GROUPING SETS } (grouping_set
[ , ...]) } [ , ... ]
syntax was added to Spark several releases ago in addition to the previously
supported non-standard syntax
GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH \{ ROLLUP |
CUBE } ]
See:
[https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html]
We need to update `SparkSqlDialect` implementation to reflect these changes in
supported SQL syntax.
> Update Spark Dialect to support ROLLUP & CUBE aggregate functions.
> --
>
> Key: CALCITE-5411
> URL: https://issues.apache.org/jira/browse/CALCITE-5411
> Project: Calcite
> Issue Type: Task
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
>
> Support for standard
> {code:java}
> GROUP BY { group_expression |
> { ROLLUP | CUBE | GROUPING SETS }
> (grouping_set [ , ...]) } [ , ... ] {code}
> syntax was added to Spark several releases ago in addition to the previously
> supported non-standard syntax
> {code:java}
> GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH { ROLLUP |
> CUBE } ] {code}
> See:
> [https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html]
> We need to update `SparkSqlDialect` implementation to reflect these changes
> in supported SQL syntax.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5411) Update Spark Dialect to support ROLLUP & CUBE aggregate functions.
Leonid Chistov created CALCITE-5411:
---
Summary: Update Spark Dialect to support ROLLUP & CUBE aggregate
functions.
Key: CALCITE-5411
URL: https://issues.apache.org/jira/browse/CALCITE-5411
Project: Calcite
Issue Type: Task
Affects Versions: 1.32.0
Reporter: Leonid Chistov
Assignee: Leonid Chistov
Support for standard
GROUP BY \{ group_expression | { ROLLUP | CUBE | GROUPING SETS } (grouping_set
[ , ...]) } [ , ... ]
syntax was added to Spark several releases ago in addition to the previously
supported non-standard syntax
GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH \{ ROLLUP |
CUBE } ]
See:
[https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html]
We need to update `SparkSqlDialect` implementation to reflect these changes in
supported SQL syntax.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5402) RelToSql generates invalid code if left and right side field names clash
[
https://issues.apache.org/jira/browse/CALCITE-5402?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5402:
Description:
Wrong SQL code is generated when left and right side of a semi-join have fields
with same name.
Generated condition looks like `FIELD` = `rhs table`.`FIELD`, where left
`FIELD` is resolved to be the same as `rhs table`.`FIELD` during query
execution, but not a reference to an outer table of correlated subquery, as was
intended.
Examples of tests that would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testSemiJoinNameClash() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("DEPT")
.project(builder.field("DEPTNO"), builder.field("DNAME"))
.scan("EMP")
.filter(
builder.call(SqlStdOperatorTable.GREATER_THAN,
builder.field("JOB"),
builder.literal((short) 10)))
.project(builder.field("DEPTNO"))
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "DEPTNO"),
builder.field(2, 1, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
final String expectedSql = "SELECT \"DEPTNO\"\n"
+ "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
+ "FROM \"scott\".\"DEPT\"\n"
+ "WHERE EXISTS (SELECT 1\n"
+ "FROM (SELECT \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE \"JOB\" > 10) AS \"t1\"\n"
+ "WHERE \"DEPT\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
{code:java}
@Test void testSemiJoinWithSameTable() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("EMP")
.project(builder.field("DEPTNO"), builder.field("EMPNO"))
.scan("EMP")
.filter(
builder.call(SqlStdOperatorTable.GREATER_THAN,
builder.field("JOB"),
builder.literal((short) 10)))
.project(builder.field("EMPNO"))
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "EMPNO"),
builder.field(2, 1, "EMPNO")))
.project(builder.field("DEPTNO"))
.build();
final String expectedSql = "SELECT \"DEPTNO\"\n"
+ "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE EXISTS (SELECT 1\n"
+ "FROM (SELECT \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE \"JOB\" > 10) AS \"t1\"\n"
+ "WHERE \"EMP\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
was:
Wrong SQL code is generated when left and right side of a semi-join have fields
with same name.
Generated condition looks like `FIELD` = `rhs table`.`FIELD`, where left
`FIELD` is resolved to be the same as `rhs table`.`FIELD` during query
execution, but not a reference to an outer table of correlated subquery, as was
intended.
Examples of tests that would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testSemiJoinNameClash() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("DEPT")
.project(builder.field("DEPTNO"), builder.field("DNAME"))
.scan("EMP")
.filter(
builder.call(SqlStdOperatorTable.GREATER_THAN,
builder.field("JOB"),
builder.literal((short) 10)))
.project(builder.field("DEPTNO"))
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "DEPTNO"),
builder.field(2, 1, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
final String expectedSql = "SELECT \"DEPTNO\"\n"
+ "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE EXISTS (SELECT 1\n"
+ "FROM (SELECT \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE \"JOB\" > 10) AS \"t1\"\n"
+ "WHERE \"EMP\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
{code:java}
@Test void testSemiJoinWithSameTable() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("EMP")
.project(builder.field("DEPTNO"), builder.field("EMPNO"))
.scan("EMP")
.filter(
builder.call(SqlStdOperatorTable.GREATER_THAN,
builder.field("JOB"),
builder.literal((short) 10)))
.project(builder.field("EMPNO"))
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "EMPNO"),
builder.field(2, 1, "EMPNO")))
.project(builder.field("DEPTNO"))
.build();
final String expectedSql = "SELECT \"DEPTNO\"\n"
+ "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE EXISTS (SELECT 1\n"
+ "FROM (SELECT \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
[jira] [Created] (CALCITE-5402) RelToSql generates invalid code if left and right side field names clash
Leonid Chistov created CALCITE-5402:
---
Summary: RelToSql generates invalid code if left and right side
field names clash
Key: CALCITE-5402
URL: https://issues.apache.org/jira/browse/CALCITE-5402
Project: Calcite
Issue Type: Bug
Affects Versions: 1.32.0
Reporter: Leonid Chistov
Wrong SQL code is generated when left and right side of a semi-join have fields
with same name.
Generated condition looks like `FIELD` = `rhs table`.`FIELD`, where left
`FIELD` is resolved to be the same as `rhs table`.`FIELD` during query
execution, but not a reference to an outer table of correlated subquery, as was
intended.
Examples of tests that would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testSemiJoinNameClash() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("DEPT")
.project(builder.field("DEPTNO"), builder.field("DNAME"))
.scan("EMP")
.filter(
builder.call(SqlStdOperatorTable.GREATER_THAN,
builder.field("JOB"),
builder.literal((short) 10)))
.project(builder.field("DEPTNO"))
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "DEPTNO"),
builder.field(2, 1, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
final String expectedSql = "SELECT \"DEPTNO\"\n"
+ "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE EXISTS (SELECT 1\n"
+ "FROM (SELECT \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE \"JOB\" > 10) AS \"t1\"\n"
+ "WHERE \"EMP\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
{code:java}
@Test void testSemiJoinWithSameTable() {
final RelBuilder builder = relBuilder();
final RelNode root = builder
.scan("EMP")
.project(builder.field("DEPTNO"), builder.field("EMPNO"))
.scan("EMP")
.filter(
builder.call(SqlStdOperatorTable.GREATER_THAN,
builder.field("JOB"),
builder.literal((short) 10)))
.project(builder.field("EMPNO"))
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "EMPNO"),
builder.field(2, 1, "EMPNO")))
.project(builder.field("DEPTNO"))
.build();
final String expectedSql = "SELECT \"DEPTNO\"\n"
+ "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE EXISTS (SELECT 1\n"
+ "FROM (SELECT \"EMPNO\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE \"JOB\" > 10) AS \"t1\"\n"
+ "WHERE \"EMP\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
assertThat(toSql(root), isLinux(expectedSql));
} {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5395) RelToSql converter fails when SELECT * is under a semi-join node
[
https://issues.apache.org/jira/browse/CALCITE-5395?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5395:
Description:
The following test case would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testUnionUnderSemiJoinNode() {
final RelBuilder builder = relBuilder();
final RelNode base = builder
.scan("EMP")
.scan("EMP")
.union(true)
.build();
final RelNode root = builder
.push(base)
.scan("DEPT")
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 1, "DEPTNO"),
builder.field(2, 0, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
toSql(root);
} {code}
The exception is:
{code:java}
Index 7 out of bounds for length 1
java.lang.IndexOutOfBoundsException: Index 7 out of bounds for length 1
at
java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
at
java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
at
java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266)
at java.base/java.util.Objects.checkIndex(Objects.java:359)
at java.base/java.util.ArrayList.get(ArrayList.java:427)
at org.apache.calcite.sql.SqlNodeList.get(SqlNodeList.java:160)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter$AliasReplacementShuttle.visit(RelToSqlConverter.java:197)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter$AliasReplacementShuttle.visit(RelToSqlConverter.java:179)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:954)
at org.apache.calcite.sql.util.SqlShuttle.visit(SqlShuttle.java:68)
at org.apache.calcite.sql.util.SqlShuttle.visit(SqlShuttle.java:41)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitAntiOrSemiJoin(RelToSqlConverter.java:261)
{code}
What happens is that `AliasReplacementShuttle` expects a select list as a
source of replacement, but cannot handle a case when select node has a `select
*` form.
In case of current test, `union` is transformed to `select *` form before
semi-join handling occurs.
was:
The following test case would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testUnionUnderSemiJoinNode() {
final RelBuilder builder = relBuilder();
final RelNode base = builder
.scan("EMP")
.scan("EMP")
.union(true)
.build();
final RelNode root = builder
.push(base)
.scan("DEPT")
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 1, "DEPTNO"),
builder.field(2, 0, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
toSql(root);
} {code}
The exception is:
{code:java}
Index 7 out of bounds for length 1
java.lang.IndexOutOfBoundsException: Index 7 out of bounds for length 1
at
java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
at
java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
at
java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266)
at java.base/java.util.Objects.checkIndex(Objects.java:359)
at java.base/java.util.ArrayList.get(ArrayList.java:427)
at org.apache.calcite.sql.SqlNodeList.get(SqlNodeList.java:160)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter$AliasReplacementShuttle.visit(RelToSqlConverter.java:197)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter$AliasReplacementShuttle.visit(RelToSqlConverter.java:179)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:954)
at org.apache.calcite.sql.util.SqlShuttle.visit(SqlShuttle.java:68)
at org.apache.calcite.sql.util.SqlShuttle.visit(SqlShuttle.java:41)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitAntiOrSemiJoin(RelToSqlConverter.java:261)
{code}
What happens is that `AliasReplacementShuttle` expects a select list as a
source of replacement, but cannot handle a case when select node has a `select
*` form.
In case of current test, `union` is transformed to `select *` form before
semi-join handling occurs.
> RelToSql converter fails when SELECT * is under a semi-join node
> ---
[jira] [Updated] (CALCITE-5394) RelToSql converter fails when semi-join is under a join node
[
https://issues.apache.org/jira/browse/CALCITE-5394?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5394:
Description:
The following test case would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testSemiJoinUnderJoin() {
final RelBuilder builder = relBuilder();
final RelNode base = builder
.scan("EMP")
.scan("EMP")
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "EMPNO"),
builder.field(2, 1, "EMPNO")))
.build();
final RelNode root = builder
.scan("DEPT")
.push(base)
.join(
JoinRelType.INNER, builder.equals(
builder.field(2, 1, "DEPTNO"),
builder.field(2, 0, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
toSql(root);
} {code}
The exception is:
{code:java}
alias
java.lang.NullPointerException: alias
at java.base/java.util.Objects.requireNonNull(Objects.java:233)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:493)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:491)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.result(SqlImplementor.java:476)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:246)
{code}
Cause of failure seems to be that `RelToSqlConverter::visitAntiOrSemiJoin`
rewrites semi-join as select with no associated alias.
was:
The following test case would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testSemiJoinUnderJoin() {
final RelBuilder builder = relBuilder();
final RelNode base = builder
.scan("EMP")
.scan("EMP")
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "EMPNO"),
builder.field(2, 1, "EMPNO")))
.build();
final RelNode root = builder
.scan("DEPT")
.push(base)
.join(
JoinRelType.INNER, builder.equals(
builder.field(2, 1, "DEPTNO"),
builder.field(2, 0, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
toSql(root);
} {code}
The exception is:
{code:java}
alias
java.lang.NullPointerException: alias
at java.base/java.util.Objects.requireNonNull(Objects.java:233)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:493)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:491)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.result(SqlImplementor.java:476)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:246)
{code}
Cause of failure seems to be that `RelToSqlConverter::visitAntiOrSemiJoin`
rewrites semi-join as select with no associated alias.
> RelToSql converter fails when semi-join is under a join node
>
>
> Key: CALCITE-5394
> URL: https://issues.apache.org/jira/browse/CALCITE-5394
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Minor
>
> The following test case would fail if added to RelToSqlConverterTest.java:
> {code:java}
> @Test void testSemiJoinUnderJoin() {
> final RelBuilder builder = relBuilder();
> final RelNode base = builder
> .scan("EMP")
> .scan("EMP")
> .join(
> JoinRelType.SEMI, builder.equals(
> builder.field(2, 0, "EMPNO"),
> builder.field(2, 1, "EMPNO")))
> .build();
> final RelNode root = builder
> .scan("DEPT")
> .push(base)
> .join(
> JoinRelType.INNER, builder.equals(
> builder.field(2, 1, "DEPTNO"),
> builder.field(2, 0, "DEPTNO")))
> .project(builder.field("DEPTNO"))
> .build();
> toSql(root);
> } {code}
> The exception is:
> {code:java}
> alias
> java.lang.NullPointerException: alias
> at java.base/java.util.Objects.requireNonNull(Objects.java:233)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:493)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:491)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.result(SqlImplementor.java:476)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:246)
> {code}
> Cause of failure seems to be that `RelToSqlConverter::visitAntiOrSemiJoin`
> rewrites semi-join as select with no associated alias.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5395) RelToSql converter fails when SELECT * is under a semi-join node
Leonid Chistov created CALCITE-5395:
---
Summary: RelToSql converter fails when SELECT * is under a
semi-join node
Key: CALCITE-5395
URL: https://issues.apache.org/jira/browse/CALCITE-5395
Project: Calcite
Issue Type: Bug
Affects Versions: 1.32.0
Reporter: Leonid Chistov
The following test case would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testUnionUnderSemiJoinNode() {
final RelBuilder builder = relBuilder();
final RelNode base = builder
.scan("EMP")
.scan("EMP")
.union(true)
.build();
final RelNode root = builder
.push(base)
.scan("DEPT")
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 1, "DEPTNO"),
builder.field(2, 0, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
toSql(root);
} {code}
The exception is:
{code:java}
Index 7 out of bounds for length 1
java.lang.IndexOutOfBoundsException: Index 7 out of bounds for length 1
at
java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
at
java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
at
java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266)
at java.base/java.util.Objects.checkIndex(Objects.java:359)
at java.base/java.util.ArrayList.get(ArrayList.java:427)
at org.apache.calcite.sql.SqlNodeList.get(SqlNodeList.java:160)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter$AliasReplacementShuttle.visit(RelToSqlConverter.java:197)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter$AliasReplacementShuttle.visit(RelToSqlConverter.java:179)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:954)
at org.apache.calcite.sql.util.SqlShuttle.visit(SqlShuttle.java:68)
at org.apache.calcite.sql.util.SqlShuttle.visit(SqlShuttle.java:41)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitAntiOrSemiJoin(RelToSqlConverter.java:261)
{code}
What happens is that `AliasReplacementShuttle` expects a select list as a
source of replacement, but cannot handle a case when select node has a `select
*` form.
In case of current test, `union` is transformed to `select *` form before
semi-join handling occurs.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5394) RelToSql converter fails when semi-join is under a join node
[
https://issues.apache.org/jira/browse/CALCITE-5394?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5394:
Priority: Minor (was: Major)
> RelToSql converter fails when semi-join is under a join node
>
>
> Key: CALCITE-5394
> URL: https://issues.apache.org/jira/browse/CALCITE-5394
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Priority: Minor
>
> The following test case would fail if added to RelToSqlConverterTest.java:
> {code:java}
> @Test void testSemiJoinUnderJoin() {
> final RelBuilder builder = relBuilder();
> final RelNode base = builder
> .scan("EMP")
> .scan("EMP")
> .join(
> JoinRelType.SEMI, builder.equals(
> builder.field(2, 0, "EMPNO"),
> builder.field(2, 1, "EMPNO")))
> .build();
> final RelNode root = builder
> .scan("DEPT")
> .push(base)
> .join(
> JoinRelType.INNER, builder.equals(
> builder.field(2, 1, "DEPTNO"),
> builder.field(2, 0, "DEPTNO")))
> .project(builder.field("DEPTNO"))
> .build();
> toSql(root);
> } {code}
> The exception is:
>
> {code:java}
> alias
> java.lang.NullPointerException: alias
> at java.base/java.util.Objects.requireNonNull(Objects.java:233)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:493)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:491)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.result(SqlImplementor.java:476)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:246)
> {code}
> Cause of failure seems to be that `RelToSqlConverter::visitAntiOrSemiJoin`
> rewrites semi-join as select with no associated alias.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5394) RelToSql converter fails when semi-join is under a join node
Leonid Chistov created CALCITE-5394:
---
Summary: RelToSql converter fails when semi-join is under a join
node
Key: CALCITE-5394
URL: https://issues.apache.org/jira/browse/CALCITE-5394
Project: Calcite
Issue Type: Bug
Affects Versions: 1.32.0
Reporter: Leonid Chistov
The following test case would fail if added to RelToSqlConverterTest.java:
{code:java}
@Test void testSemiJoinUnderJoin() {
final RelBuilder builder = relBuilder();
final RelNode base = builder
.scan("EMP")
.scan("EMP")
.join(
JoinRelType.SEMI, builder.equals(
builder.field(2, 0, "EMPNO"),
builder.field(2, 1, "EMPNO")))
.build();
final RelNode root = builder
.scan("DEPT")
.push(base)
.join(
JoinRelType.INNER, builder.equals(
builder.field(2, 1, "DEPTNO"),
builder.field(2, 0, "DEPTNO")))
.project(builder.field("DEPTNO"))
.build();
toSql(root);
} {code}
The exception is:
{code:java}
alias
java.lang.NullPointerException: alias
at java.base/java.util.Objects.requireNonNull(Objects.java:233)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:493)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.collectAliases(SqlImplementor.java:491)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.result(SqlImplementor.java:476)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:246)
{code}
Cause of failure seems to be that `RelToSqlConverter::visitAntiOrSemiJoin`
rewrites semi-join as select with no associated alias.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
