You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2026/01/07 14:54:00 UTC
[jira] [Updated] (CALCITE-7362) Add rule to transform WHERE clauses into conditional aggregates
[ https://issues.apache.org/jira/browse/CALCITE-7362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Stamatis Zampetakis updated CALCITE-7362:
-----------------------------------------
Description:
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.
was:
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.
> 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)