andygrove opened a new issue, #185:
URL: https://github.com/apache/datafusion-spark/issues/185
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
```sql
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 <funcArg> and <orderingExpr> 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._
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]