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 eb0534dac4 [CALCITE-6176] JOIN_SUB_QUERY_TO_CORRELATE rule incorrectly
handles EXISTS in LEFT JOIN ON clause
eb0534dac4 is described below
commit eb0534dac4949fe4116f0fbb0c861edf8912b766
Author: Zhen Chen <[email protected]>
AuthorDate: Mon Nov 17 23:07:20 2025 +0800
[CALCITE-6176] JOIN_SUB_QUERY_TO_CORRELATE rule incorrectly handles EXISTS
in LEFT JOIN ON clause
---
.../org/apache/calcite/test/RelOptRulesTest.java | 60 +++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 136 +++++++++++++++++++++
core/src/test/resources/sql/join.iq | 62 ++++++++++
3 files changed, 258 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 6b2d1533c6..92e27614e5 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -10038,6 +10038,66 @@ public interface Config extends RelRule.Config {
.checkUnchanged();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6176";>[CALCITE-6176]
+ * JOIN_SUB_QUERY_TO_CORRELATE rule incorrectly handles EXISTS in LEFT JOIN
ON clause</a>. */
+ @Test void testJoinSubQueryRemoveRuleWithNotExists() {
+ final String sql = "select *\n"
+ + "from (select 1 id) t1\n"
+ + "left join (select 2 id) t2\n"
+ + "on not exists(select *\n"
+ + " from (select 3 id) p\n"
+ + " where p.id = t2.id)";
+ sql(sql)
+ .withRule(CoreRules.JOIN_SUB_QUERY_TO_CORRELATE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6176";>[CALCITE-6176]
+ * JOIN_SUB_QUERY_TO_CORRELATE rule incorrectly handles EXISTS in LEFT JOIN
ON clause</a>. */
+ @Test void testJoinSubQueryRemoveRuleWithOrExists() {
+ final String sql = "select *\n"
+ + "from (select 1 id) t1\n"
+ + "left join (select 2 id) t2\n"
+ + "on t1.id = t2.id or exists(select *\n"
+ + " from (select 3 id) p\n"
+ + " where p.id = t2.id)";
+ sql(sql)
+ .withRule(CoreRules.JOIN_SUB_QUERY_TO_CORRELATE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6176";>[CALCITE-6176]
+ * JOIN_SUB_QUERY_TO_CORRELATE rule incorrectly handles EXISTS in LEFT JOIN
ON clause</a>. */
+ @Test void testJoinSubQueryRemoveRuleWithOrNotExists() {
+ final String sql = "select *\n"
+ + "from (select 1 id) t1\n"
+ + "left join (select 2 id) t2\n"
+ + "on t1.id = t2.id or not exists(select *\n"
+ + " from (select 3 id) p\n"
+ + " where p.id = t2.id)";
+ sql(sql)
+ .withRule(CoreRules.JOIN_SUB_QUERY_TO_CORRELATE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6176";>[CALCITE-6176]
+ * JOIN_SUB_QUERY_TO_CORRELATE rule incorrectly handles EXISTS in LEFT JOIN
ON clause</a>. */
+ @Test void testJoinSubQueryRemoveRuleWithAndNotExists() {
+ final String sql = "select *\n"
+ + "from (select 1 id union all select 2) t1\n"
+ + "left join (select 2 id) t2\n"
+ + "on t1.id = t2.id and not exists(select *\n"
+ + " from (select 3 id) p\n"
+ + " where p.id = t1.id)";
+ sql(sql)
+ .withRule(CoreRules.JOIN_SUB_QUERY_TO_CORRELATE)
+ .check();
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-2295";>[CALCITE-2295]
* Correlated SubQuery with Project will generate error plan</a>. */
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 651b6ad520..0fe14272f3 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -8293,6 +8293,74 @@ LogicalProject(SAL=[$5])
LogicalProject(SAL=[$5], $f9=[=($5, 4)])
LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinSubQueryRemoveRuleWithAndNotExists">
+ <Resource name="sql">
+ <![CDATA[select *
+from (select 1 id union all select 2) t1
+left join (select 2 id) t2
+on t1.id = t2.id and not exists(select *
+ from (select 3 id) p
+ where p.id = t1.id)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ID=[$0], ID0=[$1])
+ LogicalJoin(condition=[AND(=($0, $1), NOT(EXISTS({
+LogicalFilter(condition=[=($0, $cor0.ID)])
+ LogicalValues(tuples=[[{ 3 }]])
+})))], joinType=[left])
+ LogicalValues(tuples=[[{ 1 }, { 2 }]])
+ LogicalValues(tuples=[[{ 2 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(ID=[$0], ID0=[$1])
+ LogicalProject(ID=[$0], ID0=[$2])
+ LogicalJoin(condition=[AND(=($0, $2), IS NULL($1))], joinType=[left])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
+ LogicalValues(tuples=[[{ 1 }, { 2 }]])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[=($0, $cor0.ID)])
+ LogicalValues(tuples=[[{ 3 }]])
+ LogicalValues(tuples=[[{ 2 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinSubQueryRemoveRuleWithNotExists">
+ <Resource name="sql">
+ <![CDATA[select *
+from (select 1 id) t1
+left join (select 2 id) t2
+on not exists(select *
+ from (select 3 id) p
+ where p.id = t2.id)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ID=[$0], ID0=[$1])
+ LogicalJoin(condition=[NOT(EXISTS({
+LogicalFilter(condition=[=($0, $cor0.ID0)])
+ LogicalValues(tuples=[[{ 3 }]])
+}))], joinType=[left])
+ LogicalValues(tuples=[[{ 1 }]])
+ LogicalValues(tuples=[[{ 2 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(ID=[$0], ID0=[$1])
+ LogicalProject(ID=[$0], ID0=[$1])
+ LogicalJoin(condition=[IS NULL($2)], joinType=[left])
+ LogicalValues(tuples=[[{ 1 }]])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[=($0, $cor0.ID)])
+ LogicalValues(tuples=[[{ 3 }]])
]]>
</Resource>
</TestCase>
@@ -8325,6 +8393,74 @@ LogicalProject(EMPNO=[$0])
LogicalProject(DEPTNO=[$0], i=[true])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinSubQueryRemoveRuleWithOrExists">
+ <Resource name="sql">
+ <![CDATA[select *
+from (select 1 id) t1
+left join (select 2 id) t2
+on t1.id = t2.id or exists(select *
+ from (select 3 id) p
+ where p.id = t2.id)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ID=[$0], ID0=[$1])
+ LogicalJoin(condition=[OR(=($0, $1), EXISTS({
+LogicalFilter(condition=[=($0, $cor0.ID0)])
+ LogicalValues(tuples=[[{ 3 }]])
+}))], joinType=[left])
+ LogicalValues(tuples=[[{ 1 }]])
+ LogicalValues(tuples=[[{ 2 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(ID=[$0], ID0=[$1])
+ LogicalProject(ID=[$0], ID0=[$1])
+ LogicalJoin(condition=[OR(=($0, $1), IS NOT NULL($2))], joinType=[left])
+ LogicalValues(tuples=[[{ 1 }]])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[=($0, $cor0.ID)])
+ LogicalValues(tuples=[[{ 3 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinSubQueryRemoveRuleWithOrNotExists">
+ <Resource name="sql">
+ <![CDATA[select *
+from (select 1 id) t1
+left join (select 2 id) t2
+on t1.id = t2.id or not exists(select *
+ from (select 3 id) p
+ where p.id = t2.id)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ID=[$0], ID0=[$1])
+ LogicalJoin(condition=[OR(=($0, $1), NOT(EXISTS({
+LogicalFilter(condition=[=($0, $cor0.ID0)])
+ LogicalValues(tuples=[[{ 3 }]])
+})))], joinType=[left])
+ LogicalValues(tuples=[[{ 1 }]])
+ LogicalValues(tuples=[[{ 2 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(ID=[$0], ID0=[$1])
+ LogicalProject(ID=[$0], ID0=[$1])
+ LogicalJoin(condition=[OR(=($0, $1), IS NULL($2))], joinType=[left])
+ LogicalValues(tuples=[[{ 1 }]])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalProject(i=[true])
+ LogicalFilter(condition=[=($0, $cor0.ID)])
+ LogicalValues(tuples=[[{ 3 }]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/join.iq
b/core/src/test/resources/sql/join.iq
index 1e1f5135e2..0c7573baee 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -1030,4 +1030,66 @@ natural join "scott".dept order by empno;
!ok
+# [CALCITE-6176] JOIN_SUB_QUERY_TO_CORRELATE rule incorrectly handles EXISTS
in LEFT JOIN ON clause
+select *
+from (select 1 id) t1
+left join (select 2 id) t2
+on not exists(select *
+ from (select 3 id) p
+ where p.id = t2.id);
++----+-----+
+| ID | ID0 |
++----+-----+
+| 1 | 2 |
++----+-----+
+(1 row)
+
+!ok
+
+select *
+from (select 1 id union all select 2) t1
+left join (select 2 id) t2
+on t1.id = t2.id and not exists(select *
+ from (select 3 id) p
+ where p.id = t1.id);
++----+-----+
+| ID | ID0 |
++----+-----+
+| 1 | |
+| 2 | 2 |
++----+-----+
+(2 rows)
+
+!ok
+
+select *
+from (select 1 id) t1
+left join (select 2 id) t2
+on t1.id = t2.id or exists(select *
+ from (select 3 id) p
+ where p.id = t2.id);
++----+-----+
+| ID | ID0 |
++----+-----+
+| 1 | |
++----+-----+
+(1 row)
+
+!ok
+
+select *
+from (select 1 id) t1
+left join (select 2 id) t2
+on t1.id = t2.id or not exists(select *
+ from (select 3 id) p
+ where p.id = t2.id);
++----+-----+
+| ID | ID0 |
++----+-----+
+| 1 | 2 |
++----+-----+
+(1 row)
+
+!ok
+
# End join.iq
