[jira] [Commented] (CALCITE-7753) CorrelateProjectExtractor corrupts plans with nested Correlates that reuse the same correlation id

["Sergey Nuyanzin (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-7753?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18124280#comment-18124280 ] 

Sergey Nuyanzin commented on CALCITE-7753:
------------------------------------------

Merged as [25d62c555af1814d0789f85ff5fc6b4489d52781b|https://github.com/apache/calcite/commit/25d62c555af1814d0789f85ff5fc6b4489d52781b]

> CorrelateProjectExtractor corrupts plans with nested Correlates that reuse the same correlation id
> --------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-7753
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7753
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Sergey Nuyanzin
>            Assignee: Sergey Nuyanzin
>            Priority: Major
>              Labels: pull-request-available
>
> {{CorrelateProjectExtractor}} moves correlated expressions from a correlate's right side to its left. If the right side contains a nested correlate reusing the same id (e.g. {{$cor0}}), the extractor also grabs the inner references and adds them to the outer correlate's {{requiredColumns}}. The decorrelated right never produces them, so the plan is inconsistent and {{RelDecorrelator}} fails with an {{AssertionError}}.
> Calcite's SQL translation never nests two correlates sharing one id, so it isn't reachable from SQL; however downstream planners like Flink build this shape.
> Example — outer correlate over {{EMP}} uses {{$cor0.COMM}}, nested correlate over {{DEPT}} reuses {{$cor0}} and uses {{$cor0.DEPTNO}}:
> {noformat}
> LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{6}])
>   LogicalTableScan(table=[[scott, EMP]])
>   LogicalFilter(condition=[IS NULL($cor0.COMM)])
>     LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
>       LogicalTableScan(table=[[scott, DEPT]])
>       LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
>         LogicalTableScan(table=[[scott, EMP]])
> {noformat}
> {{decorrelateQuery}} throws {{AssertionError}}, the same plan with a distinct nested id works.
> Example of problem SQL from Flink 
> {code:sql}
> SELECT b FROM l
> WHERE (CASE WHEN a NOT IN (SELECT i FROM t1 WHERE l.a = t1.i) THEN 1 ELSE 2 END)
>       NOT IN (SELECT d FROM r WHERE l.c = r.f)
> {code}
> subquery inside another subquery, and both look back at the same outer table l:
> outer: ... NOT IN (SELECT d FROM r WHERE l.c = r.f) uses l.c.
> inner is hidden inside the CASE: a NOT IN (SELECT i FROM t1 WHERE l.a = t1.i) - uses l.a.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)