This is an automated email from the ASF dual-hosted git repository.
dmsysolyatin 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 f4f0e978dc [CALCITE-7127] RelToSqlConverter corrupts condition inside
an anti-join with WHERE NOT EXISTS
f4f0e978dc is described below
commit f4f0e978dc26739b8e966706bc1401ed40ded61b
Author: Dmitry Sysolyatin <[email protected]>
AuthorDate: Tue Aug 5 15:10:26 2025 +0300
[CALCITE-7127] RelToSqlConverter corrupts condition inside an anti-join
with WHERE NOT EXISTS
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 7 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 104 +++++++++++++++++++++
2 files changed, 105 insertions(+), 6 deletions(-)
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 4eab979beb..67f3948c8c 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
@@ -124,9 +124,6 @@
import static com.google.common.base.Preconditions.checkArgument;
import static org.apache.calcite.rex.RexLiteral.stringValue;
-import static org.apache.calcite.sql.SqlKind.EXISTS;
-import static org.apache.calcite.sql.SqlKind.IN;
-import static org.apache.calcite.sql.SqlKind.NOT;
import static org.apache.calcite.util.Util.last;
import static java.util.Objects.requireNonNull;
@@ -478,9 +475,7 @@ public Result visit(Filter e) {
return builder.result();
} else {
Result x = visitInput(e, 0, Clause.WHERE);
- if (e.getCondition().getKind() == NOT
- || e.getCondition().getKind() == EXISTS
- || e.getCondition().getKind() == IN) {
+ if (!e.getVariablesSet().isEmpty()) {
x = x.resetAlias();
}
parseCorrelTable(e, x);
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 f0420e7919..5f846edd61 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
@@ -17,6 +17,7 @@
package org.apache.calcite.rel.rel2sql;
import org.apache.calcite.config.NullCollation;
+import org.apache.calcite.plan.Contexts;
import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelOptRule;
import org.apache.calcite.plan.RelTraitDef;
@@ -553,6 +554,85 @@ private static String toSql(RelNode root, SqlDialect
dialect,
sql(query).ok(expected);
}
+ /**
+ * Tests that an identity project is pruned over scan during SQL conversion,
+ * and that the correct table alias is propagated to the final projection.
+ */
+ @Test void testPruneIdentityProjectOverScan() {
+ final RelBuilder relBuilder =
+ RelBuilder.create(
+ // Disable merge-project optimization by RelBuilder to generate a plan
+ // containing multiple LogicalProject nodes like:
+ // LogicalProject(product_id=[$0])
+ // LogicalProject(product_id=[$0], product_name=[$2])
+ RelBuilderTest.config().context(
+ Contexts.of(RelBuilder.Config.DEFAULT.withBloat(-1)))
+ .build());
+ final RelNode root = relBuilder
+ .scan("EMP")
+ // This identity project (which would be aliased 't') should not be
used in SQL
+ .project(relBuilder.fields(),
+ ImmutableList.of(), true)
+ // This identity project (which would be aliased 't0') should not be
used in SQL
+ .project(relBuilder.fields(),
+ ImmutableList.of(), true)
+ // this project should use `EMP` as table alias for fields instead of
`t` or `t0`
+ .project(ImmutableList.of(relBuilder.field("EMPNO")),
+ ImmutableList.of(), true)
+ .build();
+ final String expected = "SELECT \"EMP\".\"EMPNO\"\n"
+ + "FROM \"scott\".\"EMP\" AS \"EMP\"";
+ final SqlDialect sqlDialect = new
CalciteSqlDialect(CalciteSqlDialect.DEFAULT_CONTEXT) {
+ // Force use of explicit table aliases everywhere
+ @Override public boolean hasImplicitTableAlias() {
+ return false;
+ }
+ };
+ relFn(b -> root).dialect(sqlDialect).ok(expected);
+ }
+
+ /**
+ * Tests that an identity projection over a join is pruned during SQL
conversion,
+ * and that the correct table alias is propagated to the final projection.
+ */
+ @Test void testPruneIdentityProjectOverJoin() {
+ final RelBuilder relBuilder =
+ RelBuilder.create(
+ // Disable merge-project optimization by RelBuilder to generate a plan
+ // containing multiple LogicalProject nodes like:
+ // LogicalProject(product_id=[$0])
+ // LogicalProject(product_id=[$0], product_name=[$2])
+ RelBuilderTest.config().context(
+ Contexts.of(RelBuilder.Config.DEFAULT.withBloat(-1)))
+ .build());
+ final RelNode root = relBuilder
+ .scan("EMP")
+ .project(relBuilder.field("EMPNO"), relBuilder.field("DEPTNO"))
+ .scan("DEPT")
+ .project(relBuilder.field("DEPTNO"))
+ // join alias is `t`
+ .join(JoinRelType.INNER, relBuilder.literal(false))
+ // This identity project (which would be aliased 't1') should not be
used in SQL
+ .project(relBuilder.fields(), ImmutableList.of(), true)
+ // The final SQL must use the join's alias ('t') because the
+ // project above is not used in SQL.
+ .project(ImmutableList.of(relBuilder.field("EMPNO")),
+ ImmutableList.of(), true)
+ .build();
+ final String expected = "SELECT \"t\".\"EMPNO\"\n"
+ + "FROM (SELECT \"EMP\".\"EMPNO\", \"EMP\".\"DEPTNO\"\n"
+ + "FROM \"scott\".\"EMP\" AS \"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"DEPT\".\"DEPTNO\"\n"
+ + "FROM \"scott\".\"DEPT\" AS \"DEPT\") AS \"t0\" ON FALSE";
+ final SqlDialect sqlDialect = new
CalciteSqlDialect(CalciteSqlDialect.DEFAULT_CONTEXT) {
+ // Force use of explicit table aliases everywhere
+ @Override public boolean hasImplicitTableAlias() {
+ return false;
+ }
+ };
+ relFn(b -> root).dialect(sqlDialect).ok(expected);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5906";>[CALCITE-5906]
* JDBC adapter should generate TABLESAMPLE</a>. */
@@ -10383,6 +10463,30 @@ private void checkLiteral2(String expression, String
expected) {
sql(sql).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7127";>[CALCITE-7127]
+ * RelToSqlConverter corrupts condition inside an anti-join with WHERE NOT
EXISTS.</a>. */
+ @Test void testAntiJoinWithComplexInput3() {
+ final String sql = "select e3.\"product_id\", e3.\"product_name\" "
+ + "from ("
+ + "select 1 AS \"additional_column\", e1.\"product_id\",
e1.\"product_name\" from \"foodmart\".\"product\" e1 "
+ + "left join \"foodmart\".\"product\" e2 on e1.\"product_id\" =
e2.\"product_id\""
+ + ") as e3 "
+ + "where e3.\"product_name\" IS NOT NULL AND NOT EXISTS("
+ + "select 1 from \"foodmart\".\"employee\" e4 "
+ + "where e4.\"employee_id\" = e3.\"additional_column\""
+ + ")";
+ final String expected =
+ "SELECT \"product_id\", \"product_name\"\n"
+ + "FROM (SELECT 1 AS \"additional_column\",
\"product\".\"product_id\", \"product\".\"product_name\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "LEFT JOIN \"foodmart\".\"product\" AS \"product0\" ON
\"product\".\"product_id\" = \"product0\".\"product_id\") AS \"t\"\n"
+ + "WHERE \"product_name\" IS NOT NULL AND NOT EXISTS (SELECT *\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "WHERE \"employee_id\" = \"t\".\"additional_column\")";
+ sql(sql).ok(expected);
+ }
+
@Test void testFilterWithSubQuery() {
final String sql = "SELECT * FROM "
+ "(select * from ("
