The expression index cannot be chosen because of different collations

[YangKeao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table `t` (data json DEFAULT NULL, KEY idx((cast(json_extract(data, '$.test') as char(64)))));

explain select /*+ USE_INDEX(t, idx) */ * from t where cast(json_extract(data, '$.test') as char(64)) = 'a';

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

+---------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------+-------------------------------------------------+
| id                              | estRows | task      | access object                                                                                    | operator info                                   |
+---------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------+-------------------------------------------------+
| Projection_4                    | 10.00   | root      |                                                                                                  | test.t.data                                     |
| └─IndexLookUp_8                 | 10.00   | root      |                                                                                                  |                                                 |
|   ├─IndexRangeScan_6(Build)     | 10.00   | cop[tikv] | table:t, index:idx(cast(json_extract(`data`, _utf8mb4'$.test') as char(64))) | range:["a","a"], keep order:false, stats:pseudo |
|   └─TableRowIDScan_7(Probe)     | 10.00   | cop[tikv] | table:t                                                                                          | keep order:false, stats:pseudo                  |
+---------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------+-------------------------------------------------+

3. What did you see instead (Required)

+------------------------------+----------+-----------+------------------------------------------------------------------------------+--------------------------------------------------------------------+
| id                           | estRows  | task      | access object                                                                | operator info                                                      |
+------------------------------+----------+-----------+------------------------------------------------------------------------------+--------------------------------------------------------------------+
| IndexLookUp_8                | 8000.00  | root      |                                                                              |                                                                    |
| ├─IndexFullScan_5(Build)     | 10000.00 | cop[tikv] | table:t, index:idx(cast(json_extract(`data`, _utf8mb4'$.test') as char(64))) | keep order:false, stats:pseudo                                     |
| └─Selection_7(Probe)         | 8000.00  | cop[tikv] |                                                                              | eq(cast(json_extract(test.t.data, "$.test"), var_string(64)), "a") |
|   └─TableRowIDScan_6         | 10000.00 | cop[tikv] | table:t                                                                      | keep order:false, stats:pseudo                                     |
+------------------------------+----------+-----------+------------------------------------------------------------------------------+--------------------------------------------------------------------+

4. What is your TiDB version? (Required)

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.3.0
Edition: Community
Git Commit Hash: 1a0c3ac3292fff7742faa0c00a662ccb66ba40db
Git Branch: HEAD
UTC Build Time: 2024-08-20 10:13:01
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[YangKeao]

A workaround is to specify the collation explicitly in both DDL and DQL:

create table `t` (data json DEFAULT NULL, KEY idx((cast(json_extract(data, '$.test') as char(64)) collate utf8mb4_bin )));

explain select /*+ USE_INDEX(t, idx) */ * from t where cast(json_extract(data, '$.test') as char(64)) collate utf8mb4_bin = 'a';

[YangKeao]

It's because for the DDL, the column type is regarded as utf8mb4_0900_ai_ci (according to the configuration of client), but the expression is recorded as a pure string.

When we are loading the schema, the expression is loaded as utf8mb4_bin collation. Therefore, it cannot pass the following condition in collectGenerateColumn:

if col != nil && col.GetType(ectx).PartialEqual(col.VirtualExpr.GetType(ectx), lp.SCtx().GetSessionVars().EnableUnsafeSubstitute) {
    exprToColumn[col.VirtualExpr] = col
}

The column type doesn't partial equal the type of col.VirtualExpr.

[YangKeao]

One possible fix is to re-build the expression with the user's session context, rather than using the expression in schema directly.

It would be easy to fix this situation, but IMO the best fix is to remove col.VirtualExpr, and make it a function to generate an expression from the user's session context. Cache is also needed to make it faster.