[
https://issues.apache.org/jira/browse/CALCITE-7127?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dmitry Sysolyatin updated CALCITE-7127:
---------------------------------------
Summary: RelToSqlConverter corrupts condition inside an anti-join with
WHERE NOT EXISTS (was: Anti-join with WHERE NOT EXISTS syntax has corrupted
condition)
> RelToSqlConverter corrupts condition inside an anti-join with WHERE NOT EXISTS
> ------------------------------------------------------------------------------
>
> Key: CALCITE-7127
> URL: https://issues.apache.org/jira/browse/CALCITE-7127
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.40.0
> Reporter: Dmitry Sysolyatin
> Assignee: Dmitry Sysolyatin
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.41.0
>
>
> This issue is similar to CALCITE-6804; however, that ticket only partially
> resolved the underlying problem.
> Consider the following query:
> {code:java}
> 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"
> ) {code}
> Calcite incorrectly converts it into the following SQL:
> {code:java}
> SELECT product_id, product_name
> FROM (
> SELECT
> 1 AS additional_column,
> product.product_id,
> product.product_name
> FROM foodmart.product
> LEFT JOIN foodmart.product AS product0
> ON product.product_id = product0.product_id
> ) AS t
> WHERE t.product_name IS NOT NULL AND NOT EXISTS (
> SELECT *
> FROM foodmart.employee
> WHERE employee_id = product.product_class_id
> ); {code}
> The problem is in the NOT EXISTS subquery's WHERE clause.
> In the original query, the correlation condition is:
> {code:java}
> WHERE e4."employee_id" = e3."additional_column"{code}
> In the generated query, this becomes:
> {code:java}
> WHERE employee_id = product.product_class_id {code}
> Calcite incorrectly generates a reference to product.product_class_id from
> the base table instead of t.additional_column from the derived table.
> The generated logical plan is correct:
> {code:java}
> LogicalProject(product_id=[$1], product_name=[$2])
> LogicalFilter(condition=[AND(IS NOT NULL($2), NOT(EXISTS({
> LogicalFilter(condition=[=($0, $cor0.additional_column)])
> JdbcTableScan(table=[[foodmart, employee]])
> })))], variablesSet=[[$cor0]])
> LogicalProject(additional_column=[1], product_id=[$1], product_name=[$3])
> LogicalJoin(condition=[=($1, $16)], joinType=[left])
> JdbcTableScan(table=[[foodmart, product]])
> JdbcTableScan(table=[[foodmart, product]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
