[
https://issues.apache.org/jira/browse/CALCITE-6824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17926906#comment-17926906
]
Alessandro Solimando edited comment on CALCITE-6824 at 2/13/25 7:04 PM:
------------------------------------------------------------------------
[~suibianwanwan33] I think it's not evident why the minimal repro from Julian
can be further reduced to your minimal repro.
This is clear as long as you know how it will be translated to a query
involving a join, which then starts to resemble your minimal repro, but it gets
some work to figure that out.
Let me provide some further explanation for future readers of this issue.
We start from this plan:
{noformat}
2025-02-13 19:53:30,423 [ForkJoinPool-1-worker-1] DEBUG - call#0: Apply rule
[SubQueryRemoveRule:Filter] to [rel#16:LogicalFilter]
2025-02-13 19:53:30,575 [ForkJoinPool-1-worker-1] DEBUG - call#0: Full plan for
rule input [rel#16:LogicalFilter]:
LogicalFilter(condition=[IN(NOT(IN($0, {
LogicalProject(DEPTNO=[CAST($0):SMALLINT NOT NULL])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
})), {
LogicalProject(EXPR$0=[=(CAST($0):INTEGER NOT NULL, 0)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
})])
LogicalProject(EMPNO=[$0])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
{noformat}
and "SubQueryRemoveRule:Filter" transforms it into:
{noformat}
2025-02-13 19:53:30,576 [ForkJoinPool-1-worker-1] DEBUG - call#0: Rule
[SubQueryRemoveRule:Filter] produced [rel#20:LogicalProject]
2025-02-13 19:53:30,578 [ForkJoinPool-1-worker-1] DEBUG - call#0: Full plan for
[rel#20:LogicalProject]:
LogicalProject(EMPNO=[$0])
LogicalJoin(condition=[=(NOT(IN($0, {
LogicalProject(DEPTNO=[CAST($0):SMALLINT NOT NULL])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
})), $1)], joinType=[inner])
LogicalProject(EMPNO=[$0])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(EXPR$0=[=(CAST($0):INTEGER NOT NULL, 0)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]]){noformat}
which involves a join with correlation like in your reproducer, and finally
"SubQueryRemoveRule:Join" produces:
{noformat}
2025-02-13 19:53:30,641 [ForkJoinPool-1-worker-1] DEBUG - call#1: Rule
[SubQueryRemoveRule:Join] produced [rel#33:LogicalProject]
2025-02-13 19:53:30,641 [ForkJoinPool-1-worker-1] DEBUG - call#1: Full plan for
[rel#33:LogicalProject]:
LogicalProject(EMPNO=[$0], EXPR$0=[$3])
LogicalJoin(condition=[=(IS NULL($2), $3)], joinType=[inner])
LogicalJoin(condition=[=($0, $1)], joinType=[left])
LogicalProject(EMPNO=[$0])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
LogicalAggregate(group=[{0}], i=[LITERAL_AGG(true)])
LogicalProject(DEPTNO=[CAST($0):SMALLINT NOT NULL])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
LogicalAggregate(group=[{0}])
LogicalProject(EXPR$0=[=(CAST($0):INTEGER NOT NULL, 0)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]]){noformat}
"SubQueryRemoveRule:Join" is the place where you finally tracked down the
underlying issue if I got it right.
was (Author: asolimando):
[~suibianwanwan33] I think it's not evident why the minimal repro from Julian
can be further reduced to your minimal repro.
This is clear as long as you know how it will be translated to a query
involving a join, which then starts to resemble your minimal repro, but it gets
some work to figure that out.
Let me provide some further explanation for future readers of this issue.
We start from this plan:
{noformat}
2025-02-13 19:53:30,423 [ForkJoinPool-1-worker-1] DEBUG - call#0: Apply rule
[SubQueryRemoveRule:Filter] to [rel#16:LogicalFilter]
2025-02-13 19:53:30,575 [ForkJoinPool-1-worker-1] DEBUG - call#0: Full plan for
rule input [rel#16:LogicalFilter]:
LogicalFilter(condition=[IN(NOT(IN($0, {
LogicalProject(DEPTNO=[CAST($0):SMALLINT NOT NULL])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
})), {
LogicalProject(EXPR$0=[=(CAST($0):INTEGER NOT NULL, 0)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
})])
LogicalProject(EMPNO=[$0])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
{noformat}
and "SubQueryRemoveRule:Filter" transforms it into:
{noformat}
2025-02-13 19:53:30,576 [ForkJoinPool-1-worker-1] DEBUG - call#0: Rule
[SubQueryRemoveRule:Filter] produced [rel#20:LogicalProject]
2025-02-13 19:53:30,578 [ForkJoinPool-1-worker-1] DEBUG - call#0: Full plan for
[rel#20:LogicalProject]:
LogicalProject(EMPNO=[$0])
LogicalJoin(condition=[=(NOT(IN($0, {
LogicalProject(DEPTNO=[CAST($0):SMALLINT NOT NULL])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
})), $1)], joinType=[inner])
LogicalProject(EMPNO=[$0])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(EXPR$0=[=(CAST($0):INTEGER NOT NULL, 0)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]]){noformat}
which involves a join with correlation like in your reproducer, and finally
"SubQueryRemoveRule:Join" produces:
{noformat}
2025-02-13 19:53:30,641 [ForkJoinPool-1-worker-1] DEBUG - call#1: Rule
[SubQueryRemoveRule:Join] produced [rel#33:LogicalProject]
2025-02-13 19:53:30,641 [ForkJoinPool-1-worker-1] DEBUG - call#1: Full plan for
[rel#33:LogicalProject]:
LogicalProject(EMPNO=[$0], EXPR$0=[$3])
LogicalJoin(condition=[=(IS NULL($2), $3)], joinType=[inner])
LogicalJoin(condition=[=($0, $1)], joinType=[left])
LogicalProject(EMPNO=[$0])
JdbcTableScan(table=[[JDBC_SCOTT, EMP]])
LogicalAggregate(group=[{0}], i=[LITERAL_AGG(true)])
LogicalProject(DEPTNO=[CAST($0):SMALLINT NOT NULL])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]])
LogicalAggregate(group=[{0}])
LogicalProject(EXPR$0=[=(CAST($0):INTEGER NOT NULL, 0)])
JdbcTableScan(table=[[JDBC_SCOTT, DEPT]]){noformat}
"SubQueryRemoveRule:Join" is the place where you finally tracked down the
underlying issue if I got it right.
> JOIN_SUB_QUERY_TO_CORRELATE rule produces an incorrect plan when operands are
> not empty
> ---------------------------------------------------------------------------------------
>
> Key: CALCITE-6824
> URL: https://issues.apache.org/jira/browse/CALCITE-6824
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: suibianwanwan
> Priority: Major
> Labels: pull-request-available
>
> Consider a query that asks whether a BOOLEAN value generated by one subquery
> is in the result set produced by another subquery. This is valid unless you
> think subqueries should not be allowed to return BOOLEAN values.
> {noformat}
> SELECT empno
> FROM emp
> WHERE (empno NOT IN (SELECT deptno FROM dept))
> IN (SELECT deptno = 0 FROM dept)
> {noformat}
> During planning an AssertionError occurs. I believe this occurs just after
> {{Programs.TrimFieldsProgram}} has been invoked.
> {noformat}
> java.lang.AssertionError: type mismatch:
> ref:
> SMALLINT NOT NULL
> input:
> BOOLEAN NOT NULL
> at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31)
> at
> org.apache.calcite.plan.RelOptUtil.eqUpToNullability(RelOptUtil.java:2261)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:131)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:62)
> at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:125)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:148)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:62)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:208)
> at org.apache.calcite.rel.core.Join.isValid(Join.java:178)
> at
> org.apache.calcite.rel.AbstractRelNode.onRegister(AbstractRelNode.java:287)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.registerImpl(VolcanoPlanner.java:1289)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:600)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:615)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:97)
> at
> org.apache.calcite.rel.AbstractRelNode.onRegister(AbstractRelNode.java:274)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.registerImpl(VolcanoPlanner.java:1289)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:600)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:615)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.ensureRegistered(VolcanoPlanner.java:97)
> at
> org.apache.calcite.rel.AbstractRelNode.onRegister(AbstractRelNode.java:274)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.registerImpl(VolcanoPlanner.java:1289)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.setRoot(VolcanoPlanner.java:276)
> at
> org.apache.calcite.tools.Programs.lambda$standard$4(Programs.java:298)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:373)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:178)
> {noformat}
> The following patch to JdbcTest reproduces; also
> [RelToSqlConverterTest.testMissingParenthesesWithSubquery3|https://github.com/apache/calcite/blob/f1c370a0cb57675b6e5a442b3d98e29d75a64043/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1544]:
> {noformat}
> diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
> b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
> index 053d7595e..975e67f88 100644
> --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
> +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
> @@ -6955,6 +6955,17 @@ private void checkGetTimestamp(Connection con) throws
> SQLException {
> .returnsUnordered("EMPNO=7876", "EMPNO=7499", "EMPNO=7698");
> }
>
> + @Test void testFoo() {
> + final String sql = "SELECT empno\n"
> + + "FROM emp\n"
> + + "WHERE (empno NOT IN (SELECT deptno FROM dept))\n"
> + + " IN (SELECT deptno = 0 FROM dept)";
> + CalciteAssert.that()
> + .with(CalciteAssert.Config.JDBC_SCOTT)
> + .query(sql)
> + .returnsUnordered("EMPNO=7876", "EMPNO=7499", "EMPNO=7698");
> + }
> +
> @Test void testTimestampEqualsComparison() {
> CalciteAssert.that()
> .query("select time0 = time1, time0 <> time1"
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
