[
https://issues.apache.org/jira/browse/CALCITE-6332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17827752#comment-17827752
]
Julian Hyde commented on CALCITE-6332:
--------------------------------------
There are two algorithms to rewrite distinct aggregates. The older one uses
join. The newer one, introduced in CALCITE-732, uses grouping sets.
The difference is whether you call {{Config.withJoin(true)}}. As far as I know,
the newer one is superior in all cases. The older one may have bugs, or
limitations such as this one - not being able to handle an {{Aggregate}} with
grouping sets.
One solution is to make the join-based rule refuse to fire if it sees grouping
sets. The other is to discourage people from using that rule.
> Optimization CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN produces
> incorrect results for aggregates with groupSets
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6332
> URL: https://issues.apache.org/jira/browse/CALCITE-6332
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.36.0
> Reporter: Mihai Budiu
> Priority: Minor
>
> The optimization rule does not seem to consider the groupSets at all.
> The following two queries produce the same resulting plan:
> {code:sql}
> select count(distinct deptno) as cd, count(*) as c
> from emp
> group by cube(deptno)
> {code}
> {code:sql}
> select count(distinct deptno) as cd, count(*) as c
> from emp
> group by deptno
> {code}
> (Notice that one query has a cube, while the other one doesn't)
> The produced plan is:
> {code}
> LogicalProject(CD=[$1], C=[$2]), id = 196
> LogicalAggregate(group=[{0}], CD=[COUNT($0)], C=[$SUM0($1)]), id = 201
> LogicalAggregate(group=[{0}], C=[COUNT()]), id = 198
> LogicalProject(DEPTNO=[$8]), id = 192
> LogicalTableScan(table=[[schema, EMP]]), id = 163
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
