Skip to content

Implement Spark-compatible mode #186

Open

Description

@andygrove

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

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.

Activity

  1. andygrove commented on Nov 15, 2026

    @andygrove
    MemberAuthor

    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):

    (This was tracked in #170, now closed as a duplicate.)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions