[
https://issues.apache.org/jira/browse/CALCITE-4665?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4665:
---------------------------------
Description:
Allow {{Aggregate.groupSet}} to contain columns not in any of the {{groupSets}}.
Rationale: even though this is not valid SQL (except in Hive SQL, which has a
strange GROUPING SETS syntax) this pattern crops up when applying rules (e.g.
pushing a Filter into an Aggregate with grouping sets) and it is best to handle
it explicitly.
When group key is a strict superset of the union of the groupKeys,
RelToSqlConverter currently generates the wrong SQL.
The UT:
{code:java}
private RelBuilder example5(RelBuilder builder) {
return builder
.scan("EMP")
.aggregate(builder.groupKey(ImmutableBitSet.of(0, 1 ,2),
(Iterable<ImmutableBitSet>)
ImmutableList.of(ImmutableBitSet.of(0,1),ImmutableBitSet.of(0))),
builder.count(false, "C"),
builder.sum(false, "S", builder.field("SAL"))).filter(
builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("C"),
builder.literal(10)))
.filter(builder.call(SqlStdOperatorTable.EQUALS,builder.field("JOB"),builder.literal("DEVELOP")))
.project(builder.field("JOB"));
}
{code}
The RelNode will generate the wrong Sql:
{code:java}
SELECT "JOB"
FROM (SELECT "EMPNO", "ENAME", "JOB", COUNT(*) AS "C", SUM("SAL") AS "S"
FROM "scott"."EMP"
GROUP BY GROUPING SETS(("EMPNO", "ENAME"), "EMPNO")
HAVING COUNT(*) > 10) AS "t0"
WHERE "JOB" = 'DEVELOP'
{code}
was:
The UT:
{code:java}
private RelBuilder example5(RelBuilder builder) {
return builder
.scan("EMP")
.aggregate(builder.groupKey(ImmutableBitSet.of(0, 1 ,2),
(Iterable<ImmutableBitSet>)
ImmutableList.of(ImmutableBitSet.of(0,1),ImmutableBitSet.of(0))),
builder.count(false, "C"),
builder.sum(false, "S", builder.field("SAL"))).filter(
builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("C"),
builder.literal(10)))
.filter(builder.call(SqlStdOperatorTable.EQUALS,builder.field("JOB"),builder.literal("DEVELOP")))
.project(builder.field("JOB"));
}
{code}
The RelNode will generate the wrong Sql:
{code:java}
SELECT "JOB"
FROM (SELECT "EMPNO", "ENAME", "JOB", COUNT(*) AS "C", SUM("SAL") AS "S"
FROM "scott"."EMP"
GROUP BY GROUPING SETS(("EMPNO", "ENAME"), "EMPNO")
HAVING COUNT(*) > 10) AS "t0"
WHERE "JOB" = 'DEVELOP'
{code}
> Allow Aggregate.groupSet to contain columns not in any of the groupSets
> -----------------------------------------------------------------------
>
> 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
> Assignee: duan xiong
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.28.0
>
> Attachments: 捕获.PNG
>
> Time Spent: 2.5h
> Remaining Estimate: 0h
>
> Allow {{Aggregate.groupSet}} to contain columns not in any of the
> {{groupSets}}.
> Rationale: even though this is not valid SQL (except in Hive SQL, which has a
> strange GROUPING SETS syntax) this pattern crops up when applying rules (e.g.
> pushing a Filter into an Aggregate with grouping sets) and it is best to
> handle it explicitly.
> When group key is a strict superset of the union of the groupKeys,
> RelToSqlConverter currently generates the wrong SQL.
> The UT:
> {code:java}
> private RelBuilder example5(RelBuilder builder) {
> return builder
> .scan("EMP")
> .aggregate(builder.groupKey(ImmutableBitSet.of(0, 1 ,2),
> (Iterable<ImmutableBitSet>)
>
> ImmutableList.of(ImmutableBitSet.of(0,1),ImmutableBitSet.of(0))),
> builder.count(false, "C"),
> builder.sum(false, "S", builder.field("SAL"))).filter(
> builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("C"),
> builder.literal(10)))
>
> .filter(builder.call(SqlStdOperatorTable.EQUALS,builder.field("JOB"),builder.literal("DEVELOP")))
> .project(builder.field("JOB"));
> }
> {code}
> The RelNode will generate the wrong Sql:
> {code:java}
> SELECT "JOB"
> FROM (SELECT "EMPNO", "ENAME", "JOB", COUNT(*) AS "C", SUM("SAL") AS "S"
> FROM "scott"."EMP"
> GROUP BY GROUPING SETS(("EMPNO", "ENAME"), "EMPNO")
> HAVING COUNT(*) > 10) AS "t0"
> WHERE "JOB" = 'DEVELOP'
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
