[
https://issues.apache.org/jira/browse/SPARK-60124?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-60124:
-----------------------------------
Labels: pull-request-available sql (was: sql)
> `LISTAGG(DISTINCT ...)` with `FILTER` fails during planning
> -----------------------------------------------------------
>
> Key: SPARK-60124
> URL: https://issues.apache.org/jira/browse/SPARK-60124
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Environment: Spark: 5.0.0-SNAPSHOT
> Commit: 38422aefaf599ffb4cfa579d98898d95b1f02081
> Java: OpenJDK 17.0.17
> OS: macOS 15.7.4
> Reporter: Yibo Dong
> Priority: Major
> Labels: pull-request-available, sql
>
> h2. What happened
> A valid query combining {{LISTAGG}}, {{DISTINCT}}, {{WITHIN GROUP (ORDER BY
> ...)}}, and {{FILTER}} fails with a {{ClassCastException}} during query
> planning.
> Each feature works in isolation, but their combination causes an internal
> error in {{RewriteDistinctAggregates}}.
> h2. How to reproduce
> Run the following SQL using {{spark-sql}}:
> {code:sql}
> SELECT listagg(DISTINCT s, '|') WITHIN GROUP (ORDER BY s)
> FILTER (WHERE keep = 1) AS rendered
> FROM VALUES
> ('b', 1), ('a', 1), ('a', 1), (NULL, 1), ('c', 0)
> AS t(s, keep);
> {code}
> Save the SQL as {{repro.sql}} and run:
> {code:bash}
> spark-sql --master 'local[2]'
> --conf spark.ui.enabled=false
> -f repro.sql
> {code}
> h2. Expected result
> The filter retains {{b}}, {{a}}, {{a}}, and {{NULL}}. After removing
> duplicates, ignoring NULL values, and sorting in ascending order, the result
> should be:
> {code:text}
> rendered
> --------
> a|b
> {code}
> h2. Actual result
> The query fails during planning with a {{ClassCastException}}:
> {code:text}
> java.lang.ClassCastException:
> class org.apache.spark.sql.catalyst.expressions.AttributeReference
> cannot be cast to class org.apache.spark.sql.catalyst.expressions.SortOrder
> at
> org.apache.spark.sql.catalyst.expressions.aggregate.ListAgg.$anonfun$withNewChildrenInternal$1(collect.scala:1051)
> at
> org.apache.spark.sql.catalyst.optimizer.RewriteDistinctAggregates.patchAggregateFunctionChildren$1(RewriteDistinctAggregates.scala:264)
> at
> org.apache.spark.sql.catalyst.optimizer.RewriteDistinctAggregates$.rewrite(RewriteDistinctAggregates.scala:308)
> {code}
> h2. Additional information
> Control queries confirmed that ordered {{LISTAGG}}, {{LISTAGG(DISTINCT
> ...)}}, and filtered ordered {{LISTAGG}} execute correctly. Only the
> combination of {{DISTINCT}}, {{FILTER}}, and {{WITHIN GROUP}} fails.
> Excluding {{RewriteDistinctAggregates}} did not prevent the failure. The
> original query fails before physical execution. A semantically equivalent
> query that filters and deduplicates the input before applying {{LISTAGG}}
> returns the expected result, {{a|b}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
