[
https://issues.apache.org/jira/browse/CALCITE-7116?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18011711#comment-18011711
]
Julian Hyde edited comment on CALCITE-7116 at 8/3/25 6:48 PM:
--------------------------------------------------------------
{quote}The GROUP_ID function is used to mark duplicate records. It is difficult
to implement in this scenario and can be temporarily disabled.
{quote}
I disagree. {{GROUP_ID}} is easy to implement in this scenario. And
furthermore, if you don't do it now, a maintainer will find it difficult to add
later. If a group occurs more than once in the {{GROUPING SETS}} clause - e.g.
{{GROUPING SETS (\(x), (x, y), \(x))}} contains {{\(x)}} twice - then you need
to generate one branch of the {{UNION ALL}} for each, distinguished with
integers 1 and 2.
was (Author: julianhyde):
{quote}The GROUP_ID function is used to mark duplicate records. It is difficult
to implement in this scenario and can be temporarily disabled.
{quote}
I disagree. {{GROUP_ID}} is easy to implement in this scenario. And
furthermore, if you don't do it now, a maintainer will find it difficult to add
later. If a group occurs more than once in the {{GROUPING SETS}} clause - e.g.
{{GROUPING SETS ((x), (x, y), (x))}} contains {{(x)}} twice - then you need to
generate one branch of the {{UNION ALL}} for each, distinguished with integers
1 and 2.
> 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: Zhen Chen
> Priority: Minor
> Labels: pull-request-available
> 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)
