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]

Reply via email to