[CALCITE-6692] AssertionError in FilterCorrelateRule when decorrelating sub-queries with common variable

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.38.0
• Component/s: core
• Assignee: Stamatis Zampetakis
• Reporter: Stamatis Zampetakis
• Created: 15/Nov/24 14:24
• Updated: 15/Nov/24 14:47

Description

Consider the following query that searches for departments that have at least one employee and they are associated with at least one project.

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

Notice that "d.deptno" column is referenced by both sub-queries.

The plan after applying the SubQueryRemoveRule transformations is shown below.

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]])

The RelDecorrelator fails to process this plan and throws the following AssertionError.

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)