Bug #108319 Illegal mix of collations for the function convert

• Submitted: 29 Aug 2022 13:05
• Modified: 29 Aug 2022 14:40
• Reporter: huahua xu
• Status: Not a Bug
• Category: MySQL Server: Options
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[29 Aug 2022 13:05] huahua xu

Description:
When a where clause contains the function `convert`, the query throws an error:

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

How to repeat:
mysql> drop database tmp;

mysql> create database tmp;

mysql> use tmp;

mysql> CREATE TABLE `t1` (  `id` int(11) NOT NULL,  `name` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

mysql> CREATE TABLE `t2` (  `id` int(11) NOT NULL,  `name2` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

mysql> set session default_collation_for_utf8mb4 = 'utf8mb4_general_ci';

mysql> show variables like '%utf8mb4%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+

mysql> create view v4 as select t1.name from (t1 join t2 on (t1.name = convert(t2.name2 using utf8mb4)));
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

[29 Aug 2022 13:20] MySQL Verification Team

Hi Mr. xu,

Thank you for your bug report.

However, this is not a bug.

If you have read our Reference Manual, you would have known that two incompatible collations can not be converted, when their coercibilities  are identical. 

This is fully described in our Reference Manual.

Not a bug.

[30 Aug 2022 8:48] Roy Lyseng

Notice the following comment for variable default_collation_for_utf8mb4:

  For internal use by replication.

It is not part of regular collation derivation.