PostgreSQL compatibility: round(float8) should match PostgreSQL half-tie behavior #22269

Activity

1. Dandandan

   added

   bugSomething isn't workingSomething isn't working

   good first issueGood for newcomersGood for newcomers

   on May 16, 2026
2. tarekakrout2021 commented on May 16, 2026

   

   tarekakrout2021

   on May 16, 2026

   More actions

   take
3. github-actions

   assigned

   tarekakrout2021

   on May 16, 2026
4. toby-bridges commented on May 18, 2026

   

   toby-bridges

   on May 18, 2026

   More actions

   I’m taking a look at this and, if it stays scoped, I plan to submit a small compatibility fix with targeted regression coverage.
5. kumarUjjawal commented on May 18, 2026

   

   kumarUjjawal

   on May 18, 2026

   Contributor

   More actions

   duckdb

   memory D SELECT round(2.5::float8);
   ┌──────────────────────────────┐
   │ round(CAST(2.5 AS "DOUBLE")) │
   │            double            │
   ├──────────────────────────────┤
   │                          3.0 │
   └──────────────────────────────┘
   

   should match PG half-tie behavior, DuckDB uses half-away-from-zero uniformly across all numeric types. PG is split: round(numeric) = half-away-from-zero, round(double) = half-to-even (delegated to libc rint(), platform-dependent). DuckDB explicitly exposes the PG-style banker's rounding as the separate round_even() / roundbankers() functions, suggesting round() was deliberately chosen as non-banker's.
6. kumarUjjawal commented on May 18, 2026

   

   kumarUjjawal

   on May 18, 2026

   Contributor

   More actions

   I’m taking a look at this and, if it stays scoped, I plan to submit a small compatibility fix with targeted regression coverage.

   I think @tarekakrout2021 is already working on this, please collaborate.
7. tarekakrout2021 commented on May 18, 2026

   

   tarekakrout2021

   on May 18, 2026

   Last edited by tarekakrout2021

   More actions

   Changing the rounding to use the round_even breaks other tests, namely: test_round_f32 and test_round_f64.

   For :
   select round(125.2345,3);
   round_even would output 125.234, however postgresql outputs 125.235.

   The reason is that postgres does not allow rounding with precision on floats :

   postgres=# SELECT round(2.5::float8, 3);
   ERROR:  function round(double precision, integer) does not exist
   LINE 1: SELECT round(2.5::float8, 3);
                  ^
   HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
   
   

   This query:
   select round(125.2345,3);
   will use the numeric type and not the float type.

   postgres=# SELECT pg_typeof(round(125.2345, 3));
    pg_typeof 
   -----------
    numeric
   (1 row)
   
   

   DuckDB also confirms this:

   memory=# SELECT round(125.2345, 3);
   ┌────────────────────┐
   │ round(125.2345, 3) │
   │    numeric(7,3)    │
   ├────────────────────┤
   │            125.235 │
   └────────────────────┘
   
   memory=# SELECT round_even(125.2345, 3);
   ┌─────────────────────────┐
   │ round_even(125.2345, 3) │
   │         double          │
   ├─────────────────────────┤
   │                 125.234 │
   └─────────────────────────┘
   
   

   Could you please clarify if the tests should be changed, such that we always use round_even, or should a round_even function be exposed like duckdb ?
   @kumarUjjawal
8. kumarUjjawal commented on May 18, 2026

   

   kumarUjjawal

   on May 18, 2026

   Contributor

   More actions

   I think this issue should stay scoped to the one-arg float case. PostgreSQL’s two-arg form is a different case, so round(125.2345, 3) should not drive this fix. For DataFusion, the safer change is: keep decimal rounding as-is, keep float rounding with explicit scale as-is for now, and change only the one-arg float overload to ties-to-even, with targeted regression tests for 2.5, 3.5, -2.5, and -3.5. If we want stricter PostgreSQL behavior for round(double precision, int) or a separate explicit banker’s function, that should be a another issue.
9. Jefffrey commented on May 27, 2026

   

   Jefffrey

   on May 27, 2026

   Contributor

   More actions

   (I haven't done research on my own, but judging from the discussion here) to me what postgres does seems a bit confusing in having different rounding methods based on if double or numeric (maybe they have some reasoning for this). So I'd be in favour of keeping what DataFusion does right now (which aligns with DuckDB), to ensure we have consistent rounding behaviour.

   (We could also get some more datapoints from Spark and Clickhouse)
10. coding-by-sea commented on Nov 4, 2026

    

    coding-by-sea

    on Nov 4, 2026

    More actions

    @tarekakrout2021 may I ask whether you want to continue working on this? If not, I want to take it.
11. kumarUjjawal commented on Nov 4, 2026

    

    kumarUjjawal

    on Nov 4, 2026

    Contributor

    More actions

    @tarekakrout2021 may I ask whether you want to continue working on this? If not, I want to take it.

    As Jeffrey said in the comment above we should keep the current behaviour as is.