MySQL Bugs: #99357: Return error of Illegal mix of collations of explicit and implicit collations.

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

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.

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.

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.

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.

Thanks.
I figured out that it's because of the repertoire of a character set. Not a bug.

Hello Mr. huang,

Yes that is correct and you are welcome.