Description
Part of #173 and apache/datafusion-comet#7300.
What the function does
listagg(expr[, delimiter]) [WITHIN GROUP (ORDER BY key [ASC | DESC] [NULLS FIRST | LAST], ...)] is an aggregate
that concatenates the non-NULL values of expr, separated by delimiter. string_agg is an alias of the same
expression. Both were added in Spark 4.0.0 and do not exist in 3.4 or 3.5.
expr must be STRING (any collation, including trim collations) or BINARY. Other types are implicitly
cast to STRING (the first type in the TypeCollection), so listagg(int_col) and listagg(bool_col) work.
delimiter is optional (default NULL), must be foldable, and must be STRING, BINARY or NULL. A NULL
delimiter means "no separator" (empty string / empty byte array). If the delimiter is not NULL it must have
the same type as expr; STRING and BINARY are not cast into each other. The result type equals the type of
expr (so BINARY in gives BINARY out, and the collation of expr is kept). The result is nullable: it is
NULL when there are no non-NULL input values.
Without WITHIN GROUP the order of the concatenated values is the arrival order and is non-deterministic after
a shuffle. With WITHIN GROUP the collected values are sorted by the key(s) at evaluation time. DISTINCT is
supported, but if both DISTINCT and WITHIN GROUP are given, the ordering must be on the aggregated
expression itself (see edge cases).
Examples
SELECT listagg(col) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- abc (order not guaranteed)
SELECT listagg(col) WITHIN GROUP (ORDER BY col DESC) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- cba
SELECT listagg(col) FROM VALUES ('a'), (NULL), ('b') AS tab(col);
-- ab
SELECT listagg(DISTINCT col) FROM VALUES ('a'), ('a'), ('b') AS tab(col);
-- ab
SELECT listagg(col, ', ') FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- a, b, c
SELECT listagg(col) FROM VALUES (NULL), (NULL) AS tab(col);
-- NULL
-- df = VALUES ('a','b'), ('a','c'), ('b','c'), ('b','d'), (NULL,NULL) AS (col1, col2)
SELECT listagg(col2) FROM df WHERE 1 != 1;
-- NULL
SELECT listagg(col1) WITHIN GROUP (ORDER BY col2) FROM df;
-- aabb
SELECT listagg(col1) WITHIN GROUP (ORDER BY col2 DESC, col1 ASC) FROM df;
-- baba
SELECT listagg(DISTINCT col, ',') WITHIN GROUP (ORDER BY col DESC) FROM VALUES (1), (10), (2), (20), (2) AS t(col);
-- 20,10,2,1
SELECT listagg(DISTINCT cast(col as string), ',') WITHIN GROUP (ORDER BY col) FROM VALUES (10), (1), (2), (20), (2) AS t(col);
-- 1,2,10,20
SELECT listagg(DISTINCT col, ',') WITHIN GROUP (ORDER BY col) FROM VALUES (1), (2), (null), (2), (3) AS t(col);
-- 1,2,3
SELECT grp, hex(listagg(DISTINCT col, X'2C') WITHIN GROUP (ORDER BY col))
FROM VALUES (1, X'AA'), (1, X'BB'), (1, X'AA'), (2, X'CC'), (2, X'CC') AS t(grp, col) GROUP BY grp;
-- 1 AA2CBB
-- 2 CC
SELECT listagg(c1) WITHIN GROUP (ORDER BY c1 COLLATE utf8_binary) FROM VALUES ('a'), ('A'), ('b'), ('B') AS t(c1);
-- ABab
SELECT listagg(c1, ', ') FROM VALUES (X'DEAD'), (X'BEEF') AS t(c1);
-- error DATATYPE_MISMATCH.DATA_DIFF_TYPESEdge cases to test
- Empty input and all-NULL groups return
NULL. A group whose values are all empty strings returns'', not
NULL(only NULL inputs are skipped;''values are kept, solistagg(c, ',')over'',''is,). - Single row: result is that value with no delimiter.
delimiter=NULLliteral and omitted delimiter both mean no separator. Non-foldable delimiter is an
analysis errorDATATYPE_MISMATCH.NON_FOLDABLE_INPUT. ASTRINGdelimiter withBINARYinput (or vice versa)
fails withDATATYPE_MISMATCH.DATA_DIFF_TYPES. Unsupported input such asARRAY<STRING>fails with
DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE.BINARYinput: concatenation is byte-wise (ByteArray.concatWS), e.g.listagg(c, X'42')overX'DEAD',
X'BEEF'gives a 5-byte result. Result type staysBINARY.- Non-string input is cast to
STRINGfirst, using Spark's cast-to-string formatting (e.g.DECIMAL(10,2)
1.10, booleanstrue/false, intervalsINTERVAL '1' MONTH, dates,TIMESTAMP_NTZ
2024-01-01 10:00:00). WITHIN GROUP: multiple keys,ASC/DESC,NULLS FIRST/NULLS LAST(defaults: ASC NULLS FIRST, DESC NULLS
LAST). Rows whoseexpris NULL are dropped before sorting even if their key is not NULL. Ties in the key keep
a non-deterministic relative order (listagg(col1) WITHIN GROUP (ORDER BY col2 DESC)ondfabove may be
babaorbbaa). Ordering by a key other thanexprrequires carrying the key values in the state.- The sort uses the key's type ordering, including collation (
ORDER BY c1 COLLATE utf8_lcasegroupsa/A). DISTINCTdeduplication is done by Spark's planner (distinct-aggregate rewrite), not byListAggitself, and
uses collation-aware equality (listagg(DISTINCT c COLLATE utf8_lcase)overa,A,b,Byields two
values;unicode_rtrimtreats'xbc 'and'xbc'as equal).DISTINCT+WITHIN GROUPwith a key different fromexpr, or more than one key, fails with
INVALID_WITHIN_GROUP_EXPRESSION.MISMATCH_WITH_DISTINCT_INPUT(sqlState42K0K): "The function is invoked with
DISTINCT and WITHIN GROUP but expressions and do not match. ...".- 4.2 only:
DISTINCT+WITHIN GROUP (ORDER BY col)whereexpris an implicit cast ofcolto string is
allowed when the cast preserves equality (integral, decimal, date,TIMESTAMP_NTZ, time, intervals, boolean,
binary,UTF8_BINARYstrings). ForDOUBLE,FLOAT,TIMESTAMP(LTZ, DST fall-back) and non-binary
collations it fails withINVALID_WITHIN_GROUP_EXPRESSION.MISMATCH_WITH_DISTINCT_INPUT_UNSAFE_CAST. Ordering is
by the original value, not the string (20,10,2,1, not lexicographic). - Window use:
listagg(...) OVER (...)is allowed;WITHIN GROUPcombined withOVERfails with
INVALID_WINDOW_SPEC_FOR_AGGREGATION_FUNC, andDISTINCTwithOVERfails with
DISTINCT_WINDOW_FUNCTION_UNSUPPORTED. - Multi-byte UTF-8 values and delimiters: concatenation is on bytes, no truncation or normalization.
- Determinism: non-deterministic without
WITHIN GROUP(or with ties); tests should sort or uselen/
regexp_countstyle assertions as Spark's own golden tests do. - Merge path: the partial state is the list of collected values (plus key values when the key is not
expr);
merge appends lists and sorting happens only in the final evaluation, so partial/final splitting does not
change the result beyond arrival order.
Differences across Spark versions
- 3.4, 3.5: function does not exist.
- 4.0, 4.1:
DISTINCT+WITHIN GROUPrequires the key to be semantically equal toexpr; any mismatch
(including an implicit cast of a non-string column) raisesMISMATCH_WITH_DISTINCT_INPUT. - 4.2: adds the internal config
spark.sql.listagg.allowDistinctCastWithOrder.enabled(defaulttrue) and the new
error subclassMISMATCH_WITH_DISTINCT_INPUT_UNSAFE_CAST, allowing equality-preserving implicit casts as
described above. With the config set tofalse, 4.2 behaves like 4.0/4.1.
Implementation notes
- Spark:
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/collect.scala,
classListAgg(extendsCollect,SupportsOrderingWithinGroup). Analysis checks forDISTINCTare in
analysis/CheckAnalysis.scalaandanalysis/resolver/AggregateExpressionResolver.scala. Golden tests:
sql/core/src/test/resources/sql-tests/results/listagg.sql.outandlistagg-collations.sql.out. - DataFusion core has
string_aggindatafusion/functions-aggregate/src/string_agg.rs. Differences: it
requires the delimiter argument (no 1-arg form), accepts onlyUtf8/LargeUtf8/Utf8View(noBinary),
always returnsLargeUtf8rather than the input type, has nolistaggname, and ordering is expressed as
string_agg(x, ',' ORDER BY y)rather thanWITHIN GROUP. Its accumulators andDISTINCT/ ordered handling
may be reusable, but a Spark version needs its own signature, return type and binary path. - The kernel in datafusion-spark mainly needs: 1- and 2-arg forms,
NULLdelimiter as empty, Binary support,
return type equal to input type, and ordering via DataFusion's ordered-aggregate (ORDER BY) machinery.
Collation and theDISTINCT+WITHIN GROUPanalysis errors are planner concerns for the caller. - Module:
crates/spark/src/function/aggregate/(next tocollect.rs, which implementscollect_list/
collect_setand may share state handling). - Tests: add
crates/sqllogictest/test_files/aggregate/listagg.sltfollowing existing files, and document the
UDAF with#[user_doc].
This issue was generated by an LLM (Claude) from the Apache Spark source code and tests. Examples come from Spark's ExpressionDescription examples, test suites and .sql.out golden files, but please verify them against Spark before relying on them.
Activity
andygrove commentedon Nov 15, 2026
Note
An LLM (Claude Code) wrote this comment. Check the details before you rely on them.
Apache DataFusion Comet already has a native, Spark-compatible implementation of this, which can be ported here along with its tests. Comet source (at
3878424d9):agg_funcs/list_agg.rs(This was tracked in #171, now closed as a duplicate.)