Bug #99357 Return error of Illegal mix of collations of explicit and implicit collations.
Submitted: 26 Apr 2020 5:57 Modified: 28 Apr 2020 13:33
Reporter: wj huang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[26 Apr 2020 5:57] wj huang
Description:
In document https://dev.mysql.com/doc/refman/8.0/en/charset-collation-coercibility.html, it says:
"Use the collation with the lowest coercibility value."
But the following query will return an error, which it's not corresponding to the document.

mysql> create table t( a char(10) collate ascii_bin, b char(10) collate utf8mb4_bin);
Query OK, 0 rows affected (0.00 sec)
mysql> select concat(a collate ascii_bin, b) from t;
ERROR 1267 (HY000): Illegal mix of collations (ascii_bin,EXPLICIT) and (utf8mb4_bin,IMPLICIT) for operation 'concat'

Besides, the following query will not return an error:
mysql> set names utf8mb4;
mysql> select concat(_ascii "a" collate ascii_bin, "b");
+-------------------------------------------+
| concat(_ascii "a" collate ascii_bin, "b") |
+-------------------------------------------+
| ab                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
create table t( a char(10) collate ascii_bin, b char(10) collate utf8mb4_bin);
select concat(a collate ascii_bin, b) from t;

Suggested fix:
Don't return the error.
[28 Apr 2020 11:46] MySQL Verification Team
Hi Mr. huang,

Thank you for your bug report.

However, this is not a bug. Since you have read that page, let me quote to you one sentence:

"
An explicit COLLATE clause has a coercibility of 0 (not coercible at all).
"

Hence, in the first query, you can not coerce those two different collations at all.

Second query is coercible.

Hence , this is not a bug.
[28 Apr 2020 12:12] wj huang
Thank you for your reply.

Sorry for my previous misleading example, in the following queries:

mysql> set names utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> select concat(_ascii "a" collate ascii_bin, "b");
+-------------------------------------------+
| concat(_ascii "a" collate ascii_bin, "b") |
+-------------------------------------------+
| ab                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select collation("b");
+----------------+
| collation("b") |
+----------------+
| utf8mb4_bin    |
+----------------+
1 row in set (0.00 sec)

I think the only difference here is the coercibility value.  Then I can't understand why the first query is not coercible but the second is coercible.
[28 Apr 2020 12:35] MySQL Verification Team
Hi Mr. huang,

You can read on the same page about the exception with UTF collations.

Which is why your first query now works.

Not a bug.
[28 Apr 2020 13:33] wj huang
Thanks.
I figured out that it's because of the repertoire of a character set. Not a bug.
[28 Apr 2020 13:36] MySQL Verification Team
Hello Mr. huang,

Yes that is correct and you are welcome.