Collation dependent issue in TiDB on select * from t1 where v like

[espresso98]

Bug Report

I enabled the collation framework but the result is different as in MySQL.

1. Minimal reproduce step

drop table if exists t1;
create table t1 (v varchar(254), index (v)) charset=utf8mb4 collate=utf8mb4_bin;
insert into t1 values ("This is a test "),(" This is a test "),("This is a test"),("This is a test");
select * from t1 where v like 'This is a test';
select * from t1 where v='This is a test';

2. What did you expect to see?

In MySQL5.7 and 8.0,

mysql> select * from t1 where v like 'This is a test';
+----------------+
| v              |
+----------------+
| This is a test |
| This is a test |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1 where v='This is a test';
+-----------------+
| v               |
+-----------------+
| This is a test  |
| This is a test  |
| This is a test  |
+-----------------+
3 rows in set (0.01 sec)

3. What did you see instead

In TiDB,

mysql> select * from t1 where v like 'This is a test';
+-----------------+
| v               |
+-----------------+
| This is a test  |
| This is a test  |
| This is a test  |
+-----------------+
3 rows in set (0.00 sec)

mysql> select * from t1 where v='This is a test';
+-----------------+
| v               |
+-----------------+
| This is a test  |
| This is a test  |
| This is a test  |
+-----------------+
3 rows in set (0.00 sec)

4. What is your TiDB version?

tidb_version(): Release Version: v5.4.0-alpha-459-g87ab28ebb-dirty
Edition: Community
Git Commit Hash: 87ab28ebb2ac87b6e8d7c252187338c517598318
Git Branch: master
UTC Build Time: 2021-12-20 19:44:29
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[xhebox]

/cc @xiongjiwei

[xiongjiwei]

/cc @winoros

[Reminiscent]

The root cause is that the like condition is used to construct the range. But range does not filter the cases with trailing spaces, while the like condition can filter. One way to ensure that there is no regression is to use the like condition to construct a range while preserving the like expressions as a selection. But in the current implementation, it's not easy to do that.

[xhebox]

Closed by #31278

[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.