concat json and utf8mb4_unicode_ci type value, result should be binary

[seiya-annie]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE tbl_12 (
col_0 varchar(183) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
col_1 char(63) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
col_2 tinytext COLLATE utf8mb4_bin NOT NULL,
col_3 text CHARACTER SET utf8mb4 NOT NULL,
col_4 longtext COLLATE utf8mb4_unicode_ci NOT NULL,
col_5 json NOT NULL,
col_6 enum('Alice','Bob','Charlie','David') COLLATE utf8mb4_unicode_ci NOT NULL,
col_7 set('1','2','3','4') COLLATE utf8mb4_general_ci NOT NULL,
col_8 binary NOT NULL,
col_9 varbinary(1) NOT NULL,
col_10 blob NOT NULL,
col_11 year(4) NOT NULL,
col_12 bigint(20) NOT NULL,
PRIMARY KEY (col_1,col_12),
KEY idx_1 (col_12,col_6),
KEY idx_2 (col_6,col_7,col_9(1),col_0)
)
PARTITION BY HASH( col_12 )
PARTITIONS 2;

2. show concat collation
   MySQL [lt]> select col_5, collation(col_5) from tbl_12;
   +------------+------------------+
   | col_5 | collation(col_5) |
   +------------+------------------+
   | {"a": "b"} | binary |
   +------------+------------------+
   1 row in set (0.01 sec)

MySQL [lt]> select col_1, collation(col_1) from tbl_12;
+-------+--------------------+
| col_1 | collation(col_1) |
+-------+--------------------+
| c | utf8mb4_unicode_ci |
+-------+--------------------+
1 row in set (0.00 sec)

MySQL [lt]> select concat(col_5,col_1), collation(concat(col_5,col_1)) from tbl_12;
+---------------------+--------------------------------+
| concat(col_5,col_1) | collation(concat(col_5,col_1)) |
+---------------------+--------------------------------+
| {"a": "b"}c | utf8mb4_unicode_ci |
+---------------------+--------------------------------+
1 row in set (0.00 sec)

2. What did you expect to see? (Required)

collation for json should be binary， concat binary and utf8mb4_unicode_ci , the result collation should be binary

3. What did you see instead (Required)

the result collation is utf8mb4_unicode_ci

4. What is your TiDB version? (Required)

| Release Version: v5.5.0-alpha
Edition: Community
Git Commit Hash: adcaaa5
Git Branch: heads/refs/tags/v5.5.0-alpha
UTC Build Time: 2022-02-08 14:56:30
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[Defined2014]

The collation with JSON type is strange in MySQL. When it be used in builtin func, the collation with JSON should be utf8mb4_bin. Ref #31320 (comment)

The result of select concat(col_5,col_1), collation(concat(col_5,col_1)) from tbl_12; is utf8mb4_bin which should be fixed by #32301