[
https://issues.apache.org/jira/browse/CALCITE-7272?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7272:
----------------------------------
Description:
case 1:
{code:sql}
SELECT ename,
(
SELECT sum(c)
FROM (
SELECT deptno AS c
FROM dept
WHERE dept.deptno = emp.deptno
UNION ALL
SELECT 2 AS c
FROM bonus
WHERE bonus.job = emp.job
) AS union_subquery
) AS correlated_sum FROM
emp;
LogicalProject(ENAME=[$1], CORRELATED_SUM=[$8]), id = 2619
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], EXPR$0=[$8]), id = 2628
LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{2,
7}]), id = 2626
LogicalTableScan(table=[[testdb, emp]]), id = 2542
LogicalAggregate(group=[{}], EXPR$0=[SUM($0)]), id = 2615
LogicalUnion(all=[true]), id = 2613
LogicalProject(C=[$0]), id = 2606
LogicalFilter(condition=[=($0, $cor0.deptno)]), id = 2604
LogicalTableScan(table=[[testdb, dept]]), id = 2544
LogicalProject(C=[2]), id = 2611
LogicalFilter(condition=[=($1, $cor0.job)]), id = 2609
LogicalTableScan(table=[[testdb, bonus]]), id = 2548
{code}
case 2:
{code:sql}
SELECT *,
(SELECT COUNT(*)
FROM (
SELECT * FROM emp WHERE emp.deptno = dept.deptno
UNION ALL
SELECT * FROM emp
) AS sub
GROUP BY deptno
) AS num_dept_groups
FROM dept;
{code}
was:
case 1:
{code:sql}
SELECT ename,
(
SELECT sum(c)
FROM (
SELECT deptno AS c
FROM dept
WHERE dept.deptno = emp.deptno
UNION ALL
SELECT 2 AS c
FROM bonus
WHERE bonus.job = emp.job
) AS union_subquery
) AS correlated_sum FROM
emp;
{code}
case 2:
{code:sql}
SELECT *,
(SELECT COUNT(*)
FROM (
SELECT * FROM emp WHERE emp.deptno = dept.deptno
UNION ALL
SELECT * FROM emp
) AS sub
GROUP BY deptno
) AS num_dept_groups
FROM dept;
{code}
> Subqueries cannot be decorrelated if have set op
> ------------------------------------------------
>
> Key: CALCITE-7272
> URL: https://issues.apache.org/jira/browse/CALCITE-7272
> Project: Calcite
> Issue Type: Improvement
> Reporter: weihua zhang
> Priority: Major
>
> case 1:
> {code:sql}
> SELECT ename,
> (
> SELECT sum(c)
> FROM (
> SELECT deptno AS c
> FROM dept
> WHERE dept.deptno = emp.deptno
> UNION ALL
> SELECT 2 AS c
> FROM bonus
> WHERE bonus.job = emp.job
> ) AS union_subquery
> ) AS correlated_sum FROM
> emp;
> LogicalProject(ENAME=[$1], CORRELATED_SUM=[$8]), id = 2619
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7], EXPR$0=[$8]), id = 2628
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{2, 7}]), id = 2626
> LogicalTableScan(table=[[testdb, emp]]), id = 2542
> LogicalAggregate(group=[{}], EXPR$0=[SUM($0)]), id = 2615
> LogicalUnion(all=[true]), id = 2613
> LogicalProject(C=[$0]), id = 2606
> LogicalFilter(condition=[=($0, $cor0.deptno)]), id = 2604
> LogicalTableScan(table=[[testdb, dept]]), id = 2544
> LogicalProject(C=[2]), id = 2611
> LogicalFilter(condition=[=($1, $cor0.job)]), id = 2609
> LogicalTableScan(table=[[testdb, bonus]]), id = 2548
> {code}
> case 2:
> {code:sql}
> SELECT *,
> (SELECT COUNT(*)
> FROM (
> SELECT * FROM emp WHERE emp.deptno = dept.deptno
> UNION ALL
> SELECT * FROM emp
> ) AS sub
> GROUP BY deptno
> ) AS num_dept_groups
> FROM dept;
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
