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 17493dde63 Test cases for [CALCITE-5418] Nested Queries are not
expanded properly
17493dde63 is described below
commit 17493dde63f2920d264c3829fccbe1f0961e63de
Author: Yu Xu <[email protected]>
AuthorDate: Tue Aug 4 21:23:44 2026 +0800
Test cases for [CALCITE-5418] Nested Queries are not expanded properly
---
.../org/apache/calcite/test/RelOptRulesTest.java | 29 +++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 57 ++++++++++++++++++++++
core/src/test/resources/sql/sub-query.iq | 37 ++++++++++++++
3 files changed, 123 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 5c687519da..bad13247c3 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -9459,6 +9459,35 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule
rule) {
sql(sql).withSubQueryRules().check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5716";>[CALCITE-5716]
+ * Two level nested correlated subquery translates to incorrect ON
+ * condition</a>.
+ *
+ * <p>A middle-level EXISTS holds two sibling inner EXISTS sub-queries that
+ * correlate to different levels: {@code ea.empno = e.empno} references the
+ * outer {@code emp} ($cor0), while {@code e2.deptno = d.deptno} references
+ * the middle {@code dept} ($cor2). The rewrite must keep those correlation
+ * variables at their originating levels rather than collapsing the inner
+ * Correlate onto the outer variable. */
+ @Test void testExpandFilterNestedExistsWithTwoSiblingInnerExists() {
+ final String sql = "SELECT deptno\n"
+ + "FROM emp e\n"
+ + "WHERE EXISTS (\n"
+ + " SELECT *\n"
+ + " FROM dept d\n"
+ + " WHERE d.deptno = e.deptno\n"
+ + " AND EXISTS (\n"
+ + " SELECT *\n"
+ + " FROM emp_address ea\n"
+ + " WHERE ea.empno = e.empno)\n"
+ + " AND EXISTS (\n"
+ + " SELECT *\n"
+ + " FROM emp e2\n"
+ + " WHERE e2.deptno = d.deptno))";
+ 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 32f423c3e1..c623e63038 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,63 @@ LogicalProject(EMPNO=[$0])
LogicalProject(DEPTNO=[$0], i=[true])
LogicalFilter(condition=[=($1, 'dept2')])
LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpandFilterNestedExistsWithTwoSiblingInnerExists">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno
+FROM emp e
+WHERE EXISTS (
+ SELECT *
+ FROM dept d
+ WHERE d.deptno = e.deptno
+ AND EXISTS (
+ SELECT *
+ FROM emp_address ea
+ WHERE ea.empno = e.empno)
+ AND EXISTS (
+ SELECT *
+ FROM emp e2
+ WHERE e2.deptno = d.deptno))]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[AND(=($0, $cor0.DEPTNO), EXISTS({
+LogicalFilter(condition=[=($0, $cor0.EMPNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
+}), EXISTS({
+LogicalFilter(condition=[=($7, $cor2.DEPTNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], variablesSet=[[$cor2]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})], variablesSet=[[$cor0]])
+ 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=[$cor0], joinType=[inner],
requiredColumns=[{0, 7}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(i=[true])
+ LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
+ LogicalCorrelate(correlation=[$cor2], joinType=[inner],
requiredColumns=[{0}])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[=($0, $cor0.EMPNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[=($7, $cor2.DEPTNO)])
+ 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 847cdb98d9..d941e1b8c3 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -4904,6 +4904,43 @@ FROM dept;
!ok
+# [CALCITE-5716] Two level nested correlated subquery translates to incorrect
ON condition.
+# A middle-level EXISTS holds two sibling inner EXISTS that correlate to
+# different levels: ea.mgr = e.mgr references the outer emp, while
+# e2.deptno = d.deptno references the middle dept. Each correlation must stay
+# at its originating level.
+SELECT e.empno
+FROM emp e
+WHERE EXISTS (
+ SELECT 1
+ FROM dept d
+ WHERE d.deptno = e.deptno
+ AND EXISTS (
+ SELECT 1 FROM emp ea WHERE ea.mgr = e.mgr)
+ AND EXISTS (
+ SELECT 1 FROM emp e2 WHERE e2.deptno = d.deptno))
+ORDER BY e.empno;
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
++-------+
+(13 rows)
+
+!ok
+
# [CALCITE-7034] IllegalArgumentException when correlate subQuery in on clause
and use rightside columns
SELECT e1.*
FROM emp e1
