Weihua Zhang created CALCITE-7897:
-------------------------------------
Summary: SubQueryRemoveRule fails or returns wrong results when a
correlated sub-query is an operand of another sub-query
Key: CALCITE-7897
URL: https://issues.apache.org/jira/browse/CALCITE-7897
Project: Calcite
Issue Type: Bug
Reporter: Weihua Zhang
Assignee: Weihua Zhang
If a correlated sub-query occurs in the operands of another sub-query,
SubQueryRemoveRule either fails or returns wrong results.
For example (expected result: two rows, A = 1 and A = 3):
{code:sql}
select l.a
from (values (0, 1), (1, 2), (2, 3)) as l(k, a)
where (select max(v) from (values (0), (1), (2)) as s(v) where s.v <= l.k)
in (select x from (values (0), (2)) as t(x) where t.x >= l.a - 1)
order by l.a;
{code}
With RelDecorrelator it returns 4 rows:
{noformat}
+---+
| A |
+---+
| 1 |
| 1 |
| 2 |
| 3 |
+---+
{noformat}
With TopDownGeneralDecorrelator it fails with
"Unable to implement ... cannot translate expression $SCALAR_QUERY(...)".
If the nested sub-query references both sides of a join, it fails with
"AssertionError: Correlation variable $cor0 should be defined":
{code:sql}
select l.a, r.b
from (values (0, 1), (1, 2), (2, 3)) as l(k, a),
(values (0, 1), (1, 2), (5, 3)) as r(k, b)
where (select count(*) from (values (0), (1), (2)) as s(v)
where s.v between l.k and r.k)
in (select x from (values (1), (2)) as t(x))
order by l.a, r.b;
{code}
The same happens in a JOIN ON condition. It works if the nested sub-query
is not correlated (e.g. CALCITE-6824), or if the IN is in the SELECT list.
The cause is that SubQueryRemoveRule finds sub-queries with
RexUtil.SubQueryFinder, which returns the outermost one first. Rewriting it
(e.g. rewriteIn) moves its operands, including the nested sub-query, into
the condition of the new Join or into a new Project. The nested sub-query
still references the correlation variable, but that variable was defined
by the original Filter/Join, not by the new relational expression.
A fix is to rewrite the innermost sub-query first, while it is still
directly under the relational expression that defines its correlation
variables; it then becomes an input reference, and the operands of the
enclosing sub-query no longer contain sub-queries. This also requires
ReplaceSubQueryShuttle to look into the operands of other sub-queries, and
LogicVisitor to visit the operands of a sub-query (with
TRUE_FALSE_UNKNOWN logic, since their value is compared with the rows of
the sub-query).
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
