[
https://issues.apache.org/jira/browse/CALCITE-4718?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4718:
---------------------------------
Description:
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 CALCITE-4673 (see
[comment|https://issues.apache.org/jira/browse/CALCITE-4673?focusedCommentId=17390703&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17390703]),
and also [additional
tests|https://github.com/julianhyde/calcite/commit/b37bdf61751a47b995a4a5fd2e45baf9aba03cb4#diff-bf7c14529204041670fb26cf7cd03ac37d3212a31fafd426e05b9e249819c92cR6733].
was:
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
> 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
> Priority: Major
>
> 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 CALCITE-4673 (see
> [comment|https://issues.apache.org/jira/browse/CALCITE-4673?focusedCommentId=17390703&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17390703]),
> and also [additional
> tests|https://github.com/julianhyde/calcite/commit/b37bdf61751a47b995a4a5fd2e45baf9aba03cb4#diff-bf7c14529204041670fb26cf7cd03ac37d3212a31fafd426e05b9e249819c92cR6733].
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
