This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 24f0278e96 [CALCITE-7821] Correlated query crashes SqlToRelConverter
24f0278e96 is described below
commit 24f0278e969d1fca465d1a3cbed87ef09f9522fb
Author: Mihai Budiu <[email protected]>
AuthorDate: Thu Sep 24 11:50:26 2026 -0700
[CALCITE-7821] Correlated query crashes SqlToRelConverter
Signed-off-by: Mihai Budiu <[email protected]>
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 33 ++++++---
.../apache/calcite/test/SqlToRelConverterTest.java | 25 +++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 83 ++++++++++++++++++++++
core/src/test/resources/sql/sub-query.iq | 66 +++++++++++++++++
4 files changed, 197 insertions(+), 10 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 0223c778a6..ce6c00e295 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -30,6 +30,7 @@
import org.apache.calcite.plan.ViewExpanders;
import org.apache.calcite.prepare.Prepare;
import org.apache.calcite.prepare.RelOptTableImpl;
+import org.apache.calcite.rel.BiRel;
import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollationTraitDef;
import org.apache.calcite.rel.RelCollations;
@@ -47,7 +48,6 @@
import org.apache.calcite.rel.core.CorrelationId;
import org.apache.calcite.rel.core.Filter;
import org.apache.calcite.rel.core.Join;
-import org.apache.calcite.rel.core.JoinInfo;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.RelFactories;
@@ -1514,9 +1514,17 @@ private void substituteSubQuery(Blackboard bb, SubQuery
subQuery) {
AggregateCall.create(call.getParserPosition(),
SqlStdOperatorTable.COUNT, false,
false, false, ImmutableList.of(), args,
-1, null, RelCollations.EMPTY, longType, null)));
- LogicalJoin join =
- LogicalJoin.create(bb.root(), aggregate, ImmutableList.of(),
- rexBuilder.makeLiteral(true), ImmutableSet.of(),
JoinRelType.INNER);
+ // seek may reference the outer query; createJoin then returns
+ // a Correlate that binds the variable.
+ final RelNode join =
+ createJoin(bb, bb.root(), aggregate, rexBuilder.makeLiteral(true),
+ JoinRelType.INNER);
+ // As in Blackboard.register, keep the group by mapping for the new
root
+ final Map<Integer, Integer> projection =
+ bb.mapRootRelToFieldProjection.get(bb.root());
+ if (projection != null) {
+ bb.mapRootRelToFieldProjection.put(join, projection);
+ }
bb.setRoot(join, false);
}
final RexNode rex =
@@ -1811,9 +1819,10 @@ private RexNode translateIn(RelOptUtil.Logic logic,
@Nullable RelNode root,
// cross join (select count(*) as c, count(deptno) as ck from v) as ct
// left join (select distinct deptno, true as i from v) as dt
// on e.deptno = dt.deptno
- final Join join = (Join) requireNonNull(root, "root");
+ // Either join is a Correlate when the sub-query is correlated.
+ final BiRel join = (BiRel) requireNonNull(root, "root");
final Project left = (Project) join.getLeft();
- final RelNode leftLeft = ((Join) left.getInput()).getLeft();
+ final RelNode leftLeft = ((BiRel) left.getInput()).getLeft();
final int leftLeftCount = leftLeft.getRowType().getFieldCount();
final RelDataType longType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
@@ -1834,10 +1843,14 @@ private RexNode translateIn(RelOptUtil.Logic logic,
@Nullable RelNode root,
falseLiteral,
rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, iRef),
trueLiteral);
- final JoinInfo joinInfo = join.analyzeCondition();
- for (int leftKey : joinInfo.leftKeys) {
- final RexNode kRef = rexBuilder.makeInputRef(root, leftKey);
- args.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, kRef),
+ // Take the IN keys from rex rather than from the join condition,
+ // because a Correlate has no condition. rex has the layout described
+ // in TRUE_FALSE.
+ final int keyCount = (rex.getType().getFieldCount() - 1) / 2;
+ for (int i = 0; i < keyCount; i++) {
+ args.add(
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL,
+ rexBuilder.makeFieldAccess(rex, i)),
unknownLiteral);
}
args.add(rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, ckRef, cRef),
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 4b26c3f284..dbce3a21c0 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2728,6 +2728,31 @@ void checkCorrelatedMapSubQuery(boolean expand) {
sql(sql).ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7821";>[CALCITE-7821]
+ * Correlated query crashes SqlToRelConverter</a>. */
+ @Test void testNotInCorrelatedNullable() {
+ final String sql = "select * from emp as t where t.mgr not in"
+ + " (select s.mgr from emp as s where s.empno = t.empno)";
+ sql(sql).withExpand(true).ok();
+ }
+
+ /** As {@link #testNotInCorrelatedNullable()}, but without decorrelation. */
+ @Test void testNotInCorrelatedNullableNoDecorrelate() {
+ final String sql = "select * from emp as t where t.mgr not in"
+ + " (select s.mgr from emp as s where s.empno = t.empno)";
+ sql(sql).withExpand(true).withDecorrelate(false).ok();
+ }
+
+ /** As {@link #testNotInCorrelatedNullable()}, but in HAVING, where the
+ * outer query is an aggregate. */
+ @Test void testNotInCorrelatedNullableHaving() {
+ final String sql = "select deptno from emp as e group by deptno\n"
+ + "having deptno not in\n"
+ + " (select s.mgr from emp as s where s.deptno = e.deptno)";
+ sql(sql).withExpand(true).ok();
+ }
+
@Test void testAllValueList() {
final String sql = "select empno from emp where deptno > all (10, 20)";
sql(sql).withExpand(false).ok();
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 21db30b73e..71376384ef 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -6509,6 +6509,89 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalProject($f0=[true])
LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInCorrelatedNullable">
+ <Resource name="sql">
+ <![CDATA[select * from emp as t where t.mgr not in (select s.mgr from
emp as s where s.empno = t.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[OR(=($9, 0), AND(IS NULL($14), >=($10, $9), IS NOT
NULL($11)))])
+ LogicalJoin(condition=[AND(=($0, $13), =($11, $12))], joinType=[left])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$10],
$f1=[$11], MGR0=[$3])
+ LogicalJoin(condition=[=($0, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], $f1=[CASE(IS NOT NULL($2), $2, 0)],
$f2=[CASE(IS NOT NULL($3), $3, 0)])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], agg#0=[COUNT()], agg#1=[COUNT($1)])
+ LogicalProject(EMPNO=[$0], MGR=[$3], $f1=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[IS NOT NULL($0)])
+ LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+ LogicalProject(MGR=[$3], EMPNO=[$0], $f1=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInCorrelatedNullableHaving">
+ <Resource name="sql">
+ <![CDATA[select deptno from emp as e group by deptno
+having deptno not in
+ (select s.mgr from emp as s where s.deptno = e.deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[OR(=($1, 0), AND(IS NULL($6), >=($2, $1)))])
+ LogicalJoin(condition=[AND(=($0, $5), =($3, $4))], joinType=[left])
+ LogicalProject(DEPTNO=[$0], $f0=[$2], $f1=[$3], DEPTNO0=[$0])
+ LogicalJoin(condition=[=($0, $1)], joinType=[inner])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0], $f1=[CASE(IS NOT NULL($2), $2, 0)],
$f2=[CASE(IS NOT NULL($3), $3, 0)])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ LogicalAggregate(group=[{0}])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], agg#0=[COUNT()], agg#1=[COUNT($1)])
+ LogicalProject(DEPTNO=[$7], MGR=[$3], $f1=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[IS NOT NULL($0)])
+ LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+ LogicalProject(MGR=[$3], DEPTNO=[$7], $f1=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInCorrelatedNullableNoDecorrelate">
+ <Resource name="sql">
+ <![CDATA[select * from emp as t where t.mgr not in (select s.mgr from
emp as s where s.empno = t.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[NOT(CASE(=($9, 0), false, IS NOT NULL($13), true,
IS NULL($11), null:BOOLEAN, <($10, $9), null:BOOLEAN, false))])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0, 11}])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9],
$f1=[$10], MGR0=[$3])
+ LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ LogicalProject(MGR=[$3], $f1=[true])
+ LogicalFilter(condition=[=($0, $cor0.EMPNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[=($cor0.MGR0, $0)])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(MGR=[$3], $f1=[true])
+ LogicalFilter(condition=[=($0, $cor0.EMPNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 4a71d6a388..717697d29f 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -10231,3 +10231,69 @@ where e.n > (
!ok
!set trimfields true
+
+# [CALCITE-7821] Correlated query crashes SqlToRelConverter
+# The results of the following four queries were validated using Postgres.
+!set expand true
+with
+t(k, a) as (select * from (values (1, 10), (2, cast(null as int)), (3, 30),
(4, 40), (5, cast(null as int)))),
+s(k, b) as (select * from (values (1, 10), (3, cast(null as int)), (4, 41),
(5, 50)))
+select * from t where t.a not in (select s.b from s where s.k = t.k);
++---+----+
+| K | A |
++---+----+
+| 2 | |
+| 4 | 40 |
++---+----+
+(2 rows)
+
+!ok
+
+with
+t(k, a) as (select * from (values (1, 10), (2, cast(null as int)), (3, 30),
(4, 40), (5, cast(null as int)))),
+s(k, b) as (select * from (values (1, 10), (3, cast(null as int)), (4, 41),
(5, 50)))
+select k, a in (select s.b from s where s.k = t.k) as i from t;
++---+-------+
+| K | I |
++---+-------+
+| 1 | true |
+| 2 | false |
+| 3 | |
+| 4 | false |
+| 5 | |
++---+-------+
+(5 rows)
+
+!ok
+
+with
+t(k, a) as (select * from (values (1, 10), (2, cast(null as int)), (3, 30),
(4, 40), (5, cast(null as int)))),
+s(k, b) as (select * from (values (1, 10), (3, cast(null as int)), (4, 41),
(5, 50)))
+select k, a not in (select s.b from s where s.k = t.k) as ni from t;
++---+-------+
+| K | NI |
++---+-------+
+| 1 | false |
+| 2 | true |
+| 3 | |
+| 4 | true |
+| 5 | |
++---+-------+
+(5 rows)
+
+!ok
+
+with
+t(a, k) as (select * from (values (10, 1), (cast(null as int), 2), (30, 3),
(40, 4), (cast(null as int), 5))),
+s(k, b) as (select * from (values (1, 10), (3, cast(null as int)), (4, 41),
(5, 50)))
+select k from t group by k having max(a) not in (select s.b from s where s.k =
t.k);
++---+
+| K |
++---+
+| 2 |
+| 4 |
++---+
+(2 rows)
+
+!ok
+!set expand false
