Collation 'latin1_bin' Causes Changes in Query Execution Plan

[hawkingrei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(id int, col varchar(100), key ix(col)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

create table t1(id varchar(100)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

explain format="brief" select * from t use index(ix) where col in (select cast(id as char) from t1);


set collation_connection='latin1_bin';

explain format="brief" select * from t use index(ix) where col in (select cast(id as char) from t1);

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

they should be the same plan.

3. What did you see instead (Required)

explain format="brief" select * from t use index(ix) where col in (select cast(id as char) from t1);

+---------------------------+----------+-----------+------------------------+--------------------------------------------------------------------------------------------------------------+
| id                        | estRows  | task      | access object          | operator info                                                                                                |
+---------------------------+----------+-----------+------------------------+--------------------------------------------------------------------------------------------------------------+
| IndexHashJoin             | 8000.00  | root      |                        | inner join, inner:IndexLookUp, outer key:Column#6, inner key:test.t.col, equal cond:eq(Column#6, test.t.col) |
| ├─HashAgg(Build)          | 6400.00  | root      |                        | group by:Column#12, funcs:firstrow(Column#12)->Column#6                                                      |
| │ └─TableReader           | 6400.00  | root      |                        | data:HashAgg                                                                                                 |
| │   └─HashAgg             | 6400.00  | cop[tikv] |                        | group by:cast(test.t1.id, var_string(5)),                                                                    |
| │     └─Selection         | 8000.00  | cop[tikv] |                        | not(isnull(cast(test.t1.id, var_string(5))))                                                                 |
| │       └─TableFullScan   | 10000.00 | cop[tikv] | table:t1               | keep order:false, stats:pseudo                                                                               |
| └─IndexLookUp(Probe)      | 8000.00  | root      |                        |                                                                                                              |
|   ├─Selection(Build)      | 8000.00  | cop[tikv] |                        | not(isnull(test.t.col))                                                                                      |
|   │ └─IndexRangeScan      | 8008.01  | cop[tikv] | table:t, index:ix(col) | range: decided by [eq(test.t.col, Column#6)], keep order:false, stats:pseudo                                 |
|   └─TableRowIDScan(Probe) | 8000.00  | cop[tikv] | table:t                | keep order:false, stats:pseudo                                                                               |
+---------------------------+----------+-----------+------------------------+--------------------------------------------------------------------------------------------------------------+


set collation_connection='latin1_bin';

explain format="brief" select * from t use index(ix) where col in (select cast(id as char) from t1);

+---------------------------+----------+-----------+------------------------+----------------------------------------------+
| id                        | estRows  | task      | access object          | operator info                                |
+---------------------------+----------+-----------+------------------------+----------------------------------------------+
| HashJoin                  | 7992.00  | root      |                        | semi join, equal:[eq(test.t.col, Column#6)]  |
| ├─Projection(Build)       | 8000.00  | root      |                        | cast(test.t1.id, var_string(5))->Column#6    |
| │ └─TableReader           | 8000.00  | root      |                        | data:Selection                               |
| │   └─Selection           | 8000.00  | cop[tikv] |                        | not(isnull(cast(test.t1.id, var_string(5)))) |
| │     └─TableFullScan     | 10000.00 | cop[tikv] | table:t1               | keep order:false, stats:pseudo               |
| └─IndexLookUp(Probe)      | 9990.00  | root      |                        |                                              |
|   ├─IndexFullScan(Build)  | 9990.00  | cop[tikv] | table:t, index:ix(col) | keep order:false, stats:pseudo               |
|   └─TableRowIDScan(Probe) | 9990.00  | cop[tikv] | table:t                | keep order:false, stats:pseudo               |
+---------------------------+----------+-----------+------------------------+----------------------------------------------+

4. What is your TiDB version? (Required)

master

634e6ab