HEX + CONVERT / INSERT with utf8 returns different values with MySQL

[espresso98]

Bug Report

1. Minimal reproduce step

select hex(convert(char(2557 using latin1) using utf8));
SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));

2. What did you expect to see?

mysql>  select hex(convert(char(2557 using latin1) using utf8));
+--------------------------------------------------+
| hex(convert(char(2557 using latin1) using utf8)) |
+--------------------------------------------------+
| 09C3BD                                           |
+--------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));
+-------------------------------------------+
| HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)) |
+-------------------------------------------+
| D18F20                                    |
+-------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.03 sec)

3. What did you see instead

tidb> select hex(convert(char(2557 using latin1) using utf8));
+--------------------------------------------------+
| hex(convert(char(2557 using latin1) using utf8)) |
+--------------------------------------------------+
| 093F                                             |
+--------------------------------------------------+
1 row in set (0.01 sec)

tidb> SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));
+-------------------------------------------+
| HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)) |
+-------------------------------------------+
| D120D18E                                  |
+-------------------------------------------+
1 row in set (0.02 sec)

4. What is your TiDB version?

tidb_version(): Release Version: v5.5.0-alpha-210-g11f4ca802
Edition: Community
Git Commit Hash: 11f4ca802083ee38d5972730ba8f9b72395316fb
Git Branch: master
UTC Build Time: 2022-02-24 04:52:01
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[aytrack]

for the first case, it's may cause by this as describe in https://docs.pingcap.com/tidb/stable/character-set-and-collation#character-sets-and-collations-supported-by-tidb

TiDB incorrectly treats latin1 as a subset of utf8. This can lead to unexpected behaviors when you store characters that differ between latin1 and utf8 encodings. It is strongly recommended to the utf8mb4 character set. See TiDB #18955 for more details.

but there maybe another bug here

MySQL root@127.0.0.1:(none)> select char(2557 using latin1)
'utf-8' codec can't decode byte 0xfd in position 1: invalid start byte
MySQL root@127.0.0.1:(none)> use test;
Packet sequence number wrong - got 5 expected 1

case 2:
tidb has the same behavior with mysql 8.0.23

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.04 sec)

mysql> SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));
+-------------------------------------------+
| HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)) |
+-------------------------------------------+
| D18F20                                    |
+-------------------------------------------+
1 row in set, 1 warning (0.04 sec)


# mysql 8.0.23

MySQL root@172.16.5.103:test> SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));
+-------------------------------------------+
| HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)) |
+-------------------------------------------+
| D120D18E                                  |
+-------------------------------------------+

1 row in set
Time: 0.062s
MySQL root@172.16.5.103:test>
MySQL root@172.16.5.103:test> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+

[aytrack]

tidb v5.3.0 has a same behavior with mysql 8.0.26, but since v5.4.0 the behavior is changed.

MySQL root@127.0.0.1:(none)> select tidb_version()\G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v5.3.0
Edition: Community
Git Commit Hash: 4a1b2e9fe5b5afb1068c56de47adb07098d768d6
Git Branch: heads/refs/tags/v5.3.0
UTC Build Time: 2021-11-24 13:31:24
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

1 row in set
Time: 0.001s
MySQL root@127.0.0.1:(none)> SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));
                         ->
+-------------------------------------------+
| HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)) |
+-------------------------------------------+
| D18F20                                    |
+-------------------------------------------+

[espresso98]

Thanks for the in-depth investigation. I am working on MySQL v.8.0.28

[solotzg]

For select hex(convert(char(2557 using latin1) using utf8));

https://nicj.net/mysql-converting-an-incorrect-latin1-column-to-utf8/

latin1 is a single-byte encoding, so each of the 256 characters are just a single byte.

The UTF-8 encoding was designed to be backward-compatible with ASCII documents, for the first 128 characters. For characters above 128, a multi-byte sequence describes the character. According to http://en.wikipedia.org/wiki/UTF-8#Description.

For 2557(0x09FD), whose size is 2 bytes, will be covert to utf8 code 0x09C3BD, whose size is 3 bytes.

2557 to binary 1001 1111 1101, 0xFD is bigger than 127, need to be converted to 2 bytes 11000011 10111101. We got 1001 11000011 10111101 which is 0x9c3bd.

But for tidb >= 5.4, result is 0x093F.

---

Root cause is #30288

[solotzg]

For SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));

According to https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_insert, INSERT(str,pos,len,newstr) use str as input. Convert _utf8 0xD18FD18E to str will be [0xD18F, 0xD18E] in hex.

INSERT(_utf8 0xD18FD18E, 2, 1, 0x20) will replace 0xD18E by 0x20 and get 0xD18F20 finally.

tidb >= 5.4 will get unexpected result.

tidb/expression/builtin_string.go

Lines 3646 to 3653 in d4dc6b5

	if types.IsBinaryStr(bf.tp) {
	sig = &builtinInsertSig{bf, maxAllowedPacket}
	sig.setPbCode(tipb.ScalarFuncSig_Insert)
	} else {
	sig = &builtinInsertUTF8Sig{bf, maxAllowedPacket}
	sig.setPbCode(tipb.ScalarFuncSig_InsertUTF8)
	}
	return sig, nil

The type is binary rather than utf8.

[zanmato1984]

Seems like some encoding/collation change broke some of our old behavior, thus this issue. Changing to sig/sql-infra.

[YangKeao]

For SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));

Another interesting case:

MySQL [(none)]> SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, _utf8 0x20));
+-------------------------------------------------+
| HEX(INSERT(_utf8 0xD18FD18E, 2, 1, _utf8 0x20)) |
+-------------------------------------------------+
| D18F20                                          |
+-------------------------------------------------+
1 row in set (1.646 sec)

It's related with the following codes:

tidb/expression/collation.go

Lines 398 to 404 in aff0c36

	if dstCollation == charset.CollationBin || argCollation == charset.CollationBin {
	if coercibility > arg.Coercibility() || (coercibility == arg.Coercibility() && argCollation == charset.CollationBin) {
	coercibility, dstCharset, dstCollation = arg.Coercibility(), argCharset, argCollation
	}
	repertoire |= arg.Repertoire()
	continue
	}

If the third argument is binary, the return collation will be inferred as binary 🤔

[YangKeao]

And the insert chooses to cast charset according to the return type, but not the first argument. This change is brought by #29905 .

[YangKeao]

well, I remember the behavior is the same with the MySQL version before 8.0.24

The behavior of SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)); is compatible with older version of MySQL (so does lpad/rpad/...)

I'll close this issue as it's expected (both problems). If you need further discussion, feel free to reopen this issue 😃 .