AVG Returns Four Decimal Places on MySQL and Sixteen Digits on PostgreSQL

The average rating of a product with ratings 1, 2 and 2 is 1.6667 on MySQL and MariaDB and 1.6666666666666667 on PostgreSQL and SQLite. A test that compares the number exactly passes on one and fails on the other.

MySQL and MariaDB return the average of exact numbers as a decimal with 4 more decimal places than the values have (the div_precision_increment setting), so the average of 1, 2 and 2 is 1.6667. PostgreSQL returns a numeric with about 16 significant digits, 1.6666666666666667, and SQLite a float with the same digits. Averages of floating point columns agree.

What Each Engine Answered

On a table made with

CREATE TABLE t (a INTEGER, d DECIMAL(10, 2));
INSERT INTO t VALUES (1, 1.00), (2, 2.00), (2, 2.00);

the example

SELECT AVG(a) FROM t;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT ROUND(AVG(a), 2) FROM t;

Round the average to the digits you need: ROUND(AVG(a), 2) gives the same answer on every engine.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:1:30: info: SQLite stores DECIMAL values as floating point numbers, so sums and comparisons can be off in the last digits. [decimal-as-float]
    DECIMAL(10, 2)
    Compare rounded values, ROUND(x, 2) = 0.3, or keep amounts as integers in the smallest unit, such as cents.
example.sql:3:8: warning: AVG of integers or decimals has 4 more decimal places than the values on MySQL and MariaDB, and about 16 significant digits on SQLite and PostgreSQL. [avg-digits]
    AVG(a)
    Round the average to the digits you need: ROUND(AVG(a), 2) gives the same answer on every engine.

2 problems: 1 warning, 1 note.

The example also runs into DECIMAL on SQLite, which has a post of its own.

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