Wrong behaviour to use hex literal with introducer

[lance6716]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

MySQL

MySQL [(none)]> select _latin1 x'e9', charset(_latin1 x'e9');
+---------------+------------------------+
| _latin1 x'e9' | charset(_latin1 x'e9') |
+---------------+------------------------+
| é             | latin1                 |
+---------------+------------------------+
1 row in set (0.039 sec)

MySQL [(none)]> show variables like '%character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.006 sec)

TiDB

MySQL [(none)]> select _latin1 x'e9', charset(_latin1 x'e9');
+---------------+------------------------+
| _latin1 x'e9' | charset(_latin1 x'e9') |
+---------------+------------------------+
|               | latin1                 |
+---------------+------------------------+
1 row in set (0.002 sec)

MySQL [(none)]> show variables like '%character_set%';
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | utf8mb4                                                |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | utf8mb4                                                |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.003 sec)

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

same result

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

Release Version: v6.2.0-alpha-670-gfcbf9735a
Edition: Community
Git Commit Hash: fcbf9735a39a2181b1a2d34906882917b0bc34de
Git Branch: master
UTC Build Time: 2022-08-10 10:46:15
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore 

[lance6716]

this is related to how DM migrate data in different charset 😢

MySQL

MySQL [test]> create table t2 (c varchar(20) character set latin1);
Query OK, 0 rows affected (0.050 sec)

MySQL [test]> insert into t2 values (_binary x'e9');
Query OK, 1 row affected (0.041 sec)

MySQL [test]> select * from t2;
+------+
| c    |
+------+
| é    |
+------+
1 row in set (0.001 sec)

TiDB

MySQL [test]> create table t2 (c varchar(20) character set latin1);
Query OK, 0 rows affected (0.012 sec)

MySQL [test]> insert into t2 values (_binary x'e9');
Query OK, 1 row affected (0.003 sec)

MySQL [test]> select * from t2;
+------+
| c    |
+------+
|      |
+------+
1 row in set (0.002 sec)

[lance6716]

caused by latin1 problem #18955