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 f2df771d86 [CALCITE-7207] Semi Join RelNode cannot be translated into
correct MySQL SQL
f2df771d86 is described below
commit f2df771d86f52e15fba8f626eaeb8b0b9b944823
Author: Zhen Chen <[email protected]>
AuthorDate: Sun Nov 30 23:09:17 2025 +0800
[CALCITE-7207] Semi Join RelNode cannot be translated into correct MySQL SQL
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 4 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 61 ++++++++++++++++++++++
core/src/test/resources/sql/join.iq | 21 ++++++++
3 files changed, 85 insertions(+), 1 deletion(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index 84fb8329ec..54b60c63d7 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -326,7 +326,9 @@ protected Result visitAntiOrSemiJoin(Join e) {
}
sqlSelect.setWhere(sqlCondition);
- if (leftResult.neededAlias != null && sqlSelect.getFrom() != null) {
+ if (leftResult.neededAlias != null
+ && sqlSelect.getFrom() != null
+ && sqlSelect.getFrom().getKind() != SqlKind.JOIN) {
sqlSelect.setFrom(as(sqlSelect.getFrom(), leftResult.neededAlias));
}
return result(sqlSelect, ImmutableList.of(Clause.FROM), e, null);
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index da2c2deefc..a4f78852fb 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -2051,6 +2051,67 @@ private static String toSql(RelNode root, SqlDialect
dialect,
assertThat(toSql(root), isLinux(expectedSql));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7207";>[CALCITE-7207]
+ * Semi Join RelNode cannot be translated into correct MySQL SQL</a>. */
+ @Test void testSemiJoinMysqlNoExtraAlias() {
+ final RelBuilder builder = relBuilder();
+ // Create the left side
+ RelNode leftSubBase = builder
+ .scan("EMP")
+ .project(builder.field("EMPNO"))
+ .build();
+ RelNode leftJoin = builder
+ .push(leftSubBase)
+ .scan("EMP")
+ .join(
+ JoinRelType.INNER, builder.equals(
+ builder.field(2, 0, "EMPNO"),
+ builder.field(2, 1, "EMPNO")))
+ .project(builder.field(0), builder.field(1))
+ .build();
+
+ // Create the right side
+ RelNode rightSubBase = builder
+ .scan("EMP")
+ .project(builder.field("EMPNO"))
+ .build();
+ RelNode rightJoin = builder
+ .push(rightSubBase)
+ .scan("EMP")
+ .join(
+ JoinRelType.INNER, builder.equals(
+ builder.field(2, 0, "EMPNO"),
+ builder.field(2, 1, "EMPNO")))
+ .project(builder.field(0), builder.field(1))
+ .build();
+
+ // Top-level SEMI join
+ final RelNode root = builder
+ .push(leftJoin)
+ .push(rightJoin)
+ .join(
+ JoinRelType.SEMI,
+ builder.and(
+ builder.equals(builder.field(2, 1, "EMPNO"), builder.field(2,
0, "EMPNO")),
+ builder.call(SqlStdOperatorTable.LESS_THAN,
+ builder.field(2, 1, "EMPNO"), builder.field(2, 0,
"EMPNO"))))
+ .project(builder.field(0), builder.field(1))
+ .build();
+
+ final String expected = "SELECT \"t\".\"EMPNO\", \"EMP0\".\"EMPNO\" AS
\"EMPNO0\"\n"
+ + "FROM (SELECT \"EMPNO\"\n"
+ + "FROM \"scott\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN \"scott\".\"EMP\" AS \"EMP0\" ON \"t\".\"EMPNO\" =
\"EMP0\".\"EMPNO\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM (SELECT \"t1\".\"EMPNO\", \"EMP2\".\"EMPNO\" AS \"EMPNO0\"\n"
+ + "FROM (SELECT \"EMPNO\"\nFROM \"scott\".\"EMP\") AS \"t1\"\n"
+ + "INNER JOIN \"scott\".\"EMP\" AS \"EMP2\""
+ + " ON \"t1\".\"EMPNO\" = \"EMP2\".\"EMPNO\") AS \"t2\"\n"
+ + "WHERE \"t\".\"EMPNO\" = \"t2\".\"EMPNO\" AND \"t\".\"EMPNO\" >
\"t2\".\"EMPNO\")";
+ assertThat(toSql(root), isLinux(expected));
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-2792";>[CALCITE-2792]
* StackOverflowError while evaluating filter with large number of OR
diff --git a/core/src/test/resources/sql/join.iq
b/core/src/test/resources/sql/join.iq
index 0c7573baee..7911edbf6b 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -1092,4 +1092,25 @@ on t1.id = t2.id or not exists(select *
!ok
+# [CALCITE-7207] Semi Join RelNode cannot be translated into correct MySQL SQL
+# This SQL comes from RelToSqlConverterTest.testSemiJoinMysqlNoExtraAlias()
+!use scott-mysql
+SELECT "t"."EMPNO", "EMP0"."EMPNO" AS "EMPNO0"
+FROM (SELECT "EMPNO"
+FROM "scott"."EMP") AS "t"
+INNER JOIN "scott"."EMP" AS "EMP0" ON "t"."EMPNO" = "EMP0"."EMPNO"
+WHERE EXISTS (SELECT 1
+FROM (SELECT "t1"."EMPNO", "EMP2"."EMPNO" AS "EMPNO0"
+FROM (SELECT "EMPNO"
+FROM "scott"."EMP") AS "t1"
+INNER JOIN "scott"."EMP" AS "EMP2" ON "t1"."EMPNO" = "EMP2"."EMPNO") AS "t2"
+WHERE "t"."EMPNO" = "t2"."EMPNO" AND "t"."EMPNO" > "t2"."EMPNO");
++-------+--------+
+| EMPNO | EMPNO0 |
++-------+--------+
++-------+--------+
+(0 rows)
+
+!ok
+
# End join.iq
