[
https://issues.apache.org/jira/browse/CALCITE-4665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17371900#comment-17371900
]
duan xiong edited comment on CALCITE-4665 at 7/3/21, 3:31 AM:
--------------------------------------------------------------
[~julianhyde] If I don't misunderstand it, Your guess is correct. This may be
a bug. When I try to run
{code:java}
private RelBuilder example4(RelBuilder builder) {
return builder
.scan("EMP")
.aggregate(builder.groupKey(ImmutableBitSet.of(0, 1, 2),
(Iterable<ImmutableBitSet>)
ImmutableList.of(ImmutableBitSet.of(0, 1))),
builder.count(false, "C"),
builder.sum(false, "S", builder.field("SAL"))).filter(
builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("C"),
builder.literal(10)));
}
{code}
The Build generator RelNode is:
{code:java}
LogicalFilter(condition=[>($3, 10)])
LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}]], C=[COUNT()],
S=[SUM($5)])
LogicalTableScan(table=[[scott, EMP]]){code}
The SQL is(*It missing $2 means job column in GROUPING SETS*):
{code:java}
SELECT "EMPNO", "ENAME", "JOB", COUNT(*) AS "C", SUM("SAL") AS "S"
FROM "scott"."EMP"
GROUP BY GROUPING SETS(("EMPNO", "ENAME"))
HAVING COUNT(*) > 10{code}
Because as I know now(tried in pg):
{code:java}
select deptno, job, avg(sal)
from emp
group by ename, grouping sets ((deptno, job ), ());
{code}
should be equals to
{code:java}
select deptno, job, avg(sal)
from emp
group by grouping sets ((ename, deptno, job), (ename));{code}
was (Author: nobigo):
[~julianhyde] If I If I don't misunderstand it, Your guess is correct. This
maybe a bug. When I try to run
{code:java}
private RelBuilder example4(RelBuilder builder) {
return builder
.scan("EMP")
.aggregate(builder.groupKey(ImmutableBitSet.of(0, 1, 2),
(Iterable<ImmutableBitSet>)
ImmutableList.of(ImmutableBitSet.of(0, 1))),
builder.count(false, "C"),
builder.sum(false, "S", builder.field("SAL"))).filter(
builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("C"),
builder.literal(10)));
}
{code}
The Build generator RelNode is:
{code:java}
LogicalFilter(condition=[>($3, 10)])
LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}]], C=[COUNT()],
S=[SUM($5)])
LogicalTableScan(table=[[scott, EMP]]){code}
The Sql is(*It missing $2 means job column in GROUPING SETS*):
{code:java}
SELECT "EMPNO", "ENAME", "JOB", COUNT(*) AS "C", SUM("SAL") AS "S"
FROM "scott"."EMP"
GROUP BY GROUPING SETS(("EMPNO", "ENAME"))
HAVING COUNT(*) > 10{code}
The sql is not corrent. this right sql should be:
{code:java}
SELECT "EMPNO", "ENAME", "JOB", COUNT(*) AS "C", SUM("SAL") AS "S"
FROM "scott"."EMP"
GROUP BY "EMPNO", "ENAME", "JOB"
HAVING COUNT(*) > 10
{code}
Because as I konw now(tried in pg):
{code:java}
select deptno, job, avg(sal)
from emp
group by ename, grouping sets ((deptno, job ), ());
{code}
should be equals to
{code:java}
select deptno, job, avg(sal)
from emp
group by grouping sets ((deptno, job, ename), (ename));{code}
> 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)
