TiDB behavior is inconsistent with MySQL in latin1 character set

[SE-Bin]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

While using pymysql module to insert data to TiDB with the charset latin1 connection. I cannot use utf8 client to get results directly. But MySQL doesn't have any problems.

import  pymysql
import  pymysql.cursors

# TiDB
connection=pymysql.connect(host='127.0.0.1',
                           user='root',
                           password='',
                           db='test',
                           port=4000,
                           charset='latin1')
# MySQL
connection1=pymysql.connect(host='127.0.0.1',
                           user='root',
                           password='',
                           db='test',
                           port=13306,
                           charset='latin1')

try:
   with connection.cursor() as cursor:
    sql="insert into test.t2 (c1) values ('é')"
    cursor.execute(sql)
    connection.commit()

    with connection1.cursor() as cursor1:
     cursor1.execute(sql)
     connection1.commit()


 
finally:
    connection.close()
    connection1.close()

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `c2` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

The client with utf8 connection can query the correct result

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-05 05:15:11
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

[lcwangchao]

Maybe same with #18955

[bb7133]

Close it since it is duplicated.