Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.22.0
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:
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")); }
The RelNode will generate the wrong Sql:
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'
Attachments
Attachments
Issue Links
- duplicates
-
CALCITE-4220 In SqlToRelConverter, use RelBuilder for creating Aggregate
- Closed
- is duplicated by
-
CALCITE-4664 When group by is same as sub-query, grouping sets are missing
- Closed
- relates to
-
CALCITE-4748 If there are duplicate GROUPING SETS, Calcite should return duplicate rows
- Closed
- links to