[
https://issues.apache.org/jira/browse/CALCITE-6332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17827872#comment-17827872
]
Abhishek Agarwal commented on CALCITE-6332:
-------------------------------------------
FWIW, the new rule doesn't produce a correct plan either when there is a
distinct aggregator and grouping set involved. E.g. for the test case below
{code:java}
@Test void testDistinctNonDistinctAggregatesWithGrouping2() {
final String sql = "SELECT deptno, COUNT(DISTINCT sal)\n"
+ "FROM emp\n"
+ "GROUP BY GROUPING SETS ((deptno), ())";
sql(sql)
.withConformance(SqlConformanceEnum.LENIENT)
.withRule(CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES)
.check();
} {code}
The below plan is produced
{noformat}
LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT($1) FILTER $2])
LogicalProject(DEPTNO=[$0], SAL=[$1], $g_0=[=($2, 0)])
LogicalAggregate(group=[{0, 1}], $g=[GROUPING($0, $1)])
LogicalProject(DEPTNO=[$7], SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {noformat}
IMO, the correct plan should be something like below
{noformat}
LogicalProject($0, $2)
LogicalAggregate(group=[{0, 2}], EXPR$1=[COUNT($1)])
LogicalProject(DEPTNO=[$0], SAL=[$1], $g_0=[=($2, 0)])
LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {1}]], $g=[GROUPING($0)])
LogicalProject(DEPTNO=[$7], SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {noformat}
> 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)
