[
https://issues.apache.org/jira/browse/CALCITE-5420?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17643683#comment-17643683
]
James Starr commented on CALCITE-5420:
--------------------------------------
The subquery/decorrelation flow:
# SqlToRel(expand=false) - Creates a query with rex subqueries.
# SubQueryRemoval - Rewrites rex subqueries to correlate rel nodes
# Decorrelation - rewrites correlate node to joins
[~julianhyde], This is a bug for before decorrelation. There are 2 ways to
expand sub-queries, SqlToRel with expand true or SubQueryRemoval(Rewrites
RexSubquery to correlate nodes). This bug is for the latter SqlToRel does not
add the CorrelateId to the rel the correlate variables in a rex subquery should
be resolved. Currently, the SubQueryRemoval assumes that subqueries are not
nested and the rel node contain the subquery is what the correlate variables in
the subquery should be resolved
to[see|https://github.com/apache/calcite/pull/2996/files#diff-a57af2470b3215be0ce7d94a226f1997d9417c613fd7f32097fd07543b30633aR848].
Not have the correlate id in the rel and assume that correlate variable is
resolved to the rel contain the subquery is not a problem unless you have
nested subqueries.
So far I have determines 3 bugs prevent nested correlated subqueries from
working:
# SqlToRel does not populate the rel with the correlate id which is need to
determine where correlated variables should be resolved to. The correlate id
appears to only populated in filters for where clauses.
# SubQueryRemoval assumes the correlate variables are to be resolved in the
immediate rel node instead of the rel with the correlate id
# Decorrelation does not handle multiple correlated variables in the same
filter rel.
There might be more problems, but the first 2 need to be solved before the 3rd
one is really relevant.
> SqlToRel should populate projects corralate id for queries with aggregates.
> ---------------------------------------------------------------------------
>
> Key: CALCITE-5420
> URL: https://issues.apache.org/jira/browse/CALCITE-5420
> Project: Calcite
> Issue Type: Bug
> Reporter: James Starr
> Assignee: James Starr
> Priority: Major
>
> The following query does not populate correlate id in the project when
> SqlToRel.expand=false:
> {code:sql}
> SELECT SUM(
> (select char_length(dname) from "scott".dept where dept.deptno =
> emp.empno)) as s
> FROM "scott".emp
> {code}
> Having the correlate id populated is important for expanding nested queries.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
