[
https://issues.apache.org/jira/browse/CALCITE-7440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18064281#comment-18064281
]
Zhen Chen commented on CALCITE-7440:
------------------------------------
Thank you very much for your bug report and pull request. I want to say that,
strictly speaking, we may need to ensure the correctness of transformations
between any rules in the plan. However, in practice, that might not always be
the case. For example, CoreRules.FILTER_SUB_QUERY_TO_CORRELATE and
CoreRules.FILTER_SUB_QUERY_TO_MARK_CORRELATE may not actually be used together,
as they serve different decorrelation algorithms. I took a quick look at the
code, and from the title, it appears that a certain rule loses information
during transformation, which is then corrected in the RelToSqlConverter. Is
this behavior as expected?
> RelToSqlConverter throws NPE (variable $cor1 not found) for correlated
> projection after semi-join rewrites
> ----------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7440
> URL: https://issues.apache.org/jira/browse/CALCITE-7440
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Reporter: Bruno Volpato
> Priority: Minor
> Labels: pull-request-available
>
> Reproduction query
> {code:sql}
> WITH product_keys AS (
> SELECT p."product_id",
> (SELECT MAX(p3."product_id")
> FROM "foodmart"."product" p3
> WHERE p3."product_id" = p."product_id") AS "mx"
> FROM "foodmart"."product" p
> )
> SELECT DISTINCT pk."product_id"
> FROM product_keys pk
> LEFT JOIN "foodmart"."product" p2 USING ("product_id")
> WHERE pk."product_id" IN (
> SELECT p4."product_id"
> FROM "foodmart"."product" p4
> )
> {code}
> Optimizer rules
> {code}
> CoreRules.FILTER_SUB_QUERY_TO_CORRELATE
> CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE
> CoreRules.JOIN_SUB_QUERY_TO_CORRELATE
> CoreRules.FILTER_SUB_QUERY_TO_MARK_CORRELATE
> CoreRules.PROJECT_SUB_QUERY_TO_MARK_CORRELATE
> CoreRules.MARK_TO_SEMI_OR_ANTI_JOIN_RULE
> CoreRules.PROJECT_TO_SEMI_JOIN
> CoreRules.JOIN_TO_SEMI_JOIN
> CoreRules.SEMI_JOIN_FILTER_TRANSPOSE
> CoreRules.SEMI_JOIN_JOIN_TRANSPOSE
> {code}
> Generated SQL
> {code:sql}
> SELECT "t4"."product_id"
> FROM (SELECT "$cor0"."product_id", "t1"."EXPR$0" AS "mx"
> FROM "foodmart"."product" AS "$cor0",
> LATERAL (SELECT MAX("product_id") AS "EXPR$0"
> FROM "foodmart"."product"
> WHERE "product_id" = "$cor0"."product_id") AS "t1"
> WHERE EXISTS (SELECT 1
> FROM (SELECT "product_id"
> FROM "foodmart"."product") AS "t3"
> WHERE "$cor0"."product_id" = "t3"."product_id")) AS "t4"
> LEFT JOIN "foodmart"."product" AS "product2" ON "t4"."product_id" =
> "product2"."product_id"
> GROUP BY "t4"."product_id"
> {code}
> Actual behavior
> RelToSql conversion fails with:
> {code}
> java.lang.NullPointerException: variable $cor1 is not found
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.getAliasContext(SqlImplementor.java:1590)
> {code}
> Expected behavior
> RelToSqlConverter should preserve correlation scope and generate valid SQL
> for this rule pipeline, without missing correlation variables.
> Environment
> - Calcite main branch (1.42.0-SNAPSHOT)
> - RelToSqlConverterTest with PostgreSQL dialect
> - Java 21
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
