skip to content

After moving a customer import to another SQL engine, SELECT DISTINCT name returns fewer rows and ORDER BY changes; how does collation explain both, and how do you pin the behaviour?

level: seniorimportance: should knowfreq 38%

answer

  1. the data did not change
  2. one ruleset for equality and sorting
  3. case, accents, and trailing spaces
  4. each engine's default differs
  5. declare it, do not inherit it

basics

~20 s

A collation defines which strings count as equal and how they sort. A case- or accent-insensitive default merges 'Ann' and 'ANN' under DISTINCT, and a different ruleset reorders ORDER BY. Declare the collation on the column or with COLLATE.

solid answer

~50 s

`DISTINCT`, `GROUP BY`, `=`, unique constraints and `ORDER BY` all compare strings under a collation, so a different default changes results on identical data. MySQL's current default, `utf8mb4_0900_ai_ci`, ignores case and accents, so `'Ann'`, `'ANN'` and `'Ánn'` collapse into one row. SQL Server installs commonly default to case-insensitive too. PostgreSQL's usual deterministic collations keep them apart, and Oracle's `=` and SQLite compare in binary by default. Sort order moves as well: a binary collation places `'Zebra'` before `'apple'`, a linguistic one does not. To diagnose it, I group by the name and count distinct spellings under a binary collation, which lists exactly what merged. The fix is a decision: state whether `'Ann'` and `'ANN'` are one customer, then declare that collation on the column, or use `COLLATE` on one expression. `UPPER` folds only case, so it cannot reproduce accent-insensitivity.

code

sql · 9 lines
sql
SELECT s
FROM (VALUES ('apple'), ('Zebra'), ('Éa')) AS v(s)
ORDER BY s COLLATE "C";
-- Zebra, apple, Éa

SELECT COUNT(DISTINCT s) AS raw_values,
       COUNT(DISTINCT LOWER(s)) AS folded_values
FROM (VALUES ('Ann'), ('ANN'), ('ann')) AS v(s);
-- raw_values 3, folded_values 1

go deeper

for a junior

Recall that a collation is a set of rules for comparing and sorting text, and that it can make 'Ann' and 'ANN' equal or different. Results can change even though the data did not.

for a middle

Explain how the column, database and server defaults feed into an effective collation, and why equality, DISTINCT, GROUP BY and ORDER BY all follow it. Contrast UPPER with an insensitive collation on an accented name.

for a senior

Treat a changed DISTINCT count as a symptom to prove: confirm the data, read the effective collation, list the merged spellings, then get the business rule from the data's owner and declare it instead of inheriting a default.

for a principal

Choose one collation policy per kind of text, such as identifiers versus display names, because a later change means re-checking constraints and resolving merged rows. Explicit declarations also make a future engine move far cheaper.

## What a collation is A **collation** is the ruleset an engine uses to compare strings. It answers two questions: are these two strings equal, and which one comes first? Every string comparison uses one, whether or not anyone chose it, and it usually arrives by inheritance from the column, then the database, then the server or install defaults. Because it governs comparison, a collation affects more than sorting: - **Equality and filters**: `WHERE name = 'ann'` may or may not match `'Ann'`. - **`DISTINCT` and `GROUP BY`**: strings that compare equal become one row or one group. - **Unique constraints**: a load can be rejected, or an upsert can merge two customers, because two values count as the same key. - **`ORDER BY`**, and range comparisons such as `name > 'M'`. ## Why the two symptoms have one cause The data did not change, so a drop in `DISTINCT` rows plus a new sort order together point at the comparison rules. The defaults really do differ: | Engine | Typical default behaviour for `=` | |---|---| | MySQL 8 (`utf8mb4_0900_ai_ci`) | Ignores case and accents | | SQL Server | Chosen at install; commonly case-insensitive, accent-sensitive | | PostgreSQL | Deterministic: `'Ann'` and `'ANN'` differ, whatever the locale's sort order | | Oracle | Binary for `=`; `ORDER BY` can follow the session's sort setting | | SQLite | Binary | Moving from a binary-style engine to MySQL therefore merges rows, and moving the other way splits them. Sorting changes for a related reason. Binary comparison orders by code point, so all capitals precede all lower-case letters, and accented letters come last: `'Zebra'`, `'apple'`, `'Éa'`. A linguistic collation places them the way a reader expects: `'apple'`, `'Éa'`, `'Zebra'`. Trailing spaces are a further variable. SQL Server, and MySQL's older `PAD SPACE` collations, treat `'abc'` and `'abc '` as equal, while MySQL's `utf8mb4_0900` collations and PostgreSQL's `text` type do not. So an uncleaned import can lose rows from `DISTINCT` on one engine and keep them on another, with no case difference at all. ## Diagnosing it 1. Confirm the data matches: compare `COUNT(*)` and `COUNT(name)` on both engines, so you are not chasing a load problem. 2. Read the effective collation from the engine's catalog, starting at the column, because a query expression, a column and a database can each differ. 3. List what merged. Group by the name under the current rules, and count spellings under a binary collation: ```sql -- MySQL: names that the default collation treats as one value SELECT name, COUNT(*) AS merged_rows, COUNT(DISTINCT name COLLATE utf8mb4_bin) AS spellings FROM customer_import GROUP BY name HAVING COUNT(DISTINCT name COLLATE utf8mb4_bin) > 1; ``` 4. Take that list to whoever owns the data. Whether `'Müller'` and `'Muller'` are one customer is a business answer, not a technical one. ## Pinning the behaviour Inheriting a default is what caused the problem, so make the rule explicit: - **On the column**, when the rule is a property of the data. This is the durable choice, because every comparison then agrees. - **With `COLLATE` on one expression**, when one report needs a different view, such as `ORDER BY name COLLATE "C"` on PostgreSQL. Collation names differ by engine, so this line never moves between engines unedited. - **In tests**, with fixtures that include mixed case, accents and trailing spaces, asserting both a count and an order. - **In load checks**, by recording the effective collation beside the row counts whenever a load is compared across environments. ## Why UPPER and LOWER are not the same thing `UPPER(a) = UPPER(b)` is the portable-looking alternative, and it answers a narrower question: | Comparison | `'Ann'` vs `'ANN'` | `'José'` vs `'Jose'` | |---|---|---| | Binary collation | Different | Different | | `UPPER` on both sides, accent-sensitive collation | Equal | Different | | Case- and accent-insensitive collation | Equal | Equal | `UPPER` maps case and leaves accents alone. It also depends on locale rules. In a Turkish locale `UPPER('i')` is not `'I'`, and the German `ß` can stay as it is or become `SS`: PostgreSQL's `pg_unicode_fast` collation gives `'STRASSE'` from `'straße'`, while its `pg_c_utf8` gives `'STRAßE'`. Under PostgreSQL's plain `C` locale, `LOWER` leaves `É` unchanged because only ASCII is mapped. PostgreSQL also offers `casefold`, which is meant for caseless matching rather than for producing a display form. Folding can change a string's length, so never assume a mapped value fits the same width.

  • Why can WHERE UPPER(a) = UPPER(b) disagree with a case-insensitive collation?
    UPPER maps case and leaves accents alone, so 'José' and 'Jose' stay different, while a collation that also ignores accents treats them as equal. UPPER also follows locale rules: in a Turkish locale 'i' does not map to 'I', and the German sharp s may or may not become SS. The two approaches answer related but different questions.
  • How can a trailing space change DISTINCT results between two SQL engines?
    SQL Server, and MySQL's older PAD SPACE collations, ignore trailing spaces when comparing, so 'abc' and 'abc ' are one value. MySQL's utf8mb4_0900 collations and PostgreSQL's text type treat them as different, so an uncleaned import yields more DISTINCT rows there. PostgreSQL's CHAR(n) also disregards trailing spaces when comparing, so the declared type matters as well.
  • How does a collation change a unique constraint on email during a customer import?
    Under a case-insensitive collation '[email protected]' and '[email protected]' are the same key, so the second insert fails or an upsert merges them into one customer. Under a case-sensitive collation both are stored as separate accounts. Decide the rule before loading, because changing it afterwards means resolving every conflict the old rule allowed through.

A collation is a filing clerk's rulebook. Hand two clerks the same customer folders: one who files Ann and ANN together ends up with fewer tabs, and one who files all capitals first arranges the drawer differently. The folders never changed.

  • DISTINCT compares raw bytes, so a collation cannot change how many rows it returns.
  • A collation only affects ORDER BY and has no effect on equality.
  • Every engine compares strings case-sensitively unless the query says otherwise.
  • Wrapping both sides in UPPER reproduces any case-insensitive collation exactly, accents included.
  • ORDER BY without COLLATE sorts alphabetically in the same way on every engine.