MySQL: Illegal mix of collations: both same collation

Question

I see a number of questions on SO regarding something similar, but none of them addresses this particular detail.

I created a database using something like this:

CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

I create a table with the same character set & collation:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    id int AUTO_INCREMENT PRIMARY KEY,
    data varchar(255)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci ENGINE=INNODB;
INSERT INTO test(data) VALUES ('apple'),('banana'),('cherry'),('date');

I then join the table with a CTE:

WITH cte(name) AS (
    SELECT cast('apple' as char(255))
    UNION ALL SELECT cast('cherry' as char(255))
)
SELECT * FROM cte JOIN test on cte.name=test.data;

I get the following error:

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT)
for operation '='.

The thing is, the default collation for the database is the same as that for the table.

If I create the table without the collation clause at the end, it’s fine. It also works if I use nchar instead of char.

I know, why don’t I just create the table without the collation clause, or use nchar? The question is why doesn’t it work with char if I specify the collation?

Answer 1

Your table is fine.

It's your session collation that is incompatible.

Demo, using your example table and data:

mysql> WITH cte(name) AS (
    ->     SELECT cast('apple' as char(255))
    ->     UNION ALL SELECT cast('cherry' as char(255))
    -> )
    -> SELECT * FROM cte JOIN test on cte.name=test.data;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='

That's the error you got, because my session collation is utf8mb4_0900_ai_ci.

I can change the session collation and test again:

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci;

mysql> WITH cte(name) AS (
    ->     SELECT cast('apple' as char(255))
    ->     UNION ALL SELECT cast('cherry' as char(255))
    -> )
    -> SELECT * FROM cte JOIN test on cte.name=test.data;
+--------+----+--------+
| name   | id | data   |
+--------+----+--------+
| apple  |  1 | apple  |
| cherry |  3 | cherry |
+--------+----+--------+