[CALCITE-2132] Unify rewrites for aggregate functions and windowed aggregate functions

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 10/Jan/18 20:34
• Updated: 22/Feb/19 01:20

Description

Similar rewrites apply to aggregate functions and windowed aggregate functions. For example, in the following queries AVG(y) can be expanded to SUM(y) / COUNT(y) (or something similar to account for empty inputs) and COUNT(z) can be changed to COUNT(*) (if z does not allow null values):

SELECT x, AVG(y), COUNT(z)
FROM t
GROUP BY x;

SELECT AVG(y) OVER w, COUNT(z) OVER w
FROM t
WINDOW w AS (ORDER BY x RANGE 3 PRECEDING)

But the rewrites are spread across several places: AggregateReduceFunctionsRule, StandardConvertletTable.AvgVarianceConvertlet, RexBuilder.addAggCall, and (when CALCITE-2126 is implemented) RexBuilder.makeOver.

This case would move the logic into one place that can be used by rewrites that apply to both kinds of functions.

One important distinction is whether the aggregate function can receive the empty set. For aggregate functions (i.e. with GROUP BY) the set is never empty (except for GROUP BY ()); for windowed aggregate functions the set may be empty (except if the window includes the current row, e.g. ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING or ROWS 2 PRECEDING or RANGE 5 PRECEDING). If the set may be empty, many aggregate functions become nullable.

Issue Links

• relates to: CALCITE-2047 AggregateReduceFunctionsRule throws "Type mismatch" AssertionError
• relates to: CALCITE-2126 Optimization of COUNT (1) in OVER window