[
https://issues.apache.org/jira/browse/CALCITE-6692?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18041605#comment-18041605
]
weihua zhang commented on CALCITE-6692:
---------------------------------------
After more than a year of evolution, the error has now changed: the query can
execute and return results, but the results are incorrect.
The root cause still lies in some RBO rules within
{{{}RelDecorrelator.java{}}}. Could we stop using these RBO rules in
{{RelDecorrelator.java}} entirely? The core RelDecorrelator framework is
actually excellent, and removing these error-prone RBO rules would make it far
more robust—freeing it from the limitations and issues caused by these rules.
> 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)
