[jira] [Created] (CALCITE-4718) Erroneous correlate variables in case of using collect sub-queries into ARRAY, MAP and MULTISET.

["Evgeny Stanilovsky (Jira)" <ji...@apache.org>]

Evgeny Stanilovsky created CALCITE-4718:
-------------------------------------------

             Summary: Erroneous correlate variables in case of using collect sub-queries into ARRAY, MAP and MULTISET.
                 Key: CALCITE-4718
                 URL: https://issues.apache.org/jira/browse/CALCITE-4718
             Project: Calcite
          Issue Type: Bug
          Components: core
            Reporter: Evgeny Stanilovsky


Query like:
{noformat}
select name,
    array (select *
        from emp
        where deptno = dept.deptno) as emp_array,
    multiset (select *
        from emp
        where deptno = dept.deptno) as emp_multiset,
    map (select empno, job
        from emp
        where deptno = dept.deptno) as job_map
from dept
{noformat}

must generate one correlate variable for all nested sub-queries, instead we erroneously obtain 3 different variables ($cor0.DEPTNO, $cor1.DEPTNO, $cor2.DEPTNO):


{noformat}
LogicalProject(NAME=[$1], EMP_ARRAY=[ARRAY({
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})], EMP_MULTISET=[MULTISET({
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalFilter(condition=[=($7, $cor1.DEPTNO)])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})], JOB_MAP=[MAP({
LogicalProject(EMPNO=[$0], JOB=[$2])
  LogicalFilter(condition=[=($7, $cor2.DEPTNO)])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})])
  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{noformat}

this issue is started from [1] [2] , additional tests [3]

[1] https://issues.apache.org/jira/browse/CALCITE-4673
[2] https://issues.apache.org/jira/browse/CALCITE-4673?focusedCommentId=17390703&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17390703
[3] https://github.com/julianhyde/calcite/commit/b37bdf61751a47b995a4a5fd2e45baf9aba03cb4#diff-bf7c14529204041670fb26cf7cd03ac37d3212a31fafd426e05b9e249819c92cR6733




--
This message was sent by Atlassian Jira
(v8.3.4#803005)