[
https://issues.apache.org/jira/browse/CALCITE-7116?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18011956#comment-18011956
]
Julian Hyde edited comment on CALCITE-7116 at 8/4/25 8:35 PM:
--------------------------------------------------------------
Previously we would rely on {{SqlToRelConverter}} handling certain constructs
(e.g. correlation), but we are moving towards a philosophy where any construct
can exist in {{RelNode}}-land, and then we can remove it via rewrite rules.
At a minimum, the rule should refuse to fire if {{GROUP_ID}} is present. You
mention that {{SqlToRelConverter}} deals with it, and it also seems that
{{RelBuilder.aggregate}} deals with it via
{{rewriteAggregateWithDuplicateGroupSets}} (added in CALCITE-4748), but I am
not confident in saying that {{GROUP_ID}} will never exist.
Note that {{rewriteAggregateWithDuplicateGroupSets}} is doing a similar job to
your rule. Maybe you can exploit that.
was (Author: julianhyde):
Previously we would rely on {{SqlToRelConverter}} handling certain constructs
(e.g. correlation), but we are moving towards a philosophy where any construct
can exist in {{RelNode}}-land, and then we can remove it via rewrite rules.
At a minimum, the rule should refuse to fire if {{GROUP_ID}} is present. You
mention that {{SqlToRelConverter}} deals with it, and it also seems that
{{RelBuilder.aggregate}} deals with it via
{{rewriteAggregateWithDuplicateGroupSets}}, but I am not confident in saying
that {{GROUP_ID}} will never exist.
Note that {{rewriteAggregateWithDuplicateGroupSets}} is doing a similar job to
your rule. Maybe you can exploit that.
> 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)
