ROUND of a Floating Point Half Is 3 on SQLite and 2 on PostgreSQL and MySQL

A price kept as a floating point number rounds to 3 on SQLite for 2.5 and to 2 on PostgreSQL, MySQL and MariaDB. With a DECIMAL column, or the literal 2.5, every engine says 3. Rounding a decimal to digits brings its own surprise on SQLite.

PostgreSQL, MySQL and MariaDB round exact numbers, such as the literal 2.5 or a DECIMAL column, half away from zero. Floating point values they round to the even neighbour, so 2.5 becomes 2 and 3.5 becomes 4, and PostgreSQL has no ROUND(x, digits) for them at all. SQLite has no exact decimal type. It rounds a float half away from zero, 2.5 to 3, and rounds decimals to some digits through the nearest binary float, so ROUND(0.15, 1) is 0.1 there because 0.15 is stored as 0.1499999.

What Each Engine Answered

On a table made with

CREATE TABLE t (x DOUBLE PRECISION);
INSERT INTO t VALUES (2.5), (3.5);

the example

SELECT ROUND(x) FROM t ORDER BY x;

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 3 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

What to Write Instead

This form answers the same on every engine:

SELECT ROUND(CAST(x AS DECIMAL(10, 2))) FROM t ORDER BY x;

To round floats to whole numbers, ROUND(CAST(x AS DECIMAL(10, 2))) gives the same answer on every engine. For money, keep whole cents in an INTEGER column and divide only to show the amount; rounding to digits on SQLite can’t be made exact.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:8: warning: ROUND of the floating point value 2.5 is 3 on SQLite and 2 on PostgreSQL, MySQL and MariaDB, and ROUND(0.15, 1) is 0.1 on SQLite and 0.2 on the others. [round-half]
    ROUND(x)
    To round floats to whole numbers, ROUND(CAST(x AS DECIMAL(10, 2))) gives the same answer on every engine. For money, keep whole cents in an INTEGER column and divide only to show the amount; rounding to digits on SQLite can't be made exact.
example.sql:3:33: info: SQLite, MySQL and MariaDB put NULL first in ascending order; PostgreSQL puts it last. [null-order]
    x
    Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.

2 problems: 1 warning, 1 note.

The example also runs into Where NULL sorts, which has a post of its own.

baresql rules round-half prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.