Skip to content

Implement Spark-compatible listagg / string_agg #185

Open

Description

@andygrove

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_TYPES

Edge 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, so listagg(c, ',') over '', '' is ,).
  • Single row: result is that value with no delimiter.
  • delimiter = NULL literal and omitted delimiter both mean no separator. Non-foldable delimiter is an
    analysis error DATATYPE_MISMATCH.NON_FOLDABLE_INPUT. A STRING delimiter with BINARY input (or vice versa)
    fails with DATATYPE_MISMATCH.DATA_DIFF_TYPES. Unsupported input such as ARRAY<STRING> fails with
    DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE.
  • BINARY input: concatenation is byte-wise (ByteArray.concatWS), e.g. listagg(c, X'42') over X'DEAD',
    X'BEEF' gives a 5-byte result. Result type stays BINARY.
  • Non-string input is cast to STRING first, using Spark's cast-to-string formatting (e.g. DECIMAL(10,2)
    1.10, booleans true/false, intervals INTERVAL '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 whose expr is 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) on df above may be
    baba or bbaa). Ordering by a key other than expr requires carrying the key values in the state.
  • The sort uses the key's type ordering, including collation (ORDER BY c1 COLLATE utf8_lcase groups a/A).
  • DISTINCT deduplication is done by Spark's planner (distinct-aggregate rewrite), not by ListAgg itself, and
    uses collation-aware equality (listagg(DISTINCT c COLLATE utf8_lcase) over a, A, b, B yields two
    values; unicode_rtrim treats 'xbc ' and 'xbc' as equal).
  • DISTINCT + WITHIN GROUP with a key different from expr, or more than one key, fails with
    INVALID_WITHIN_GROUP_EXPRESSION.MISMATCH_WITH_DISTINCT_INPUT (sqlState 42K0K): "The function is invoked with
    DISTINCT and WITHIN GROUP but expressions and do not match. ...".
  • 4.2 only: DISTINCT + WITHIN GROUP (ORDER BY col) where expr is an implicit cast of col to string is
    allowed when the cast preserves equality (integral, decimal, date, TIMESTAMP_NTZ, time, intervals, boolean,
    binary, UTF8_BINARY strings). For DOUBLE, FLOAT, TIMESTAMP (LTZ, DST fall-back) and non-binary
    collations it fails with INVALID_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 GROUP combined with OVER fails with
    INVALID_WINDOW_SPEC_FOR_AGGREGATION_FUNC, and DISTINCT with OVER fails 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 use len /
    regexp_count style 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 GROUP requires the key to be semantically equal to expr; any mismatch
    (including an implicit cast of a non-string column) raises MISMATCH_WITH_DISTINCT_INPUT.
  • 4.2: adds the internal config spark.sql.listagg.allowDistinctCastWithOrder.enabled (default true) and the new
    error subclass MISMATCH_WITH_DISTINCT_INPUT_UNSAFE_CAST, allowing equality-preserving implicit casts as
    described above. With the config set to false, 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,
    class ListAgg (extends Collect, SupportsOrderingWithinGroup). Analysis checks for DISTINCT are in
    analysis/CheckAnalysis.scala and analysis/resolver/AggregateExpressionResolver.scala. Golden tests:
    sql/core/src/test/resources/sql-tests/results/listagg.sql.out and listagg-collations.sql.out.
  • DataFusion core has string_agg in datafusion/functions-aggregate/src/string_agg.rs. Differences: it
    requires the delimiter argument (no 1-arg form), accepts only Utf8/LargeUtf8/Utf8View (no Binary),
    always returns LargeUtf8 rather than the input type, has no listagg name, and ordering is expressed as
    string_agg(x, ',' ORDER BY y) rather than WITHIN GROUP. Its accumulators and DISTINCT / 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, NULL delimiter as empty, Binary support,
    return type equal to input type, and ordering via DataFusion's ordered-aggregate (ORDER BY) machinery.
    Collation and the DISTINCT + WITHIN GROUP analysis errors are planner concerns for the caller.
  • Module: crates/spark/src/function/aggregate/ (next to collect.rs, which implements collect_list /
    collect_set and may share state handling).
  • Tests: add crates/sqllogictest/test_files/aggregate/listagg.slt following 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

  1. andygrove commented on Nov 15, 2026

    @andygrove
    MemberAuthor

    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):

    (This was tracked in #171, now closed as a duplicate.)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions