No CAST to a Floating Point Type Works the Same on All Four SQL Engines

CAST(price AS DOUBLE PRECISION) is standard SQL and runs on SQLite, PostgreSQL and MySQL. MariaDB refuses it, PostgreSQL refuses DOUBLE, and FLOAT runs everywhere with fewer digits on MySQL and MariaDB.

No floating point type name works in CAST on all four engines with the same meaning. MariaDB takes DOUBLE and FLOAT in CAST but not DOUBLE PRECISION or REAL, and PostgreSQL has no type called DOUBLE. FLOAT runs everywhere, but it means an 8-byte float on SQLite and PostgreSQL and a 4-byte one on MySQL and MariaDB, so CAST(0.1 AS FLOAT) * 3 is 0.30000000000000004 on the first two and 0.30000000447034836 on the others.

What Each Engine Answered

The example

SELECT CAST(0.1 AS FLOAT) * 3;

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 4 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(0.1 * 3, 4);

There is no portable CAST to a float. When a fraction is all that’s needed, multiply by 1.0 (see Integer division) and round the result to the digits you need.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:1:8: warning: CAST(x AS FLOAT) is an 8-byte float on SQLite and PostgreSQL and a 4-byte one on MySQL and MariaDB, so the digits differ. [cast-to-float]
    CAST(0.1 AS FLOAT)
    There is no portable CAST to a float. When a fraction is all that's needed, multiply by 1.0 (see integer-division) and round the result to the digits you need.

1 problem: 1 warning.

baresql rules cast-to-float prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.