[jira] [Comment Edited] (CALCITE-5420) SqlToRel should populate projects corralate id for queries with aggregates.

["James Starr (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5420?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17643968#comment-17643968 ] 

James Starr edited comment on CALCITE-5420 at 12/6/22 5:31 PM:
---------------------------------------------------------------

[~libenchao]There are several queries currently checked in for SqlToRelConverterTest that are incorrect for subqueries in selects.  They still produce the correct results but it needs to be fixed if nested correlated subqueries are to work robustly.  But you are right, a set of selects subqueries are populated correctly, however, not all.

See:
{code:xml}
  </TestCase>
  <TestCase name="testCorrelatedSubQueryInAggregate">
    <Resource name="sql">
      <![CDATA[SELECT SUM(
  (select char_length(name) from dept
   where dept.deptno = emp.empno))
FROM emp]]>
    </Resource>
    <Resource name="plan">
      <![CDATA[
LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
  LogicalProject($f0=[$SCALAR_QUERY({
LogicalProject(EXPR$0=[CHAR_LENGTH($1)])
  LogicalFilter(condition=[=($0, $cor0.EMPNO)])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
})])                                                                                         <-- Notice the lack of a correlated variable attach to the project
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
    </Resource>
  </TestCase>
{code}


was (Author: jamesstarr):
[~libenchao]There are several queries currently checked in for SqlToRelConverterTest that are incorrect for subqueries in selects.  They still produce the correct results but it needs to be fixed if nested correlated subqueries are to work robustly.

See:
{code:xml}
  </TestCase>
  <TestCase name="testCorrelatedSubQueryInAggregate">
    <Resource name="sql">
      <![CDATA[SELECT SUM(
  (select char_length(name) from dept
   where dept.deptno = emp.empno))
FROM emp]]>
    </Resource>
    <Resource name="plan">
      <![CDATA[
LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
  LogicalProject($f0=[$SCALAR_QUERY({
LogicalProject(EXPR$0=[CHAR_LENGTH($1)])
  LogicalFilter(condition=[=($0, $cor0.EMPNO)])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
})])                                                                                         <-- Notice the lack of a correlated variable attach to the project
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
    </Resource>
  </TestCase>
{code}

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