MySQL Bugs: #113864: select statements with convert function report error for specified usage

Bug #113864	select statements with convert function report error for specified usage
Submitted:	2 Feb 2024 9:41	Modified:	4 Feb 2024 3:04
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Any (rhel-7.4)
Assigned to:		CPU Architecture:	Any (x86-64)
Tags:	charset, collation

Description:
Hello,
  Recently we find a case that I get an error when access convert function with SELECT statement. The test case is followed.

How to repeat:

[yxxdb_8031@localhost ~]$ mysql -uroot -p'db1x@NJ+1' -S bin/mysql1.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.36 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database d4;
Query OK, 1 row affected (0.00 sec)

mysql> use d4
Database changed
mysql>
mysql> CREATE TABLE c1 (   a varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> nsert into c1 values ('asfsdgdsgh');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nsert into c1 values ('asfsdgdsgh')' at line 1
mysql>
mysql> insert into c1 values ('asfsdgdsgh');
Query OK, 1 row affected (0.00 sec)

mysql> explain select * from c1 where c1.a = convert('asfsdgdsgh' using utf8mb4);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

Hi Mr. Yue,

Thank you for your bug report.

However, this is not a bug.

That error message is the expected behaviour.

This is all explained in our Reference Manual. Simply, both collations have the same coercibility. Coercibility is the ability of a string to override the collation of another string. Since they both have the same coercibility, there is no room left for the conversion.

This is actually basic SQL.

Not a bug.

Hello，
  I have carefully read the Reference manual about convert function part from "14.10 Cast Functions and Operators" and "12.8.4 Collation Coercibility in Expressions", and noticed the description: 

```
To convert strings between character sets, you can also use CONVERT(expr, type) syntax (without USING), or CAST(expr AS type), which is equivalent.
....
If you specify CHARACTER SET charset_name as just shown, the character set and collation of the result are charset_name and the default collation of charset_name. 
```

The I tried to modify the default collation of utf8mb4 as utf8mb4_general_ci, but the behavior seems to indicate that expression `convert('xxx' using utf8mb4)` always produces a result of collation utf8mb4_0900_ai_ci instead of default collation for charser utf8mb4 controlled by configure `default_collation_for_utf8mb4`, which makes me confused. 

The new case if followed:

```
mysql> create database d4;
Query OK, 1 row affected (0.01 sec)

mysql> use d4
Database changed
mysql>
mysql> CREATE TABLE c1 (   a varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into c1 values ('asfsdgdsgh');
Query OK, 1 row affected (0.00 sec)

mysql> set @@session.default_collation_for_utf8mb4 = 'utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@session.default_collation_for_utf8mb4;
+-----------------------------------------+
| @@session.default_collation_for_utf8mb4 |
+-----------------------------------------+
| utf8mb4_general_ci                      |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from c1 where c1.a = convert('asfsdgdsgh' using utf8mb4);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
```

In this case, column c1.a uses collation utf8mb4_general_ci, and default_collation_for_utf8mb4 is modified as utf8mb4_general_ci so I expecte that expression `convert('asfsdgdsgh' using utf8mb4)` produces result with utf8mb4_general_ci collation, so I don't know why still 'Illegal mix of collations' error is reported.

Thanks for you work in advance.

Please note the warning in the user guide for default_collation_for_utf8mb4:

  The default_collation_for_utf8mb4 system variable is for internal use by
  MySQL Replication only.

It has no use in user commands.

Thus, you need to add an explicit COLLATE clause for this query to be valid.