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]

Reply via email to