[
https://issues.apache.org/jira/browse/CALCITE-4665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17371631#comment-17371631
]
Julian Hyde commented on CALCITE-4665:
--------------------------------------
I have re-opened this issue, because we need to talk about what is the correct
behavior. When we've decided correct behavior, let's add a test. Otherwise the
behavior will change without notice.
First, let's look at the SQL:
{code}
SELECT x, y, z
FROM t
GROUP BY x, y, z GROUPING SETS (x, y)
{code}
is not valid SQL. You do not specify GROUP BY and GROUPING SETS separately. The
following is valid SQL:
{code}
SELECT x, y, z
FROM t
GROUP BY x, y, z, GROUPING SETS (x, y)
{code}
(note the extra comma - making x, y, z effectively 3 singleton grouping sets)
and the following is also valid SQL:
{code}
SELECT x, y, z
FROM t
GROUP BY GROUPING SETS ((x, y, z), (x, y))
{code}
Because you cannot specify {{GROUPING SETS}} separately from {{GROUP BY}}, you
see that the {{groupKey}} in Calcite's {{Aggregate}} operator is the union of
the bits in the {{groupKeys}}.
If there are bits in {{groupKey}} that do not occur in any of the
{{groupKeys}}, is the {{Aggregate}} valid? That's what's at issue here.
Can someone make the case that {{Aggregate}} should not remove unused bits?
> When group by are same as sub-query, grouping sets are missing
> --------------------------------------------------------------
>
> Key: CALCITE-4665
> URL: https://issues.apache.org/jira/browse/CALCITE-4665
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.22.0
> Reporter: xiejiajun
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.28.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> UT:
> {code:java}
> builder.scan("EMP")
> .aggregate(builder.groupKey(0, 1, 7),
> builder.aggregateCall(SqlStdOperatorTable.COUNT,
> builder.field("JOB")).as("job_num"))
> .aggregate(
> builder.groupKey(ImmutableBitSet.of(0, 1, 2),
> (Iterable<ImmutableBitSet>)
> ImmutableList.of(ImmutableBitSet.of(0, 1))))
> // GROUP BY 0,1,2 GROUPING SETS((0, 1))
> .build();
> {code}
> Before I fixed it, you can see groupings set are missing because
> LogicalProject.
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
> LogicalAggregate(group=[{0, 1, 7}], job_num=[COUNT($2)])
> LogicalTableScan(table=[[scott, EMP]]){code}
> After I fixed it, groupings set will be saved.
> {code:java}
> LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}]])
> LogicalAggregate(group=[{0, 1, 7}], job_num=[COUNT($2)])
> LogicalTableScan(table=[[scott, EMP]]{code}
> Although the user will not write such SQL directly, it does happen after
> the logic is complicated, and the user will be confused about the wrong data.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
