[
https://issues.apache.org/jira/browse/CALCITE-6692?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17898643#comment-17898643
]
Stamatis Zampetakis commented on CALCITE-6692:
----------------------------------------------
The FilterCorrelateRule fails when applied to the following sub-plan:
{noformat}
LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
{noformat}
When the "$cor0.DEPTNO" is above the correlate, the cor0 refers to the
departments table. If it is pushed under the correlate it "changes" scope and
it refers to the employee table so pushing the filter in this case is
semantically incorrect. Moreover, having a correlation to the left input of a
correlate violates the structural preconditions of the {{Correlate}} expression
and this leads to the {{AssertionError}}.
> 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)
