[
https://issues.apache.org/jira/browse/CALCITE-7362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18051912#comment-18051912
]
Julian Hyde commented on CALCITE-7362:
--------------------------------------
{quote}The above analysis assumes that there is no GROUP BY clause so
essentially we are dealing with grand totals.{quote}
This is an important caveat. The summary/description should state it explicitly.
I verified that the rules are the same if there is no {{GROUP BY}} (or has
{{GROUP BY ()}}) but may be different if there is a {{GROUP BY}} and the filter
removes all rows from the group:
{noformat}
0: jdbc:hsqldb:res:scott> select sum(sal) filter (where sal < 0) from emp;
+------+
| C1 |
+------+
| null |
+------+
1 row selected (0.009 seconds)
0: jdbc:hsqldb:res:scott> select sum(sal) from emp where sal < 0;
+------+
| C1 |
+------+
| null |
+------+
1 row selected (0.001 seconds)
0: jdbc:hsqldb:res:scott> select deptno, sum(sal) filter (where sal < 0) from
emp group by deptno;
+--------+------+
| DEPTNO | C2 |
+--------+------+
| 20 | null |
| 30 | null |
| 10 | null |
+--------+------+
3 rows selected (0.01 seconds)
0: jdbc:hsqldb:res:scott> select deptno, sum(sal) from emp where sal < 0 group
by deptno;
+--------+----+
| DEPTNO | C2 |
+--------+----+
No rows selected (0.002 seconds)
{noformat}
It is true that common aggregate functions ignore null values. But
{{ARRAY_AGG}} does not, and user-defined aggregate functions might not. So, in
my opinion, filtering values by converting them to null is a hack. (Albeit a
hack that people became comfortable with because standard SQL gave them no
alternative.)
> Add rule to transform WHERE clauses into conditional aggregates
> ---------------------------------------------------------------
>
> Key: CALCITE-7362
> URL: https://issues.apache.org/jira/browse/CALCITE-7362
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
>
> Add a rule to transform aggregate queries with filtering based on WHERE
> clauses to conditional aggregates (CASE WHEN) without a WHERE clause.
> The proposed transformation using the SQL representation is shown below:
> +Before+
> {code:sql}
> select sum(ss_net_paid_inc_tax)
> from store_sales
> where ss_quantity < 20
> {code}
> +After+
> {code:sql}
> select sum(case when ss_quantity < 20 then ss_net_paid_inc_tax else null)
> from store_sales
> {code}
> The queries are equivalent and the transformation is valid for all aggregate
> functions that [skip NULL input
> values|https://github.com/apache/calcite/blob/c0d5a0832808fabfa32dea744415c0f46c516bce/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L233].
> The main motivation for introducing this rule is view-based rewriting where
> it is quite common to have multiple conditional aggregates in the same
> (materialized) view definition for precomputing and reusing expensive
> aggregations.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
