GROUP BY Counts Berlin and berlin as One City on MySQL and Two on PostgreSQL

A report counts sign-ups per city. On PostgreSQL and SQLite, Berlin and berlin are two rows. On MySQL and MariaDB they are one, and the count beside it is the sum of both.

MySQL and MariaDB compare text with a case-insensitive collation by default (see Text comparisons and letter case), and grouping and removing duplicates use the same comparison. The spelling that stands for a group is the one the engine happened to see first. On SQLite and PostgreSQL each spelling is its own group.

What Each Engine Answered

On a table made with

CREATE TABLE t (s VARCHAR(10));
INSERT INTO t VALUES ('x'), ('X'), ('y');

the example

SELECT COUNT(DISTINCT s) FROM t;

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 COUNT(DISTINCT LOWER(s)) FROM t;

Group on LOWER(s) to merge spellings on every engine, as in SELECT LOWER(s), COUNT(*) FROM t GROUP BY LOWER(s). To keep them apart on MySQL and MariaDB instead, give the column a binary collation there.

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: info: GROUP BY, DISTINCT, COUNT(DISTINCT) and UNION treat 'x' and 'X' as one value on MySQL and MariaDB and as two on SQLite and PostgreSQL. [group-case]
    COUNT(DISTINCT s)
    Group on LOWER(s) to merge spellings on every engine, as in SELECT LOWER(s), COUNT(*) FROM t GROUP BY LOWER(s). To keep them apart on MySQL and MariaDB instead, give the column a binary collation there.

1 problem: 1 note.

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