[
https://issues.apache.org/jira/browse/CALCITE-6804?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis reassigned CALCITE-6804:
--------------------------------------------
Assignee: Anton Kovalevsky
> Anti-join with WHERE NOT EXISTS syntax has corrupted condition
> --------------------------------------------------------------
>
> Key: CALCITE-6804
> URL: https://issues.apache.org/jira/browse/CALCITE-6804
> Project: Calcite
> Issue Type: Bug
> Reporter: Anton Kovalevsky
> Assignee: Anton Kovalevsky
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.39.0
>
>
> Queries like:
> {code:sql}
> SELECT * FROM
> (
> SELECT field1 FROM table1 JOIN table2 ON table1.field1 = table2.field2
> ) selected
> WHERE NOT EXISTS (select 1 from table3 where table3.field3 = selected.field1)
> {code}
> are being converted into
> {code:sql}
> SELECT * FROM
> (
> SELECT field1 FROM table1 JOIN table2 ON table1.field1 = table2.field2
> ) selected
> WHERE NOT EXISTS (select 1 from table3 where table3.field3 =
> table2.<random_field>)
> {code}
> Example I added to RelToSqlConverterTest
> {code:java}
> @Test void testAntiJoinWithWhereNotExists() {
> final String sql = "SELECT * FROM (select * from (select
> e1.\"product_id\"\n"
> + "FROM \"foodmart\".\"product\" e1 LEFT JOIN
> \"foodmart\".\"product\" e3 on e1.\"product_id\" = e3.\"product_id\") s where
> true) selected where not exists\n"
> + "(select 1 from \"foodmart\".\"product\" e2 where e2.\"product_id\"
> = selected.\"product_id\")";
> final String expected = "SELECT *\n" +
> "FROM (SELECT \"product\".\"product_id\"\n" +
> "FROM \"foodmart\".\"product\"\n" +
> "LEFT JOIN \"foodmart\".\"product\" AS \"product0\" ON
> \"product\".\"product_id\" = \"product0\".\"product_id\"" +
> ") AS \"t\"\n" +
> "WHERE EXISTS (SELECT *\nFROM \"foodmart\".\"product\"\nWHERE
> \"product_id\" = \"t\".\"product_class_id\")"
> ;
> sql(sql).ok(expected);
> }
> {code}
> {code:java}
> Expected: is "SELECT ...) AS \"t\"\nWHERE EXISTS (... WHERE \"product_id\" =
> \"t\".\"product_id\")"
> but: was "SELECT ...) AS \"t\"\nWHERE EXISTS (... WHERE \"product_id\" =
> \"product1\".\"product_class_id\")"
> {code}
> *product1* is generated alias for a query from one of sub-queries, and
> *product_class_id* is a field from that misused table scan.
> My high level understanding is that query with WHERE NOT EXISTS syntax is
> considered as LogicalFilter and appropriate pieces of code (like
> *AliasReplacementShuttle* and {*}visitAntiOrSemiJoin{*}) are not invoked.
> And visit of Filter node builds alias context inappropriately.
> Directions I am trying:
> - Duplicate antiJoin visit under the scope of filter visit.
> - Explicit rule to convert Filter to Join manually. Feels artificial because
> it planner should be triggered, it requires a convention.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
