Columns Missing From GROUP BY Work on SQLite and MariaDB and Fail on PostgreSQL and MySQL
SELECT customer_id, name, SUM(total) FROM orders GROUP BY customer_id is the kind of query that runs for years on SQLite or MariaDB. Point the same code at PostgreSQL or MySQL and it stops with an error, because name is neither grouped nor summed.
SELECT a, b FROM t GROUP BY a asks for one b per group without saying which. SQLite and MariaDB pick one (MariaDB’s default mode leaves out ONLY_FULL_GROUP_BY). PostgreSQL and MySQL stop with an error, unless the grouped column is the table’s primary key, which fixes every other column of that table.
What Each Engine Answered
On a table made with
CREATE TABLE t (a INTEGER, b INTEGER);
INSERT INTO t VALUES (1, 1), (1, 2);
the example
SELECT a, b FROM t GROUP BY a;
gave these answers:
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 1, 1 |
| PostgreSQL 16.15 | error: column "t.b" must appear in the GROUP BY clause or be used in an aggregate function |
| MySQL 8.0.46 | error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'baresql_compare.t.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by |
| MariaDB 10.11.14 | 1, 1 |
What to Write Instead
This form answers the same on every engine:
SELECT a, MAX(b) FROM t GROUP BY a;
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 1, 2 |
| PostgreSQL 16.15 | 1, 2 |
| MySQL 8.0.46 | 1, 2 |
| MariaDB 10.11.14 | 1, 2 |
Add the column to GROUP BY, or say which value you want with an aggregate such as MAX(b).
What BareSQL Portable Reports
Saved as example.sql, the example gets this from baresql lint --all:
$ baresql lint --all example.sql
example.sql:3:11: error: A selected column that is neither grouped nor aggregated takes its value from any row of the group on SQLite and MariaDB; PostgreSQL and MySQL refuse the query. [ungrouped-column]
b
Add the column to GROUP BY, or say which value you want with an aggregate such as MAX(b).
1 problem: 1 error.
baresql rules ungrouped-column prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.