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 a096c8fd32 [CALCITE-6963] SqlToRelConverter fails when subquery is in
join on clause
a096c8fd32 is described below
commit a096c8fd32213cfd365eadffd8162e472e3aa00e
Author: Zhen Chen <[email protected]>
AuthorDate: Fri Nov 21 16:27:50 2025 +0800
[CALCITE-6963] SqlToRelConverter fails when subquery is in join on clause
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 3 +-
.../apache/calcite/test/SqlToRelConverterTest.java | 10 +++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 45 ++++++++++++++++------
3 files changed, 46 insertions(+), 12 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 392fcdda10..da96b98b06 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -3453,7 +3453,8 @@ private Pair<RexNode, RelNode> convertOnCondition(
SqlNode condition,
RelNode leftRel,
RelNode rightRel) {
- bb.setRoot(ImmutableList.of(leftRel, rightRel));
+ bb.setRoot(ImmutableList.of(leftRel, rightRel), leftRel,
+ leftRel instanceof LogicalJoin);
replaceSubQueries(bb, condition, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
final RelNode newRightRel =
bb.root == null || bb.registered.isEmpty()
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 b332be680e..b62b9c247f 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -5976,4 +5976,14 @@ void checkUserDefinedOrderByOver(NullCollation
nullCollation) {
+ " where e1.deptno = e2.deptno)";
sql(sql).withExpand(false).ok();
}
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6963";>[CALCITE-6963]
+ * SqlToRelConverter fails when subquery is in join on clause</a>. */
+ @Test void testSubqueryInJoinOnClause() {
+ final String sql = "select t1.* from emp t1\n"
+ + "left join dept t2 on t1.deptno = t2.deptno\n"
+ + "and t1.ename in (select t3.ename from emp t3 )";
+ sql(sql).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 382e39292f..a1c7f101b3 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -3751,14 +3751,15 @@ from (values (cast(null as int), 1),
<Resource name="planExpanded">
<![CDATA[
LogicalProject(DEPTNO=[$9], SAL=[$7])
- LogicalJoin(condition=[AND(=($9, $0), <($7, $0))], joinType=[inner])
+ LogicalJoin(condition=[=($9, $0)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EXPR$0=[$10])
- LogicalJoin(condition=[=($0, $9)], joinType=[left])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
- LogicalProject(DEPTNO=[$7], SAL=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<($5, $0)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EXPR$0=[$10])
+ LogicalJoin(condition=[=($0, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planNotExpanded">
@@ -3953,11 +3954,13 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
<Resource name="plan">
<![CDATA[
LogicalProject(X=[$0])
- LogicalJoin(condition=[AND(=($0, $2), IN($0, {
-LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }, { 7 }, { 8 },
{ 9 }, { 10 }, { 11 }, { 12 }, { 13 }, { 14 }, { 15 }, { 16 }, { 17 }, { 18 },
{ 19 }, { 20 }, { 21 }]])
-}))], joinType=[left])
- LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])
+ LogicalJoin(condition=[=($0, $2)], joinType=[left])
LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])
+ LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])
+ LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
+ LogicalAggregate(group=[{0}])
+ LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }, { 7
}, { 8 }, { 9 }, { 10 }, { 11 }, { 12 }, { 13 }, { 14 }, { 15 }, { 16 }, { 17
}, { 18 }, { 19 }, { 20 }, { 21 }]])
]]>
</Resource>
</TestCase>
@@ -8251,6 +8254,26 @@ LogicalProject(DEPTNO=[$7])
LogicalJoin(condition=[true], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalValues(tuples=[[{ 10 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSubqueryInJoinOnClause">
+ <Resource name="sql">
+ <![CDATA[select t1.* from emp t1
+left join dept t2 on t1.deptno = t2.deptno
+and t1.ename in (select t3.ename from emp t3 )]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[=($1, $2)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
