Stamatis Zampetakis created CALCITE-6692:
--------------------------------------------
Summary: 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
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)
