This is an automated email from the ASF dual-hosted git repository.
zhenchen 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 f89d8862e1 [CALCITE-7303] Subqueries cannot be decorrelated if filter
condition have multi CorrelationId
f89d8862e1 is described below
commit f89d8862e19a878a6af3d8fa0d2f4f4c9420c80a
Author: iwanttobepowerful <[email protected]>
AuthorDate: Fri Nov 28 18:40:13 2025 +0800
[CALCITE-7303] Subqueries cannot be decorrelated if filter condition have
multi CorrelationId
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 14 +++-
.../calcite/sql2rel/RelDecorrelatorTest.java | 84 ++++++++++++++++++++++
core/src/test/resources/sql/sub-query.iq | 62 ++++++++++++++++
3 files changed, 157 insertions(+), 3 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index e9d7f498f9..e45edff7e5 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -1495,17 +1495,25 @@ private Frame
decorrelateInputWithValueGenerator(RelNode rel, Frame frame) {
}
/** Finds a {@link RexInputRef} that is equivalent to a {@link CorRef},
- * and if found, throws a {@link org.apache.calcite.util.Util.FoundOne}. */
+ * and if found, throws a {@link org.apache.calcite.util.Util.FoundOne}.
+ *
+ * <p>The equivalent expression must not contain any {@link RexFieldAccess},
+ * ensuring that we only map the correlation variable to a local field or
+ * expression from the current relational expression (e.g., a {@link
RexInputRef}),
+ * rather than to another correlation variable.
+ */
private static void findCorrelationEquivalent(CorRef correlation, RexNode e)
throws Util.FoundOne {
switch (e.getKind()) {
case EQUALS:
final RexCall call = (RexCall) e;
final List<RexNode> operands = call.getOperands();
- if (references(operands.get(0), correlation)) {
+ if (!RexUtil.containsFieldAccess(operands.get(1))
+ && references(operands.get(0), correlation)) {
throw new Util.FoundOne(operands.get(1));
}
- if (references(operands.get(1), correlation)) {
+ if (!RexUtil.containsFieldAccess(operands.get(0))
+ && references(operands.get(1), correlation)) {
throw new Util.FoundOne(operands.get(0));
}
break;
diff --git
a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
index c079af7740..f6504fdef9 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -272,6 +272,90 @@ public static Frameworks.ConfigBuilder config() {
assertThat(after, hasTree(planAfter));
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7303";>[CALCITE-7303]
+ * Subqueries cannot be decorrelated if filter condition have multi
CorrelationId</a>. */
+ @Test void testCorrelationEquivalent() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = "SELECT deptno\n"
+ + "FROM emp e\n"
+ + "WHERE EXISTS (\n"
+ + " SELECT *\n"
+ + " FROM dept d\n"
+ + " WHERE EXISTS(\n"
+ + " SELECT *\n"
+ + " FROM bonus b\n"
+ + " WHERE b.ename = e.ename\n"
+ + " AND b.job = d.dname\n"
+ + " AND d.deptno = e.deptno))";
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalProject(DEPTNO=[$7])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{1, 7}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(i=[true])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0, 1}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(i=[true])\n"
+ + " LogicalFilter(condition=[AND(=($0, $cor0.ENAME),
=(CAST($1):VARCHAR(14), $cor1.DNAME), =($cor1.DEPTNO, $cor0.DEPTNO))])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ // Verify plan
+ final String planAfter = ""
+ + "LogicalProject(DEPTNO=[$7])\n"
+ + " LogicalJoin(condition=[AND(=($1, $8), =($7, $9))],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(ENAME0=[$0], DEPTNO0=[$1], $f2=[true])\n"
+ + " LogicalAggregate(group=[{0, 1}])\n"
+ + " LogicalProject(ENAME0=[$3], DEPTNO0=[$4])\n"
+ + " LogicalJoin(condition=[AND(=($0, $5), =($1, $6))],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(ENAME0=[$0], DEPTNO=[$1], DEPTNO0=[$2],
DNAME=[$3], $f4=[true])\n"
+ + " LogicalAggregate(group=[{0, 1, 2, 3}])\n"
+ + " LogicalProject(ENAME0=[$4], DEPTNO=[$5],
DEPTNO0=[$6], DNAME=[$7])\n"
+ + " LogicalJoin(condition=[AND(=($0, $4),
=(CAST($1):VARCHAR(14), $7))], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + " LogicalJoin(condition=[=($2, $1)],
joinType=[inner])\n"
+ + " LogicalProject(ENAME=[$1], DEPTNO=[$7])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
/**
* Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6468";>[CALCITE-6468]
RelDecorrelator
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index ab9a52fa79..2fa71122fb 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -5443,4 +5443,66 @@ ORDER BY deptno;
!ok
+# [CALCITE-7303] Subqueries cannot be decorrelated if filter condition have
multi CorrelationId
+SELECT deptno
+FROM emp e
+WHERE EXISTS
+ (SELECT *
+ FROM dept d
+ WHERE EXISTS
+ (SELECT *
+ FROM bonus b
+ WHERE b.ename = e.ename
+ AND d.deptno = e.deptno));
++--------+
+| DEPTNO |
++--------+
++--------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7303] Subqueries cannot be decorrelated if filter condition have
multi CorrelationId
+SELECT deptno
+FROM emp e
+WHERE EXISTS (
+ SELECT *
+ FROM dept d
+ WHERE EXISTS(
+ SELECT *
+ FROM bonus b
+ WHERE b.ename = e.ename
+ AND b.job = d.dname
+ AND d.deptno = e.deptno));
++--------+
+| DEPTNO |
++--------+
++--------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7303] Subqueries cannot be decorrelated if filter condition have
multi CorrelationId
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (1, 2), (2, 1), (2, 2), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a
+FROM t0 e
+WHERE EXISTS
+ (SELECT *
+ FROM t1 d
+ WHERE EXISTS
+ (SELECT *
+ FROM t2 b
+ WHERE b.t2b = e.t0b
+ AND d.t1a = e.t0a));
++-----+
+| T0A |
++-----+
+| 1 |
+| 1 |
++-----+
+(2 rows)
+
+!ok
# End sub-query.iq
