CAST which only changes the length of the string type column will cause full scan instead of index seek

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1(a varchar(100) primary key);
create table t2(a varchar(10) primary key);
create view v as select a from t1 union select a from t2;
explain select * from v where a='test';

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

+----------------------+---------+------+--------------------------------------+-------------------------------------------------------+
| id                   | estRows | task | access object                        | operator info                                         |
+----------------------+---------+------+--------------------------------------+-------------------------------------------------------+
| HashAgg_13           | 16.00   | root |                                      | group by:Column#3, funcs:firstrow(Column#3)->Column#3 |
| └─Union_14           | 20.00   | root |                                      |                                                       |
|   ├─Point_Get_16     | 1.00    | root | table:t1, clustered index:PRIMARY(a) |                                                       |
|   └─Point_Get_18     | 1.00    | root | table:t2, clustered index:PRIMARY(a) |                                                       |
+----------------------+---------+------+--------------------------------------+-------------------------------------------------------+

3. What did you see instead (Required)

+--------------------------------+----------+-----------+--------------------------------------+-------------------------------------------------------------------------------------+
| id                             | estRows  | task      | access object                        | operator info                                                                       |
+--------------------------------+----------+-----------+--------------------------------------+-------------------------------------------------------------------------------------+
| HashAgg_13                     | 6408.00  | root      |                                      | group by:Column#3, funcs:firstrow(Column#3)->Column#3                               |
| └─Union_14                     | 8010.00  | root      |                                      |                                                                                     |
|   ├─Point_Get_16               | 1.00     | root      | table:t1, clustered index:PRIMARY(a) |                                                                                     |
|   └─Projection_17              | 8000.00  | root      |                                      | cast(test.t2.a, varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#3   |
|     └─TableReader_20           | 8000.00  | root      |                                      | data:Selection_19                                                                   |
|       └─Selection_19           | 8000.00  | cop[tikv] |                                      | eq(cast(test.t2.a, varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin), "test") |
|         └─TableFullScan_18     | 10000.00 | cop[tikv] | table:t2                             | keep order:false, stats:pseudo                                                      |
+--------------------------------+----------+-----------+--------------------------------------+-------------------------------------------------------------------------------------+

4. What is your TiDB version? (Required)

master

[winoros]

The initial plan before predicate-push-down will be like Filter(a'='test')->Union{antoherChild, Projetion(cast(a, 100)->a')->Scan} and the projection's input column is a, output expression is cast(1, 100), as the new a'.
When we push down the filter a='test', we meet the projection and do column substitution. we see the a'='test' and then transform the a' to cast(a, 100) then push the converted filter down to the projection.

[winoros]

The cast(a, 100)->a' is needed.
Suppose that the length of the table column a is 4. The behavior of a = 'testtesttest' and cast(a as char(100)) = 'testtesttest' is different. The first one will truncate the const column while the second one enlarges the length of the column so no truncate will happen.

If we don't write UNION, just write where cast(a as char(10)) = 'testtesttest', we also cannot use index seek.

[winoros]

A possible solution is that we directly modify the ranger, detect the CASTs which only change the length, and construct an index range for them