Why are case-sensitive report labels grouped together?

Prepare the report input

Original report treats a and A as separate labels, but the legacy column declares NOCASE. GROUP BY inherits that comparison unless overridden. BINARY at grouping preserves both raw cases; the displayed minimum also uses BINARY for deterministic representation.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE labels(id INTEGER PRIMARY KEY,label TEXT COLLATE NOCASE NOT NULL); INSERT INTO labels VALUES(1,'a'),(2,'A');

Compare misleading and corrected results

SELECT min(label COLLATE BINARY) AS label,COUNT(*) AS records FROM labels GROUP BY label ORDER BY label COLLATE BINARY;

SELECT min(label COLLATE BINARY) AS label,COUNT(*) AS records FROM labels GROUP BY label COLLATE BINARY ORDER BY label COLLATE BINARY;

Bad group A has two records; corrected groups A and a each have one. BINARY on the displayed minimum chooses a representative; BINARY in GROUP BY is what separates the groups.

Check a fresh boundary case

Add another a. Lowercase a reaches two while uppercase A stays one.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO labels VALUES(3,'a');
SELECT min(label COLLATE BINARY) AS label,COUNT(*) AS records FROM labels GROUP BY label COLLATE BINARY ORDER BY label COLLATE BINARY;