Weihua Zhang created CALCITE-7899:
-------------------------------------
Summary: 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
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)
