[
https://issues.apache.org/jira/browse/CALCITE-7899?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Weihua Zhang resolved CALCITE-7899.
-----------------------------------
Fix Version/s: 1.43.0
Resolution: Fixed
> SubQueryRemoveRule binds an outer query's correlation variable to the wrong
> input when a Project has several sub-queries
> ------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7899
> URL: https://issues.apache.org/jira/browse/CALCITE-7899
> Project: Calcite
> Issue Type: Bug
> Reporter: Weihua Zhang
> Assignee: Weihua Zhang
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> If a SELECT list contains a sub-query that is correlated with the current
> query, followed by a sub-query that is correlated with an outer query,
> SubQueryRemoveRule binds the outer query's correlation variable to the wrong
> input. Execution then fails with an AssertionError.
> The following query, on the scott schema, fails:
> {code:sql}
> select d.dname,
> (select max((select count(*) from emp as s where s.mgr = e.empno)
> + (select count(*) from emp as s where s.deptno = d.deptno))
> from emp as e) as c
> from dept as d
> order by d.dname;
> {code}
> {noformat}
> java.lang.AssertionError: Correlation variable $cor1 should be defined
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.clearCorrelVariable(EnumerableRelImplementor.java:473)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:142)
> ...
> {noformat}
> The same query with the two inner sub-queries swapped succeeds and returns
> the expected result:
> {noformat}
> +------------+----+
> | DNAME | C |
> +------------+----+
> | ACCOUNTING | 8 |
> | OPERATIONS | 5 |
> | RESEARCH | 10 |
> | SALES | 11 |
> +------------+----+
> {noformat}
> In the plan of the failing query, $cor1 (a row of DEPT) is defined twice. The
> inner Correlate binds it to a row of (EMPNO, EXPR$0), so $cor1.DEPTNO
> resolves to field 0, EMPNO:
> {noformat}
> EnumerableCorrelate(correlation=[$cor1], joinType=[left],
> requiredColumns=[{0}])
> EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
> EnumerableTableScan(table=[[scott, DEPT]])
> EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> EnumerableCalc(...)
> EnumerableCorrelate(correlation=[$cor1], joinType=[left],
> requiredColumns=[{0}]) <-- wrong
> EnumerableCalc(..., EMPNO=[$t0], EXPR$0=[$t4])
> EnumerableCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}])
> ...
> EnumerableAggregate(group=[{}], EXPR$0=[COUNT()])
> EnumerableCalc(..., expr#8=[$cor1], expr#9=[$t8.DEPTNO],
> expr#10=[=($t7, $t9)], ...)
> EnumerableTableScan(table=[[scott, EMP]])
> {noformat}
> *Cause*
> SubQueryRemoveRule.matchProject rewrites one sub-query per call and relies on
> the rule firing again for the others. It rebuilds the Project with
> RelBuilder.project(nodes, fieldNames), which gives the new Project an empty
> variablesSet. This is the same problem that CALCITE-7882 fixed for Join.
> For Project, the problem is hidden by a fallback that was added in
> CALCITE-7057: if the Project's variablesSet is empty, the variables used by
> the sub-query are not filtered at all. In the query above:
> # The first call rewrites the sub-query that uses $cor0. The Project's
> variablesSet is \{$cor0\}, so the rewrite is correct. The new Project has an
> empty variablesSet.
> # The second call rewrites the sub-query that uses $cor1. Because the
> variablesSet is now empty, the fallback keeps $cor1, although it is defined
> by an outer Correlate, not by this Project. The rule creates a second
> Correlate on $cor1 over the Project's input.
> When the sub-queries are swapped, the sub-query that uses $cor1 is rewritten
> first. At that point the variablesSet is still \{$cor0\}, so $cor1 is
> filtered out correctly.
> *Proposed fix*
> Rewrite all sub-queries of a Project in a single call, as matchFilter already
> does. Always filter the variables against the original Project's
> variablesSet, and remove the fallback for an empty variablesSet. The Project
> is then built only once, so no sub-queries are left whose correlation
> variables could be lost.
> Related: CALCITE-7882 (same problem in matchJoin), CALCITE-7057 (introduced
> the fallback).
> {code}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
