[
https://issues.apache.org/jira/browse/CALCITE-4665?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiejiajun updated CALCITE-4665:
-------------------------------
Description:
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}
was:
{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 fix:
{code:java}
LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
LogicalAggregate(group=[{0, 1, 7}], job_num=[COUNT($2)])
LogicalTableScan(table=[[scott, EMP]])
{code}
After fix:
{code:java}
LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}]])
LogicalAggregate(group=[{0, 1, 7}], job_num=[COUNT($2)])
LogicalTableScan(table=[[scott, EMP]]){code}
I use calcite For example:
{code:java}
SELECT
t.ID, t.NAME, nvl(t.AGE, 0) T_AGE
FROM
(SELECT u.ID, u.NAME, u.AGE
FROM USERS u
GROUP BY u.ID, u.NAME, u.AGE
) t
GROUP BY t.ID, t.NAME, t.AGE
GROUPING SETS(
(t.ID, t.NAME),
(t.ID, t.NAME, t.AGE)
)
{code}
will be
{code:java}
SELECT u.ID, u.NAME, u.AGE
FROM USERS u
GROUP BY u.ID, u.NAME, u.AGE
{code}
groupings set are missing
> 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
> Reporter: xiejiajun
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> 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}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
