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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Assignee: Unassigned
• Reporter: Evgeny Stanilovsky
• Created: 05/Aug/21 17:05
• Updated: 10/Mar/22 14:53

Description

Query like:

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

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

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

This issue is started from CALCITE-4673 (see comment), and also additional tests.

Issue Links

• Discovered while testing: CALCITE-4673 If arguments to a table function are correlation variables, SqlToRelConverter should eliminate duplicate variables
• is related to: CALCITE-4719 Add variants of RexSubQuery that collect sub-queries into MULTISET, ARRAY and MAP collections
• is related to: CALCITE-4913 Correlated variables in a select list are not deduplicated