Julian Hyde created CALCITE-6824:
------------------------------------
Summary: FieldTrimmer corrupts plan if query compares two boolean
subqueries
Key: CALCITE-6824
URL: https://issues.apache.org/jira/browse/CALCITE-6824
Project: Calcite
Issue Type: Bug
Reporter: Julian Hyde
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)
