[
https://issues.apache.org/jira/browse/CALCITE-7897?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Weihua Zhang updated CALCITE-7897:
----------------------------------
Description:
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(...)".
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).
was:
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).
> 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
> Priority: Major
>
> 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(...)".
> 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)
