Yibo Dong created SPARK-60124:
---------------------------------
Summary: `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
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]
