You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Dongjoon Hyun (Jira)" <ji...@apache.org> on 2026/08/03 18:00:00 UTC

[jira] [Updated] (SPARK-56898) Add pre-pass rule to canonicalize COUNT(DISTINCT IF(cond, col, NULL)) into COUNT(DISTINCT col) FILTER

     [ https://issues.apache.org/jira/browse/SPARK-56898?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dongjoon Hyun updated SPARK-56898:
----------------------------------
    Affects Version/s: 4.3.0
                           (was: 4.2.0)

>  Add pre-pass rule to canonicalize COUNT(DISTINCT IF(cond, col, NULL)) into COUNT(DISTINCT col) FILTER
> ------------------------------------------------------------------------------------------------------
>
>                 Key: SPARK-56898
>                 URL: https://issues.apache.org/jira/browse/SPARK-56898
>             Project: Spark
>          Issue Type: Improvement
>          Components: Optimizer
>    Affects Versions: 4.3.0
>            Reporter: James Xu
>            Assignee: James Xu
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 4.3.0
>
>
> *Problem:*
> When a query contains many COUNT(DISTINCT IF(cond_i, col, NULL)) expressions over the same base column, RewriteDistinctAggregates treats each unique IF(...) expression as a distinct group. N conditions → N distinct groups → N× Expand amplification. In production workloads with 25–60 such expressions, this produces multi-terabyte shuffles and hour-long runtimes. Example query pattern:
> {code:java}
> SELECT
>   user_id,
>   COUNT(DISTINCT IF(dt >= '2026-04-16', order_id, NULL)) AS orders_30d,
>   COUNT(DISTINCT IF(dt >= '2026-02-15', order_id, NULL)) AS orders_90d,
>   COUNT(DISTINCT IF(pay_status = 'paid', order_id, NULL)) AS orders_paid,
>   -- ... 50 more expressions
> FROM transactions
> GROUP BY user_id {code}
>  
> *Root cause:*
> RewriteDistinctAggregates groups distinct aggregates by their unfoldable child sets. For COUNT(DISTINCT IF(cond1, col, NULL)), the child set is \{IF(cond1, col, NULL)} — each unique condition forms its own group, even though all conditions apply to the same base column.
>  
> *Solution:*
> Add a pre-pass optimizer rule (before RewriteDistinctAggregates) that canonicalizes:
> {code:java}
>  COUNT(DISTINCT IF(cond, col, NULL))  →  COUNT(DISTINCT col) FILTER (WHERE cond) {code}
> This identity is exact: COUNT DISTINCT ignores NULLs, so nulling rows where !cond is semantically identical to filtering them. After the rewrite, all expressions share the same unfoldable child set \{col}, collapsing to 1 distinct group and 1× Expand regardless of condition count.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org