[
https://issues.apache.org/jira/browse/CALCITE-7272?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7272:
------------------------------------
Labels: pull-request-available (was: )
> 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
> Labels: pull-request-available
>
> 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)
