[
https://issues.apache.org/jira/browse/CALCITE-6824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17926931#comment-17926931
]
Alessandro Solimando commented on CALCITE-6824:
-----------------------------------------------
After reviewing the code it's still not clear to me what you mean by "operands
are not empty".
The Jira title should not talk about Calcite internals but rather SQL concepts
like subquery, JOIN, etc. rather than "JOIN_SUB_QUERY_TO_CORRELATE".
You should see this from the users' perspective, they submit an SQL query and
they get an error due to this bug, the title of the ticket should caracterize
precisely what SQL "aspects" leads to this bug.
What you describe as "it is always assumed that the operands in RexSubQuery are
on the right side of the join" seems to be pointing to the right direction, in
fact, the query "SELECT empno FROM dept JOIN emp on emp.deptno not in (SELECT
deptno FROM dept)" works fine while "SELECT empno FROM emp JOIN dept on
emp.deptno not in (SELECT deptno FROM dept)" hits the error.
What about "Subquery in join conditions rewrite fails if referencing a column
from the right-hand side table"?
> 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)
