MySQL Bugs: #99538: The collation return by concat() is not corresponding to the document.

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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;

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.

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.

Hi,

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.

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."