[
https://issues.apache.org/jira/browse/CALCITE-6824?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alessandro Solimando resolved CALCITE-6824.
-------------------------------------------
Resolution: Fixed
Fixed via
[{{130c8dc}}|https://github.com/apache/calcite/commit/130c8dcacd2a2e8e44aa37c3832db063af66dd0f],
thanks [~suibianwanwan33] for your contribution!
> Subquery in join conditions rewrite fails if referencing a column from the
> right-hand side table
> ------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6824
> URL: https://issues.apache.org/jira/browse/CALCITE-6824
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.38.0
> Reporter: Julian Hyde
> Assignee: suibianwanwan
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.39.0
>
>
> 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)
