E0036 illegal-mix-of-collations

Comparing, joining, or combining columns with incompatible collations in MySQL.

What it reports

In MySQL, comparing or combining two column references whose collations differ and have the same coercibility (IMPLICIT) fails at runtime with:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

This error commonly surfaces after upgrading to MySQL 8.0 (where new tables default to utf8mb4_0900_ai_ci while existing tables retain utf8mb4_unicode_ci or utf8mb4_general_ci), or when joining tables with mixed character sets and collations.

sqlsift reports this for:

• Comparison operators (=, <>, <, <=, >, >=)
• JOIN conditions (ON a.code = b.code or JOIN ... USING (code))
• LIKE pattern comparisons
• IN (subquery) and IN (list) with column references
• Simple CASE expressions (CASE a.code WHEN b.code ... compares the operand with each WHEN value)

UNION / INTERSECT / EXCEPT branches, COALESCE / IFNULL arguments and CASE result branches are not reported: when their collations belong to the same character set, MySQL falls back to the character set's _bin collation for the result instead of raising the error.

The schema's collations follow CREATE TABLE options, column CHARACTER SET / COLLATE, ALTER TABLE ... CONVERT TO CHARACTER SET ... [COLLATE ...] (which converts the existing string columns) and ALTER TABLE ... [DEFAULT] CHARACTER SET / COLLATE (the default for columns added later). National types (NVARCHAR, NATIONAL VARCHAR) always use utf8mb3.

Example

CREATE TABLE a (
  id INT PRIMARY KEY,
  code VARCHAR(10) COLLATE utf8mb4_unicode_ci
) DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_unicode_ci;

CREATE TABLE b (
  id INT PRIMARY KEY,
  code VARCHAR(10) COLLATE utf8mb4_0900_ai_ci
) DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci;

SELECT * FROM a JOIN b ON a.code = b.code;

error[E0036]: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
  --> q.sql:1:27
    |
  1 | SELECT * FROM a JOIN b ON a.code = b.code;
    |                           ^^^^^^
    = help: Collation mismatch between column references. Consider using an explicit COLLATE clause.

When it is not reported

Nothing is reported when:

• Either operand has an explicit COLLATE clause (e.g. a.code COLLATE utf8mb4_bin = b.code), which gives it EXPLICIT coercibility.
• Either operand is a literal (e.g. a.code = 'xyz'), whose collation coercibility is COERCIBLE and converts silently.
• Either column has no known collation (database or server default is unknown).
• Both columns have matching collations.
• The collations belong to different character sets (MySQL converts one side), or one of them is a _bin collation.
• The dialect is not MySQL (e.g. PostgreSQL, SQLite).

Configuring

# sqlsift.toml
[rules]
illegal-mix-of-collations = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0036.