LOCATE is ignoring the _latin1 literal and treating this as a utf8 string.

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select locate(_latin1 0xD0B1, _latin1 0xD0B0D0B1D0B2) ;

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

mysql> select locate(_latin1 0xD0B1, _latin1 0xD0B0D0B1D0B2) ;
+------------------------------------------------+
| locate(_latin1 0xD0B1, _latin1 0xD0B0D0B1D0B2) |
+------------------------------------------------+
|                                              3 |
+------------------------------------------------+

3. What did you see instead (Required)

mysql> select locate(_latin1 0xD0B1, _latin1 0xD0B0D0B1D0B2) ;
+------------------------------------------------+
| locate(_latin1 0xD0B1, _latin1 0xD0B0D0B1D0B2) |
+------------------------------------------------+
|                                              2 |
+------------------------------------------------+

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-67-g17edc5758
Edition: Community
Git Commit Hash: 17edc5758fbf865cada7e156318c41d3ee8a7501
Git Branch: master
UTC Build Time: 2021-11-10 20:45:18
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[ChenPeng2013]

https://docs.pingcap.com/tidb/stable/character-set-and-collation

Warning:

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.

[Defined2014]

/remove-type bug

[Defined2014]

/type enhancement

[Defined2014]

https://docs.pingcap.com/tidb/stable/character-set-and-collation

Warning:
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.

According to the doc, it's more like enhancement better than bug.

[Defined2014]

/type compatibility

[Alkaagr81]

--It works 
select POSITION(_utf8mb4'B' IN _utf8mb4'abcd' COLLATE utf8mb4_general_ci);
--Not works 
select POSITION(_utf8mb4'B' IN _utf8mb4'abcd');
select POSITION(_latin1'B' IN _latin1'aBcd');

--In Mysql
mysql> select POSITION(_utf8mb4'B' IN _utf8mb4'abcd');
+-----------------------------------------+
| POSITION(_utf8mb4'B' IN _utf8mb4'abcd') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select POSITION(_latin1'b' IN _latin1'aBcd');
+---------------------------------------+
| POSITION(_latin1'b' IN _latin1'aBcd') |
+---------------------------------------+
|                                     2 |
+---------------------------------------+
1 row in set (0.00 sec)

--IN TIDB
mysql> select POSITION(_utf8mb4'B' IN _utf8mb4'abcd');
+-----------------------------------------+
| POSITION(_utf8mb4'B' IN _utf8mb4'abcd') |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select POSITION(_latin1'b' IN _latin1'aBcd');
+---------------------------------------+
| POSITION(_latin1'b' IN _latin1'aBcd') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)