andygrove opened a new issue, #186:
URL: https://github.com/apache/datafusion-spark/issues/186
Part of #173 and apache/datafusion-comet#7300.
### What the function does
`mode(col[, deterministic])` is an aggregate that returns the most frequent
non-NULL value of `col`. Spark 4.0
also accepts the ordered-set form `mode() WITHIN GROUP (ORDER BY col [ASC |
DESC])`. `col` may be of any type
(`AnyDataType`, including intervals, arrays, structs and maps); the result
has the same type as `col` and is
nullable. NULL inputs are ignored; if there are no rows or all values are
NULL the result is `NULL`. Available
since Spark 3.4.0.
Tie-breaking when several values share the highest count:
- `mode(col)` and `mode(col, false)`: any of the tied values may be
returned. Spark picks the first maximum in
`OpenHashMap` iteration order, which is effectively arbitrary.
- `mode(col, true)`: the smallest tied value (by the type's natural
ordering).
- `mode() WITHIN GROUP (ORDER BY col)` / `ORDER BY col ASC`: the smallest
tied value.
- `mode() WITHIN GROUP (ORDER BY col DESC)`: the largest tied value.
`deterministic` must be a foldable, non-NULL `BOOLEAN`. `DISTINCT` is
rejected. The state is a hash map from
value to `Long` count; merge adds counts, so partial/final aggregation is
exact. Only the tie-break of the
non-deterministic form can depend on the merge order.
### Examples
```sql
SELECT mode(col) FROM VALUES (0), (10), (10) AS tab(col);
-- 10
SELECT mode(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH),
(INTERVAL '10' MONTH) AS tab(col);
-- 0-10
SELECT mode(col) FROM VALUES (0), (10), (10), (null), (null), (null) AS
tab(col);
-- 10
SELECT mode(col, false) FROM VALUES (-10), (0), (10) AS tab(col);
-- 0 (any of -10, 0, 10 would be valid)
SELECT mode(col, true) FROM VALUES (-10), (0), (10) AS tab(col);
-- -10
SELECT mode() WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10), (10), (20),
(20) AS tab(col);
-- 10
SELECT mode() WITHIN GROUP (ORDER BY col DESC) FROM VALUES (0), (10), (10),
(20), (20) AS tab(col);
-- 20
SELECT mode() WITHIN GROUP (ORDER BY col), mode() WITHIN GROUP (ORDER BY col
DESC)
FROM VALUES (null), (null), (null) AS tab(col);
-- NULL NULL
SELECT mode(col, true) FROM VALUES (map(1, 'a')) AS tab(col);
-- {1:"a"}
SELECT mode(col, 'true') FROM VALUES (-10), (0), (10) AS tab(col);
-- error UNEXPECTED_INPUT_TYPE (second parameter requires "BOOLEAN")
```
### Edge cases to test
- Empty input, all-NULL group: `NULL`. Single row: that value.
- NULLs never count, even if NULL is the most frequent "value" (`(0), (10),
(10), (null) x3` gives `10`).
- `deterministic` argument errors (analysis time): `mode(col, 'true')` gives
`UNEXPECTED_INPUT_TYPE`;
`mode(col, null)` gives `DATATYPE_MISMATCH.UNEXPECTED_NULL` for
`deterministic`; a column such as
`mode(col, b)` gives `NON_FOLDABLE_ARGUMENT`. More than two arguments is a
wrong-number-of-arguments error.
- `mode(DISTINCT x)` (with or without `WITHIN GROUP`) fails with
`INVALID_WITHIN_GROUP_EXPRESSION.DISTINCT_UNSUPPORTED`
in 4.0+. `mode()` without `WITHIN GROUP` fails with
`INVALID_WITHIN_GROUP_EXPRESSION.WITHIN_GROUP_MISSING`;
`mode(salary) WITHIN GROUP (ORDER BY salary)` fails with
`INVALID_WITHIN_GROUP_EXPRESSION.WRONG_NUM_ORDERINGS`
(`expectedNum` 0). These are planner checks; the kernel only needs the
"reverse" flag.
- `NULLS FIRST` / `NULLS LAST` in the `WITHIN GROUP` clause have no effect
because NULLs are ignored.
- Floating point: in 4.2, `-0.0` and `0.0` are counted as the same key and
NaNs with different bit patterns are
canonicalized, also inside arrays and structs
(`NormalizeFloatingNumbers`). Spark test: `0.0, 0.0, -0.0, -0.0,
9.0, 9.0, 9.0` as `DOUBLE` gives `0.0`, same for `FLOAT`, `ARRAY<DOUBLE>`
(`[0.0]`) and
`named_struct('a', v)`. Before 4.2 these keys are distinct, so the same
input gives `9.0`.
- Deterministic tie-break ordering for doubles follows Spark's ordering (NaN
is larger than any other value).
- Collations (4.0+): for strings whose collation is not binary-stable (e.g.
`UTF8_LCASE`, `UNICODE_CI`), counts
are combined by collation key at evaluation time, also for strings nested
in arrays, structs and maps. Which
original spelling is returned for a combined group is not specified. Map
keys/values with collated strings
are compared through their collation keys too.
- Intervals, dates, timestamps, decimals: result keeps the input type and
precision/scale.
- Nested types (array/struct/map) as the value, including NULL elements
inside the nested value (these are not
skipped; only a top-level NULL is).
- Window use: `mode() WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY
...)` with and without frames is
supported and tested in `mode.sql.out`.
- Large counts: counts are `Long`.
### Differences across Spark versions
- 3.4, 3.5: only `mode(col)`; no `deterministic` argument and no `WITHIN
GROUP` form. Ties are always arbitrary.
`DISTINCT` is not specifically rejected. No collation handling.
- 4.0: adds `mode(col, deterministic)`, `mode() WITHIN GROUP (ORDER BY col
[DESC])` (via `ModeBuilder`),
collation-aware grouping and the `INVALID_WITHIN_GROUP_EXPRESSION.*`
errors.
- 4.1: same as 4.0.
- 4.2: adds `ModeKeyNormalizer`, so `-0.0`/`0.0` and NaN bit patterns are
merged (SPARK-57329).
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/Mode.scala`
(`Mode`, `ModeBuilder`, `ModeKeyNormalizer`; `PandasMode` there is
internal and out of scope). Golden file:
`sql/core/src/test/resources/sql-tests/results/mode.sql.out`; float
normalization test in
`sql/core/src/test/scala/org/apache/spark/sql/DataFrameAggregateSuite.scala`.
- DataFusion core has no `mode` aggregate, so this has to be written from
scratch. A `GroupsAccumulator` keyed on
the value (for example using row-format keys for nested types) with a
count per key is the natural shape.
The state must be serializable for partial/final aggregation (for example
a list of values plus a list of
counts).
- The non-deterministic form can return the smallest tied value, which is a
valid Spark result and makes tests
stable. A `reverse` / descending flag covers the `WITHIN GROUP ... DESC`
form; the caller maps
`WITHIN GROUP` to it.
- Normalize `-0.0` to `0.0` and canonicalize NaN before hashing to match 4.2.
- Module: `crates/spark/src/function/aggregate/` (new `mode.rs`).
- Tests: add `crates/sqllogictest/test_files/aggregate/mode.slt` following
existing files, and document 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]