Repository navigation
Description
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)andmode(col, false): any of the tied values may be returned. Spark picks the first maximum in
OpenHashMapiteration 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
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) x3gives10). deterministicargument errors (analysis time):mode(col, 'true')givesUNEXPECTED_INPUT_TYPE;
mode(col, null)givesDATATYPE_MISMATCH.UNEXPECTED_NULLfordeterministic; a column such as
mode(col, b)givesNON_FOLDABLE_ARGUMENT. More than two arguments is a wrong-number-of-arguments error.mode(DISTINCT x)(with or withoutWITHIN GROUP) fails withINVALID_WITHIN_GROUP_EXPRESSION.DISTINCT_UNSUPPORTED
in 4.0+.mode()withoutWITHIN GROUPfails withINVALID_WITHIN_GROUP_EXPRESSION.WITHIN_GROUP_MISSING;
mode(salary) WITHIN GROUP (ORDER BY salary)fails withINVALID_WITHIN_GROUP_EXPRESSION.WRONG_NUM_ORDERINGS
(expectedNum0). These are planner checks; the kernel only needs the "reverse" flag.NULLS FIRST/NULLS LASTin theWITHIN GROUPclause have no effect because NULLs are ignored.- Floating point: in 4.2,
-0.0and0.0are 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.0asDOUBLEgives0.0, same forFLOAT,ARRAY<DOUBLE>([0.0]) and
named_struct('a', v). Before 4.2 these keys are distinct, so the same input gives9.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 inmode.sql.out. - Large counts: counts are
Long.
Differences across Spark versions
- 3.4, 3.5: only
mode(col); nodeterministicargument and noWITHIN GROUPform. Ties are always arbitrary.
DISTINCTis not specifically rejected. No collation handling. - 4.0: adds
mode(col, deterministic),mode() WITHIN GROUP (ORDER BY col [DESC])(viaModeBuilder),
collation-aware grouping and theINVALID_WITHIN_GROUP_EXPRESSION.*errors. - 4.1: same as 4.0.
- 4.2: adds
ModeKeyNormalizer, so-0.0/0.0and 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;PandasModethere 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
modeaggregate, so this has to be written from scratch. AGroupsAccumulatorkeyed 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. Areverse/ descending flag covers theWITHIN GROUP ... DESCform; the caller maps
WITHIN GROUPto it. - Normalize
-0.0to0.0and canonicalize NaN before hashing to match 4.2. - Module:
crates/spark/src/function/aggregate/(newmode.rs). - Tests: add
crates/sqllogictest/test_files/aggregate/mode.sltfollowing 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.
Activity
andygrove commentedon Nov 15, 2026
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):agg_funcs/mode.rs(This was tracked in #170, now closed as a duplicate.)