This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 4fedf95c24 [CALCITE-6804] Anti-join with WHERE NOT EXISTS syntax has
corrupted condition
4fedf95c24 is described below
commit 4fedf95c24d05e1ca71fb7a576c4a704846a7c75
Author: antonkw <[email protected]>
AuthorDate: Mon Feb 10 19:09:35 2025 +0100
[CALCITE-6804] Anti-join with WHERE NOT EXISTS syntax has corrupted
condition
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 10 +++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 67 ++++++++++++++++++++++
2 files changed, 76 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 b171d88d03..1f26b850ce 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
@@ -122,6 +122,9 @@
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;
@@ -456,7 +459,12 @@ public Result visit(Filter e) {
builder.context.toSql(null, e.getCondition())));
return builder.result();
} else {
- final Result x = visitInput(e, 0, Clause.WHERE);
+ Result x = visitInput(e, 0, Clause.WHERE);
+ if (e.getCondition().getKind() == NOT
+ || e.getCondition().getKind() == EXISTS
+ || e.getCondition().getKind() == IN) {
+ x = x.resetAlias();
+ }
parseCorrelTable(e, x);
final Builder builder = x.builder(e);
if (input instanceof Join) {
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 d744247e4a..f9900a6fbf 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
@@ -9176,6 +9176,73 @@ private void checkLiteral2(String expression, String
expected) {
.withPresto().ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6804";>[CALCITE-6804]
+ * Ensures that alias for the left side of anti join is being
propagated.</a>. */
+ @Test void testAntiJoinWithComplexInput() {
+ final String sql = "SELECT * FROM "
+ + "(select * from ("
+ + "select e1.\"product_id\" FROM \"foodmart\".\"product\" e1 "
+ + "LEFT JOIN \"foodmart\".\"product\" e3 "
+ + "on e1.\"product_id\" = e3.\"product_id\""
+ + ")"
+ + ") selected where not exists\n"
+ + "(select 1 from \"foodmart\".\"product\" e2 "
+ + "where selected.\"product_id\" = e2.\"product_id\")";
+ final String expected =
+ "SELECT *\nFROM (SELECT \"product\".\"product_id\"\nFROM
\"foodmart\".\"product\"\n"
+ + "LEFT JOIN \"foodmart\".\"product\" AS \"product0\" "
+ + "ON \"product\".\"product_id\" = \"product0\".\"product_id\") AS
\"t\"\n"
+ + "WHERE NOT EXISTS ("
+ + "SELECT *\nFROM \"foodmart\".\"product\"\nWHERE
\"t\".\"product_id\" = \"product_id\""
+ + ")";
+ sql(sql).ok(expected);
+ }
+
+ @Test void testAntiJoinWithComplexInput2() {
+ final String sql = "SELECT * FROM "
+ + "(select * from ("
+ + "select e1.\"product_id\" FROM \"foodmart\".\"product\" e1 "
+ + "LEFT JOIN \"foodmart\".\"product\" e3 "
+ + "on e1.\"product_id\" = e3.\"product_id\""
+ + ")"
+ + ") selected where not exists\n"
+ + "(select 1 from \"foodmart\".\"product\" e2 "
+ + "where e2.\"product_id\" = selected.\"product_id\" and
e2.\"product_id\" > 10)";
+ final String expected =
+ "SELECT *\nFROM (SELECT \"product\".\"product_id\"\nFROM
\"foodmart\".\"product\"\n"
+ + "LEFT JOIN \"foodmart\".\"product\" AS \"product0\" "
+ + "ON \"product\".\"product_id\" = \"product0\".\"product_id\") AS
\"t\"\n"
+ + "WHERE NOT EXISTS ("
+ + "SELECT *\nFROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" = \"t\".\"product_id\" AND \"product_id\"
> 10"
+ + ")";
+ sql(sql).ok(expected);
+ }
+
+ @Test void testFilterWithSubQuery() {
+ final String sql = "SELECT * FROM "
+ + "(select * from ("
+ + "select e1.\"product_id\" FROM \"foodmart\".\"product\" e1 "
+ + "LEFT JOIN \"foodmart\".\"product\" e3 "
+ + "on e1.\"product_id\" = e3.\"product_id\""
+ + ")"
+ + ") selected where 1 in\n"
+ + "(select \"gross_weight\" from \"foodmart\".\"product\" e2 "
+ + "where e2.\"product_id\" = selected.\"product_id\" and
e2.\"product_id\" > 10)";
+
+ final String expected =
+ "SELECT *\nFROM (SELECT \"product\".\"product_id\"\nFROM
\"foodmart\".\"product\"\n"
+ + "LEFT JOIN \"foodmart\".\"product\" AS \"product0\" "
+ + "ON \"product\".\"product_id\" = \"product0\".\"product_id\") AS
\"t\"\n"
+ + "WHERE CAST(1 AS DOUBLE) IN ("
+ + "SELECT \"gross_weight\"\nFROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" = \"t\".\"product_id\" AND \"product_id\"
> 10)";
+
+ sql(sql).ok(expected);
+ }
+
+
/** Fluid interface to run tests. */
static class Sql {
private final CalciteAssert.SchemaSpec schemaSpec;
