[ 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)