Can't use like to build range when new collation is enabled

[King-Dylan]

Bug Report

After pr 31278 , we can't use like to build range when new collation is enabled. This doesn't make sense and will cause bad sql performance.
Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t1` (
  `id` varchar(40) COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  KEY `ix_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

mysql> explain SELECT * FROM t1 cm use index(ix_name) WHERE cm.name LIKE  '18620391343%';
+-------------------------------+---------+-----------+--------------------------------------------------------+---------------------------------------------------------------------+
| id                            | estRows | task      | access object                                          | operator info                                                       |
+-------------------------------+---------+-----------+--------------------------------------------------------+---------------------------------------------------------------------+
| IndexLookUp_7                 | 250.00  | root      |                                                        |                                                                     |
| ├─IndexRangeScan_5(Build) | 250.00  | cop[tikv] | table:cm, index:ix_name(name) | range:["18620391343","18620391344"), keep order:false, stats:pseudo |
| └─TableRowIDScan_6(Probe) | 250.00  | cop[tikv] | table:cm                                               | keep order:false, stats:pseudo                                      |
+-------------------------------+---------+-----------+--------------------------------------------------------+---------------------------------------------------------------------+
3 rows in set (0.01 sec)

3. What did you see instead (Required)

mysql> explain SELECT * FROM t1 cm use index(ix_name) WHERE cm.name LIKE  '18620391343%';
+-------------------------------+----------+-----------+--------------------------------------------------------+---------------------------------------------------------+
| id                            | estRows  | task      | access object                                          | operator info                                           |
+-------------------------------+----------+-----------+--------------------------------------------------------+---------------------------------------------------------+
| IndexLookUp_8                 | 8000.00  | root      |                                                        |                                                         |
| ├─Selection_7(Build)      | 8000.00  | cop[tikv] |                                                        | like(test.company_member.logonname, "18620391343%", 92) |
| │ └─IndexFullScan_5     | 10000.00 | cop[tikv] | table:cm, index:ix_name(name) | keep order:false, stats:pseudo                          |
| └─TableRowIDScan_6(Probe) | 8000.00  | cop[tikv] | table:cm                                               | keep order:false, stats:pseudo                          |
+-------------------------------+----------+-----------+--------------------------------------------------------+---------------------------------------------------------+
4 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

v6.5.3

[jackysp]

/found gs

[time-and-fate]

Converting this issue to an enhancement since it's a known limitation, as said in #31278, and needs extra work for implementation and testing.

[time-and-fate]

/type enhancement

[time-and-fate]

/remove-type bug

[time-and-fate]

Background

To implement this feature, the key is to build the range for the wildcard case correctly. In brief, our target is to build sortKey(chars) as the start key and sortKey(chars)+1 as the end key where the chars is the part in the pattern before the wildcard.
This sounds simple, but currently, we don't convert the value to the sort key in this stage (newBuildFromPatternLike of "points builder" in util/ranger), we do this conversion in the executor.
Therefore, we need to restructure many code paths to enable us to safely convert the values to the sort key in the ranger, specifically, in newBuildFromPatternLike.

Implementation

1. Currently, cutting prefix for the prefix index happens after newBuildFromPatternLike. Cutting prefix requires the original value (because prefix length is calculated by characters instead of bytes), so we must first move the cutting prefix logic into "points builder".
2. Convert the string values to the sort key in the "points builder", and correctly build the range for the LIKE function.
3. Some usages of the "points builder" require the original value instead of the sort key, so we need to provide an option to the "points builder" so the caller can choose.
4. After the "points builder", there are some type conversion works, we must make them correctly handle the newly introduced sort key case.
5. Remove the current limitation that we can't use LIKE function on new collation column to build ranges in the "checker".

Tests

Construct tables and data, and check the execution plan and query result for the following cases:

Charset

• ascii
• latin1
• gbk
• utf8*

Collation

• _bin
• *_ci

Index

• Normal index
• Prefix index
• Multi-column index
• Unique key
• Primary key (clustered)
• Primary key (nonclustered)

LIKE pattern

• Empty / All spaces
• Contains / Not contain wildcards
• Starts / Ends with wildcards
• Wildcards: % / _
• Mix spaces in non-space characters

[seiya-annie]

/found customer