[
https://issues.apache.org/jira/browse/CALCITE-2132?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-2132:
---------------------------------
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):
{code}
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)
{code}
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.
was:
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):
{code}
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)
{code}
But the rewrites are spread across several places:
{{AggregateReduceFunctionsRule}},
{{StandardConvertletTable.AvgVarianceConvertlet}}, {{RexBuilder.addAggCall}},
and (when CALCITE-2126 is implemented) {{RexBuilder.makeOver}}.
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.
> Unify rewrites for aggregate functions and windowed aggregate functions
> -----------------------------------------------------------------------
>
> Key: CALCITE-2132
> URL: https://issues.apache.org/jira/browse/CALCITE-2132
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
>
> 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):
> {code}
> 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)
> {code}
> 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.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
