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 f1c44784d2 Test case for [CALCITE-4792] SqlToRel should populate
corralateId for join with corralated query in ON condition
f1c44784d2 is described below
commit f1c44784d22363d88689e5d41f937a9ff1ba7363
Author: Yu Xu <[email protected]>
AuthorDate: Thu Aug 6 15:30:29 2026 +0800
Test case for [CALCITE-4792] SqlToRel should populate corralateId for join
with corralated query in ON condition
---
.../apache/calcite/test/SqlToRelConverterTest.java | 11 +++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 18 ++++++++++++++++++
core/src/test/resources/sql/sub-query.iq | 22 ++++++++++++++++++++++
3 files changed, 51 insertions(+)
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 a3cae00b94..90ed8a6333 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -377,6 +377,17 @@ public static void checkActualAndReferenceFiles() {
sql(sql).withExpand(false).ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4792";>[CALCITE-4792]
+ * SqlToRel should populate corralateId for join with corralated query in ON
condition</a>.
+ */
+ @Test void testJoinOnCorrelatedSubQuery() {
+ final String sql = "select * from emp inner join dept\n"
+ + "on emp.deptno = dept.deptno\n"
+ + "and exists (select 1 from emp e2 where e2.deptno = dept.deptno)";
+ sql(sql).withExpand(false).ok();
+ }
+
@Test void testJoinUsing() {
sql("SELECT * FROM emp JOIN dept USING (deptno)").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 ea4e81d044..e916c08131 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4345,6 +4345,24 @@ LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5
}, { 6 }, { 7 }, { 8 }, {
}))], joinType=[left])
LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])
LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinOnCorrelatedSubQuery">
+ <Resource name="sql">
+ <![CDATA[select * from emp inner join dept
+on emp.deptno = dept.deptno
+and exists (select 1 from emp e2 where e2.deptno = dept.deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10])
+ LogicalJoin(condition=[AND(=($7, $9), EXISTS({
+LogicalFilter(condition=[=($7, $cor0.DEPTNO0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], joinType=[inner], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index d941e1b8c3..e1cf406081 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -10110,5 +10110,27 @@ WHERE E1.SAL > (SELECT B1.COMM FROM BONUS B1 WHERE
E1.ENAME = B1.ENAME LIMIT 2);
+--------+
(0 rows)
+!ok
+
+# [CALCITE-4792] SqlToRel should populate corralateId for join with corralated
query in ON condition.
+SELECT emp.ename, dept.dname
+FROM emp INNER JOIN dept
+ON emp.deptno = dept.deptno
+AND EXISTS (SELECT 1 FROM emp e2 WHERE e2.deptno = dept.deptno AND e2.sal >
2900)
+ORDER BY emp.ename;
++--------+------------+
+| ENAME | DNAME |
++--------+------------+
+| ADAMS | RESEARCH |
+| CLARK | ACCOUNTING |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| KING | ACCOUNTING |
+| MILLER | ACCOUNTING |
+| SCOTT | RESEARCH |
+| SMITH | RESEARCH |
++--------+------------+
+(8 rows)
+
!ok
# End sub-query.iq
