skip to content

In SQL, why can ROUND(2.5) return 3 on a DECIMAL value but 2 on a FLOAT value?

level: middleimportance: should knowfreq 42%

answer

  1. the operand's type picks the tie rule
  2. exact: away from zero
  3. approximate: often nearest even
  4. many float values are not really ties

basics

~20 s

Exact types such as DECIMAL commonly break a tie away from zero, giving 3, while approximate types often round ties to the nearest even number, giving 2. The float rule varies by engine and platform, so it is not portable.

solid answer

~40 s

`ROUND` follows the operand's type, so the same call can use different tie rules. PostgreSQL documents that `numeric` breaks ties away from zero, while for `double precision` the behaviour is platform dependent and round-to-nearest-even is the most common rule. MySQL shows the same split: `ROUND(2.5)` is 3 and `ROUND(25E-1)` is 2. Floats have a second problem: many decimal fractions are not stored exactly, so 2.675 as a double sits just below the tie and SQLite rounds it to 2.67. For money, get the value into an exact type before `ROUND` runs. If finance requires half-even on an exact type, you have to write it yourself.

code

sql · 4 lines
sql
SELECT round(2.5::numeric)          AS exact_tie,    -- 3
       round(2.5::double precision) AS float_tie,    -- 2 on most machines
       round(-2.5::numeric)         AS exact_neg,    -- -3
       round(2.675, 2)              AS exact_digits; -- 2.68

go deeper

for a junior

Recall that ROUND(2.5) on a decimal value gives 3 and that a float may behave differently. Know that money should reach ROUND in an exact type.

for a middle

Explain the two tie rules using -2.5 and 2.5, tie each to the operand's type, and separate true ties from values that only look like ties because a float stored something nearby.

for a senior

Check the operand's type before arguing about a cent. Pick test values that expose the rule, since 1.5 and 3.5 agree under both rules, and verify the engine rather than quoting a general rule for floats.

for a principal

Decide one tie rule with finance and put it in a shared expression or function, because a rule scattered across reports will drift. Weigh the cost of hand-written half-even against simply matching the engine's exact-type rule.

## What a tie is A **tie** is a value exactly halfway between the two results `ROUND` could return, such as 2.5 between 2 and 3. Every non-tie has one obvious answer, so a rounding function only needs a rule for ties. Two rules matter here: - **Half away from zero:** a tie moves to the value with the larger magnitude. 2.5 becomes 3, and -2.5 becomes -3. - **Half to even**, also called banker's rounding: a tie moves to whichever neighbour is even. 2.5 becomes 2, 3.5 becomes 4, and -2.5 becomes -2. "Round half up" is a third phrasing people use, but it is not what exact types do on a negative value: half up would make -2.5 into -2. ## The rule follows the type The function name is the same, but engines choose the behaviour by the type of the operand. An **exact** type (`DECIMAL`, `NUMERIC`) holds the digits precisely. An **approximate** type (`FLOAT`, `REAL`, `DOUBLE PRECISION`) holds a nearby binary value. PostgreSQL's documentation states the rule and shows it: | `x` | `round(x::numeric)` | `round(x::double precision)` | |---|---|---| | -2.5 | -3 | -2 | | -0.5 | -1 | -0 | | 0.5 | 1 | 0 | | 1.5 | 2 | 2 | | 2.5 | 3 | 2 | | 3.5 | 4 | 4 | Its wording is worth quoting at its true strength: for `double precision`, tie-breaking is platform dependent, and nearest even is "the most common rule". That is not a promise. Other engines add more variety: - **MySQL** gives 3 for `ROUND(2.5)` and 2 for `ROUND(25E-1)`, because the first literal is exact and the second is approximate. - **SQLite** holds 2.5 as a floating-point value and still returns 3.0, so approximate does not always mean half-even. Notice that 1.5 and 3.5 agree under both rules, which is why this goes unnoticed in a quick test. You need 0.5, 2.5 or 4.5 to see a difference. ## Many float values are not ties at all Binary floating point cannot hold most decimal fractions exactly. The literal 2.675, stored as a double, is really 2.67499999999999982..., just below the tie. So the correct rounding to two places is 2.67, whichever tie rule applies, and that is what SQLite returns for `ROUND(2.675, 2)`. An engine that computes rounding differently internally may report 2.68. The value looks like a tie on screen and you cannot predict the result from the printed digits. On PostgreSQL there is a guard rail: `round(v, s)` is only defined for `numeric`, so `round(price, 2)` on a `double precision` value is an error until the value is cast. Other engines accept it and round the approximate value. ## Knowing which type reached ROUND The type is not always the one you would guess from reading the query: - A column has its declared type, whatever the digits look like when printed. - The literal `2.5` is exact on PostgreSQL, MySQL and SQL Server, while `2.5E0` is approximate. - A mixed expression can turn approximate. PostgreSQL's operator rules resolve `numeric` combined with `double precision` to `double precision`, so one float operand early in a calculation changes the type `ROUND` finally receives. ## What this means for a money figure This is a consequence for function results, not a lesson in how to declare a column. If a calculation reaches `ROUND` as a float, the tie rule and the stored value are both outside your control. Follow these steps: 1. Compute in an exact type from the start, and treat a float or a float literal entering the chain as a defect to remove. 2. If the source really is a float, cast it before rounding, `ROUND(CAST(x AS DECIMAL(12,4)), 2)`, and accept that the cast decides which nearby decimal the float becomes. `CAST(ROUND(x, 2) AS DECIMAL(12,4))` runs the two steps the wrong way round. 3. Ask finance which tie rule its books use, since half-even is common in some accounting practices. 4. Test with values that expose the rule: 0.125, 2.5, -2.5 and a value with no exact binary form, such as 2.675. ## Writing half-even yourself `ROUND` on PostgreSQL's `numeric` has no option for half-even, so implement it with a `CASE` on the digit past the rounding position, on an exact type. The follow-up below gives the form. Keep it in one shared place: two reports each carrying their own version is how rounding rules drift apart.

  • How would you write half-even rounding to two places on a PostgreSQL numeric value?
    Test for an exact tie, then choose the even neighbour: `CASE WHEN ABS(x * 100 - TRUNC(x * 100)) = 0.5 AND MOD(TRUNC(x * 100), 2) = 0 THEN TRUNC(x * 100) / 100 ELSE ROUND(x, 2) END`. For 2.665 this gives 2.66, and for 2.675 it gives 2.68. It relies on exact arithmetic, so it is not valid for a float.
  • What does ROUND(-2.5) return on a DECIMAL value in PostgreSQL, and what does that rule out?
    It returns -3. PostgreSQL breaks `numeric` ties away from zero, so the magnitude grows on both sides of zero. That rules out two popular descriptions: round half up, which would give -2, and banker's rounding, which would also give -2. The negative case is the quickest way to tell the rules apart.
  • Why does round(price, 2) fail in PostgreSQL when price is double precision?
    PostgreSQL lists the two-argument `round(v, s)` for `numeric` only, while one-argument `round` also accepts `double precision`. The call has no matching function, so the query is rejected. Cast the value first, `round(price::numeric, 2)`. The cast decides which decimal value the rounding starts from, so review it when the figure has to reconcile.

  • ROUND always moves a .5 upward, so ROUND(-2.5) is -2 on a DECIMAL.
  • SQL's ROUND always uses banker's rounding, whatever the type.
  • FLOAT and DECIMAL round identically because the function name is the same.
  • Every engine rounds float ties to the nearest even number.
  • ROUND(2.675, 2) on a float must return 2.68 because it is written 2.675.
  • Casting to DECIMAL after rounding a float recovers the exact cents.