[
https://issues.apache.org/jira/browse/CALCITE-6692?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17898647#comment-17898647
]
Stamatis Zampetakis commented on CALCITE-6692:
----------------------------------------------
Apart from the wrong filter push-down that throws the {{AssertionError}}, the
plan obtained after the applying the {{SubQueryRemoveRule}} transformations is
also somewhat problematic. It contains two {{LogicalCorrelate}} expressions
that are nested and they both use the same correlation id ($cor0). This creates
ambiguity and problems if we try to move expressions with correlation variables
around the plan. Given the current situation it seems in general unsafe to move
correlated expressions across {{Correlate}} nodes.
> AssertionError in FilterCorrelateRule when decorrelating sub-queries with
> common variable
> -----------------------------------------------------------------------------------------
>
> Key: CALCITE-6692
> URL: https://issues.apache.org/jira/browse/CALCITE-6692
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.38.0
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
>
> Consider the following query that searches for departments that have at least
> one employee and they are associated with at least one project.
> {code:sql}
> SELECT d.name
> FROM dept d
> WHERE EXISTS (SELECT 1
> FROM emp e
> WHERE d.deptno = e.deptno AND
> EXISTS (SELECT 1
> FROM project p
> WHERE d.deptno = p.deptno
> {code}
> Notice that "d.deptno" column is referenced by both sub-queries.
> The plan after applying the {{SubQueryRemoveRule}} transformations is shown
> below.
> {noformat}
> LogicalProject(NAME=[$1])
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{0}])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{0}])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalFilter(condition=[=($cor0.DEPTNO, $2)])
> LogicalTableScan(table=[[CATALOG, SALES, PROJECT]])
> {noformat}
> The {{RelDecorrelator}} fails to process this plan and throws the following
> {{AssertionError}}.
> {noformat}
> java.lang.AssertionError: contains $cor0
> at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31)
> at
> org.apache.calcite.plan.RelOptUtil.notContainsCorrelation(RelOptUtil.java:345)
> at org.apache.calcite.rel.core.Correlate.isValid(Correlate.java:147)
> at org.apache.calcite.rel.core.Correlate.<init>(Correlate.java:109)
> at
> org.apache.calcite.rel.logical.LogicalCorrelate.<init>(LogicalCorrelate.java:73)
> at
> org.apache.calcite.rel.logical.LogicalCorrelate.copy(LogicalCorrelate.java:133)
> at
> org.apache.calcite.rel.logical.LogicalCorrelate.copy(LogicalCorrelate.java:49)
> at org.apache.calcite.rel.core.Correlate.copy(Correlate.java:152)
> at
> org.apache.calcite.rel.rules.FilterCorrelateRule.onMatch(FilterCorrelateRule.java:109)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:350)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:420)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeRuleInstance(HepPlanner.java:243)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance$State.execute(HepInstruction.java:178)
> at
> org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:211)
> at
> com.google.common.collect.ImmutableList.forEach(ImmutableList.java:423)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:210)
> at
> org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:205)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:191)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:313)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:250)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:210)
> at
> org.apache.calcite.test.RelOptFixture.checkPlanning(RelOptFixture.java:392)
> at org.apache.calcite.test.RelOptFixture.check(RelOptFixture.java:334)
> at org.apache.calcite.test.RelOptFixture.check(RelOptFixture.java:318)
> at
> org.apache.calcite.test.RelOptRulesTest.testNestedExistsSubQueriesWithCommonCorrelationVariable(RelOptRulesTest.java:8446)
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
