Skip to content

[BUG] median value not correct #18867

Closed
Closed

Description

@davidlghellin

Describe the bug

The value median is not correct

To Reproduce

in datafusion-cli 51

SELECT median(n) FROM (SELECT 0 AS n UNION ALL SELECT 1  UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9);
Image

Expected behavior

same value of percentile 50
in datafusion-cli 51

SELECT percentile_cont(n,0.5) FROM (SELECT 0 AS n UNION ALL SELECT 1  UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9);
Image

Additional context

testing ibis tests in Sail and add agg funtion
related to lakehq/sail#1072

Activity

  1. davidlghellin commented on Nov 21, 2025

    @davidlghellin
    ContributorAuthor

    I have a question about the intended semantics here: is median supposed to behave as a discrete median (returning a value of the same type as the input, e.g. INT), or more like a continuous percentile (similar to percentile_cont(..., 0.5))?

    For example, in DataFusion today, median for integer inputs effectively takes the midpoint but returns it in the input type, so for values [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] you get 4, while a continuous percentile like percentile_cont(n, 0.5) would return 4.5.

    It would be helpful to clarify whether the goal here is:

    • to keep median as a discrete median that preserves the input type, or
    • to align median with a continuous percentile definition (and possibly promote to Float64).
  2. alamb commented on Nov 21, 2025

    @alamb
    Contributor

    In general, I think DataFusion should follow existing system semantics when possible. I think for aggregates that means:

    1. Follow what postgres does (by default)

    So basically, what does postgres return for median in this case?

    If spark has different semantics, perhaps we can add a new median implementation in the datafusion-spark crate with those semantics

  3. davidlghellin commented on Nov 21, 2025

    @davidlghellin
    ContributorAuthor

    Thanks, and sorry — I didn’t know that DataFusion aims to follow Postgres semantics by default for aggregates.

    I will check how Postgres defines median for these cases. If the behavior I implemented does not match the Postgres semantics, I will close this issue and move the Sail-specific version to the datafusion-spark crate instead, since Spark’s median behaves differently.

    Thanks for the clarification!

  4. alamb commented on Nov 21, 2025

    @alamb
    Contributor

    No worries! We haven't documented the postgres thing -- it was hard to get consensus on the wording

  5. davidlghellin commented on Nov 21, 2025

    @davidlghellin
    ContributorAuthor

    Hi again @alamb
    I couldn't find a built-in median function in PostgreSQL (I am definitely not a Postgres expert), but I did find the two official options:

    • percentile_cont(0.5) WITHIN GROUP (ORDER BY x) — continuous median with interpolation
    • percentile_disc(0.5) WITHIN GROUP (ORDER BY x) — discrete median

    So it seems that PostgreSQL does not define a standalone median() function, and relies on these two percentile variants instead.

    Image

    Let's close this issue for now. I’ll open a PR once the implementation is integrated on the Sail side, based on the ongoing work in the current PR.

    Thanks a lot for the guidance!

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

    bugSomething isn't working

    Type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions