[
https://issues.apache.org/jira/browse/SPARK-56898?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Xu updated SPARK-56898:
-----------------------------
Description:
*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.
was:
*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.
*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:
COUNT(DISTINCT IF(cond, col, NULL)) → COUNT(DISTINCT col) FILTER (WHERE
cond)
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.
> 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.2.0
> Reporter: James Xu
> Priority: Major
> Labels: pull-request-available
>
> *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: [email protected]
For additional commands, e-mail: [email protected]
