Tidb uhhex() results are conflicting with mysql( 5.7 and 8.0) when collation_database =gbk_chinese_ci

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1;
SET NAMES utf8;
SET collation_database =gbk_chinese_ci;
CREATE TABLE t1 (code  VARCHAR(4),a VARCHAR(4));
INSERT INTO `t1` (code) VALUES ('C29F'),('CC91'),('D697'),('8020'),('CCA0'),('D2B1'),('DFAB'),('DCB0');
UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
SELECT COUNT(*) FROM t1 WHERE a <> '';

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

--mysql 5.7
mysql> SELECT COUNT(*) FROM t1 WHERE a <> '';
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
--mysql 8.0
mysql> SELECT COUNT(*) FROM t1 WHERE a <> '';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT COUNT(*) FROM t1 WHERE a <> '';
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-264-g6efa36df6
Edition: Community
Git Commit Hash: 6efa36df6cff325106f67ecfe3d79816ba37ca6a
Git Branch: master
UTC Build Time: 2021-11-29 16:57:51
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[Defined2014]

Tried on MySQL 8.0.27, the behavior is same as TiDB. But on 8.0.12, the result is 1. :(
What's your MySQL version? @ramanich1

mysql> SELECT COUNT(*) FROM t1 WHERE a <> '';
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1;
+------+------+
| code | a    |
+------+------+
| C29F |     |
| CC91 | ̑    |
| D697 | ֗    |
| 8020 |      |
| CCA0 | ̠    |
| D2B1 | ұ    |
| DFAB | ߫    |
| DCB0 | ܰ    |
+------+------+
8 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

[Defined2014]

But we still have some problems with unhex() func related gbk.

create table t1(code  VARCHAR(4) charset gbk, a varchar(4) charset gbk);
INSERT INTO `t1` (code) VALUES ('C29F'),('CC91'),('D697'),('8020'),('CCA0'),('D2B1'),('DFAB'),('DCB0');
UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
select * from t1;

mysql

mysql> select * from t1;
+------+------+
| code | a    |
+------+------+
| C29F | 聼   |
| CC91 | 虘   |
| D697 | 謼   |
| 8020 |      |
| CCA0 | 虪   |
| D2B1 | 冶   |
| DFAB | 攉   |
| DCB0 | 馨   |
+------+------+
8 rows in set (0.00 sec)

TiDB

mysql> select * from t1;
+------+------+
| code | a    |
+------+------+
| C29F |      |
| CC91 |      |
| D697 |      |
| 8020 |      |
| CCA0 |      |
| D2B1 |      |
| DFAB |      |
| DCB0 |      |
+------+------+
8 rows in set (0.00 sec)

[Defined2014]

But we still have some problems with unhex() func related gbk.

create table t1(code  VARCHAR(4) charset gbk, a varchar(4) charset gbk);
INSERT INTO `t1` (code) VALUES ('C29F'),('CC91'),('D697'),('8020'),('CCA0'),('D2B1'),('DFAB'),('DCB0');
UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
select * from t1;

mysql

mysql> select * from t1;
+------+------+
| code | a    |
+------+------+
| C29F | 聼   |
| CC91 | 虘   |
| D697 | 謼   |
| 8020 |      |
| CCA0 | 虪   |
| D2B1 | 冶   |
| DFAB | 攉   |
| DCB0 | 馨   |
+------+------+
8 rows in set (0.00 sec)

TiDB

mysql> select * from t1;
+------+------+
| code | a    |
+------+------+
| C29F |      |
| CC91 |      |
| D697 |      |
| 8020 |      |
| CCA0 |      |
| D2B1 |      |
| DFAB |      |
| DCB0 |      |
+------+------+
8 rows in set (0.00 sec)

I think the problem is about insert to a gbk filed. The minimal step to reproduce it:

CREATE TABLE `t1` (a varchar(4) charset gbk);
insert into t1 values (0xc29f);

Use a hack way to fix it, it works. Seems we think all string is decoded as UTF-8.

diff --git a/table/column.go b/table/column.go
index 445a169a8..31244a75e 100644
--- a/table/column.go
+++ b/table/column.go
@@ -319,6 +319,10 @@ func CastValue(ctx sessionctx.Context, val types.Datum, col *model.ColumnInfo, r
                strategy := charset.TruncateStrategyReplace
                if val.Collation() == charset.CollationBin {
                        strategy = charset.TruncateStrategyTrim
+                       if col.Charset == charset.CharsetGBK {
+                               str, err = charset.NewEncoding(col.Charset).DecodeString(str)
+                               casted.SetString(str, charset.CollationUTF8)
+                       }
                }
                if newStr, invalidPos := v.Truncate(str, strategy); invalidPos >= 0 {
                        casted = types.NewStringDatum(newStr)

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.