[
https://issues.apache.org/jira/browse/CALCITE-7116?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18011682#comment-18011682
]
Yu Xu commented on CALCITE-7116:
--------------------------------
[~jensen] had research it before and nearly finished，so I take this issue to
him.
> Optimize queries with GROUPING SETS by converting them into equivalent UNION
> ALL of GROUP BY operations.
> --------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7116
> URL: https://issues.apache.org/jira/browse/CALCITE-7116
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Reporter: Zhen Chen
> Assignee: Yu Xu
> Priority: Minor
> Fix For: 1.41.0
>
>
> Currently, GROUPING SETS operations may not be optimally executed in some
> cases. This ticket proposes a rule to transform GROUPING SETS into a series
> of UNION ALL operations, each with its own GROUP BY clause.
> Original query:
> {code:java}
> SELECT a, b, c FROM t GROUP BY GROUPING SETS ((a,b), (a,c))
> {code}
> Transformed to:
> {code:java}
> SELECT a, b, NULL AS c FROM t GROUP BY a, b
> UNION ALL
> SELECT a, NULL AS b, c FROM t GROUP BY a, c
> {code}
> Supplementary description:
> SQL is
> {code:sql}
> SELECT deptno,
> job,
> sal,
> SUM(comm),
> GROUPING(deptno) AS deptno_flag,
> GROUPING(job) AS job_flag,
> GROUPING(sal) AS sal_flag
> FROM emp
> GROUP BY GROUPING SETS ((deptno, job), (deptno, sal))
> {code}
> Original Plan is
> {code:sql}
> LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}, {0, 2}]],
> EXPR$3=[SUM($3)], DEPTNO_FLAG=[GROUPING($0)], JOB_FLAG=[GROUPING($1)],
> SAL_FLAG=[GROUPING($2)])
> LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> Converted Plan should be
> {code:sql}
> LogicalUnion(all=[true])
> LogicalProject(DEPTNO=[$0], JOB=[$1], $f2=[null:INTEGER], EXPR$3=[$2],
> $f4=[0:BIGINT], $f5=[0:BIGINT], $f6=[1:BIGINT])
> LogicalAggregate(group=[{0, 1}], EXPR$3=[SUM($3)])
> LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(DEPTNO=[$0], $f1=[null:VARCHAR(10)], SAL=[$1], EXPR$3=[$2],
> $f4=[0:BIGINT], $f5=[1:BIGINT], $f6=[0:BIGINT])
> LogicalAggregate(group=[{0, 2}], EXPR$3=[SUM($3)])
> LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> I think we can only support the GROUPING function. GROUPING_ID has been
> marked as deprecated in Calcite. The GROUP_ID function is used to mark
> duplicate records. It is difficult to implement in this scenario and can be
> temporarily disabled.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
