[CALCITE-4665] Allow Aggregate.groupSet to contain columns not in any of the groupSets

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.22.0
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: Jiajun Xie
• Created: 24/Jun/21 00:21
• Updated: 20/Oct/21 05:24
• Resolved: 24/Aug/21 22:27

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'

 

Issue Links

• duplicates: CALCITE-4220 In SqlToRelConverter, use RelBuilder for creating Aggregate
• is duplicated by: CALCITE-4664 When group by is same as sub-query, grouping sets are missing
• relates to: CALCITE-4748 If there are duplicate GROUPING SETS, Calcite should return duplicate rows
• links to: GitHub Pull Request #2445
• links to: GitHub Pull Request #2458