This is an automated email from the ASF dual-hosted git repository.
xuzifu666 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 5dcfb76e4a Test cases for [CALCITE-5413] Nested Subqueries with
correlated variables are not decorrelated correctly
5dcfb76e4a is described below
commit 5dcfb76e4a30239908200ad4d726176236ec1069
Author: Yu Xu <[email protected]>
AuthorDate: Thu Aug 6 16:22:24 2026 +0800
Test cases for [CALCITE-5413] Nested Subqueries with correlated variables
are not decorrelated correctly
---
.../org/apache/calcite/test/RelOptRulesTest.java | 18 +++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 44 ++++++++++++++++++++++
core/src/test/resources/sql/new-decorr.iq | 35 +++++++++++++++++
3 files changed, 97 insertions(+)
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index aafd9429d2..0cfb76b9d5 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -9530,6 +9530,24 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule
rule) {
sql(sql).withSubQueryRules().check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5413";>[CALCITE-5413]
+ * Nested Subqueries with correlated variables are not decorrelated
correctly</a>.
+ */
+ @Test void testExpandFilterNestedExistsCorrelatingTwoLevels() {
+ 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 emp_address ea\n"
+ + " WHERE d.deptno = e.deptno\n"
+ + " AND ea.empno = e.empno))";
+ sql(sql).withSubQueryRules().check();
+ }
+
@Test void testDecorrelateExists() {
final String sql = "select * from sales.emp\n"
+ "where EXISTS (\n"
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index ba303c062b..0b544e3f1c 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -5220,6 +5220,50 @@ LogicalProject(EMPNO=[$0])
LogicalProject(DEPTNO=[$0], i=[true])
LogicalFilter(condition=[=($1, 'dept2')])
LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpandFilterNestedExistsCorrelatingTwoLevels">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno
+FROM emp e
+WHERE EXISTS (
+ SELECT *
+ FROM dept d
+ WHERE EXISTS(
+ SELECT *
+ FROM emp_address ea
+ WHERE d.deptno = e.deptno
+ AND ea.empno = e.empno))]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[AND(=($cor0.DEPTNO, $cor1.DEPTNO), =($0,
$cor1.EMPNO))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
+})], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})], variablesSet=[[$cor1]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0, 7}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(i=[true])
+ LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[AND(=($cor0.DEPTNO, $cor1.DEPTNO),
=($0, $cor1.EMPNO))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/new-decorr.iq
b/core/src/test/resources/sql/new-decorr.iq
index 3aef6e5276..a1caa699d6 100644
--- a/core/src/test/resources/sql/new-decorr.iq
+++ b/core/src/test/resources/sql/new-decorr.iq
@@ -496,4 +496,39 @@ SELECT empno, (SELECT row_number() OVER (PARTITION BY
dname ORDER BY emp.sal) FR
!ok
!}
+
+# [CALCITE-5413] Nested Subqueries with correlated variables are not
decorrelated correctly
+# The innermost subquery correlates to both the outermost (e) and the middle
(d) query levels.
+SELECT e.empno
+FROM emp e
+WHERE EXISTS (
+ SELECT *
+ FROM dept d
+ WHERE EXISTS (
+ SELECT *
+ FROM emp ea
+ WHERE d.deptno = e.deptno
+ AND ea.empno = e.empno))
+ORDER BY e.empno;
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
++-------+
+(14 rows)
+
+!ok
# End new-decorr.iq
