CAST to INTEGER Fails on MySQL and Rounds Differently on SQLite and PostgreSQL

Turning a price into a whole number with CAST(5.7 AS INTEGER) gives 5 on SQLite and 6 on PostgreSQL and MariaDB. MySQL doesn’t run it at all.

SQLite drops the fraction and PostgreSQL and MariaDB round it. MySQL takes only SIGNED and UNSIGNED as integer types in CAST, and PostgreSQL refuses both. Decimals differ as well: SQLite ignores the scale of CAST(x AS DECIMAL(10, 1)) and keeps every digit, and MySQL and MariaDB read DECIMAL without a precision as DECIMAL(10, 0), a whole number.

SELECT CAST(5.7 AS INTEGER), run on four engines: SQLite 3.45.1 5; PostgreSQL 16.15 6; MySQL 8.0.46 error: You have an error in your SQL syntax ... near 'INTEGER)' at line 1; MariaDB 10.11.14 6.

What Each Engine Answered

The example

SELECT CAST(5.7 AS INTEGER);

gave these answers:

Engine	Answer
SQLite 3.45.1	`5`
PostgreSQL 16.15	`6`
MySQL 8.0.46	error: `You have an error in your SQL syntax ... near 'INTEGER)' at line 1`
MariaDB 10.11.14	`6`

Every Form BareSQL Checks

BareSQL Portable checks 10 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.

Form	SQLite	PostgreSQL	MySQL	MariaDB
`INTEGER`	`5`	`6`	no	`6`
`INT`	`5`	`6`	no	`6`
`BIGINT`	yes	yes	no	no
`SMALLINT`	yes	yes	no	no
`TINYINT`	yes	no	no	no
`SIGNED`	`5.7`	no	`6`	`6`
`UNSIGNED`	`5.7`	no	`6`	`6`
`NUMERIC`	yes	yes	no	no
`DECIMAL`	`5.75`	`5.75`	`6`	`6`
`DECIMAL(p, s)`	`5.75`	`5.8`	`5.8`	`5.8`

What to Write Instead

This form answers the same on every engine:

SELECT ROUND(5.7);

Engine	Answer
SQLite 3.45.1	`6.0`
PostgreSQL 16.15	`6`
MySQL 8.0.46	`6`
MariaDB 10.11.14	`6`

Round on purpose with ROUND(x) or ROUND(x, digits), which every engine has.

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: error: MySQL refuses CAST(x AS INTEGER), and SQLite drops the fraction where PostgreSQL and MariaDB round it. [cast-to-number]
    CAST(5.7 AS INTEGER)
    Round on purpose with ROUND(x) or ROUND(x, digits), which every engine has.

1 problem: 1 error.

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