[CALCITE-1172] Add rule to flatten two Aggregate operators into one

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.20.0
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 28/Mar/16 03:47
• Updated: 04/Aug/20 20:14
• Resolved: 05/Apr/19 03:12

Description

If there is an Aggregate on top of an Aggregate, and the top aggregate's group key is a subset of the lower aggregate's group key, and the aggregates are expansions of rollups, then a rule would convert into a single aggregate.

For example, SUM of SUM becomes SUM; SUM of COUNT becomes COUNT; MAX of MAX becomes MAX; MIN of MIN becomes MIN. AVG of AVG would not match, nor would COUNT of COUNT.

The rule would convert

Aggregate({x}, SUM(c1), MIN(c2), MAX(c3))
  Aggregate({x, y}, SUM(c1), MIN(c2), MAX(c3))
    R

into

Aggregate({x}, SUM(c1), MIN(c2), MAX(c3))
  R

The rule would not work if there was anything between the two Aggregate operators.

The implementation would use SqlSplittableAggFunction to deduce rollups, and in fact would be practically the converse of AggregateExchangeTransposeRule proposed in CALCITE-907.

Issue Links

• is depended upon by: PHOENIX-2794 Flatten nested aggregate queries when possible
• is related to: CALCITE-907 Add AggregateExchangeTransposeRule
• is related to: CALCITE-2949 Double aggregate generated for in subquery with distinct
• is related to: CALCITE-4154 Add a rule to merge a Project onto an Aggregate
• relates to: CALCITE-3957 AggregateMergeRule should merge SUM0 into COUNT even if GROUP BY is empty
• links to: GitHub Pull Request #1087