Bug #99538 The collation return by concat() is not corresponding to the document.
Submitted: 13 May 2020 5:28 Modified: 13 May 2020 13:38
Reporter: wj huang Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[13 May 2020 5:28] wj huang
According to the document:
MySQL uses coercibility values with the following rules to resolve ambiguities:

Use the collation with the lowest coercibility value.

consider the following statements:

mysql> create table t(a char) charset utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t  values("a");
Query OK, 1 row affected (0.00 sec)
mysql> select collation(concat(a, binary("a"))) from t;
| collation(concat(a, binary("a"))) |
| binary                            |
1 row in set (0.00 sec)

Expected outcome: utf8mb4_0900_ai_ci

The document does say automatic conversion can happen. But it doesn't mention binary charset.

How to repeat:
create table t(a char) charset utf8mb4;
insert into t  values("a");
select collation(concat(a, binary("a"))) from t;
[13 May 2020 12:47] MySQL Verification Team
Hi Mr. huang,

Thank you for your bug report.

However, this is not a bug.

Document says that a collation with a lowest coercibility value is used. It does not depend on the collation itself, but on the coercibility level, which is defined by SQL that is used. Hence, there is no need to name all available collations.

Not a bug.
[13 May 2020 13:06] wj huang
Hi, Sinisa Milivojevic

I found that the coercibility of `binary("a")` is 2, and I thought it's 4 previously:

mysql> select coercibility(binary("a"));
| coercibility(binary("a")) |
|                         2 |
1 row in set (0.00 sec)

But I can't find any document about it. The document says:
The collation of a column or a stored routine parameter or local variable has a coercibility of 2.
So I have no clue why the result is 2.
[13 May 2020 13:10] MySQL Verification Team

All values of various coercibilities are sufficiently described in our Reference Manual, in several sub-chapters.

These definitions correspond to the SQL standard, so you can also consult SQL standard from 2011, which is available online and in books.
[13 May 2020 13:38] wj huang
Hi, Sinisa Milivojevic

Thanks for your reply.
Though I still can't find the specification in MySQL document, I do find the specification in SQL 2016:
"If TD is a fixed-length, variable-length, or large object character string, then the declared type collation of the <cast specification> is the character set collation of the character set of TD and its collation derivation is implicit."