[
https://issues.apache.org/jira/browse/CALCITE-7753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sergey Nuyanzin updated CALCITE-7753:
-------------------------------------
Description:
{{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}
was:
{{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.
> 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}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
