Truncation of UTF8mb4 values when the UNION statement for binary and non-binary charsets

[Alkaagr81]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Do --binary-as-hex=false for better output

CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8mb4;
CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT);
INSERT INTO t1 (c) VALUES ('н1234567890');
INSERT INTO t2 (b, i) VALUES ('1', 1);
SELECT c FROM t1 UNION SELECT b FROM t2;
SELECT c FROM t1 UNION SELECT i FROM t2;
SELECT i FROM t2 UNION SELECT c FROM t1;
SELECT b FROM t2 UNION SELECT c FROM t1;

DROP TABLE t1, t2;

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

mysql> CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (c) VALUES ('н1234567890');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (b, i) VALUES ('1', 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT c FROM t1 UNION SELECT b FROM t2;
+--------------+
| c            |
+--------------+
| н1234567890  |
| 1            |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT c FROM t1 UNION SELECT i FROM t2;
+--------------+
| c            |
+--------------+
| н1234567890  |
| 1            |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT i FROM t2 UNION SELECT c FROM t1;
+--------------+
| i            |
+--------------+
| 1            |
| н1234567890  |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT b FROM t2 UNION SELECT c FROM t1;
+--------------+
| b            |
+--------------+
| 1            |
| н1234567890  |
+--------------+
2 rows in set (0.01 sec)

3. What did you see instead (Required)

mysql> CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (c) VALUES ('н1234567890');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (b, i) VALUES ('1', 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT c FROM t1 UNION SELECT b FROM t2;
+-------------+
| c           |
+-------------+
| н123456789  |
| 1           |
+-------------+
2 rows in set, 1 warning (0.02 sec)

mysql> SELECT c FROM t1 UNION SELECT i FROM t2;
+--------------+
| c            |
+--------------+
| н1234567890  |
| 1            |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT i FROM t2 UNION SELECT c FROM t1;
+--------------+
| i            |
+--------------+
| н1234567890  |
| 1            |
+--------------+
2 rows in set (0.01 sec)

mysql> SELECT b FROM t2 UNION SELECT c FROM t1;
+-------------+
| b           |
+-------------+
| н123456789  |
| 1           |
+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1406 | Data Too Long, field len 11, data len 12 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.5.0-alpha-60-g8c70432b0-dirty
Edition: Community
Git Commit Hash: 8c70432b000d648e9ea26cc57a7fe79d2e67d341
Git Branch: master
UTC Build Time: 2022-01-07 17:47:06
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false | 

[CbcWestwolf]

/assign bb7133