[
https://issues.apache.org/jira/browse/CALCITE-7127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18012930#comment-18012930
]
Dmitry Sysolyatin edited comment on CALCITE-7127 at 8/8/25 10:32 PM:
---------------------------------------------------------------------
[~julianhyde] I added {{RelToSqlConverter}} to the title. I think many
Calcite users know about {{{}RelToSqlConverter{}}}. I can’t add any specific
component name because this bug was found in a custom-built pipeline.
Fixed in
[f4f0e97|https://github.com/apache/calcite/commit/f4f0e978dc26739b8e966706bc1401ed40ded61b].
Thanks [~mbudiu] and [~suibianwanwan33] for review!{*}{*}
was (Author: dmsysolyatin):
[~julianhyde] I added {{RelToSqlConverter}} to the title. I think many
Calcite users know about {{{}RelToSqlConverter{}}}. I can’t add any specific
component name because this bug was found in a custom-built pipeline.
Fixed in
[f4f0e97|https://github.com/apache/calcite/commit/f4f0e978dc26739b8e966706bc1401ed40ded61b].
Thanks [~mbudiu] and [~suibianwanwan33] for review!{*}{*}
> 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)
