planner: part of union stmt can't use index

[wshwsh12]

Feature Request

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

drop table t1;
drop table t2;
create table t1(a int,index idx(a));
create table t2(a bigint,index idx(a));
create view tmp as (select * from t1 union all select * from t2);
desc select * from tmp where a = 1;

2. What did you expect to see?

Both t1 and t2 table can use index.

3. What did you see instead?

tidb> desc select * from tmp where a = 1;
+--------------------------------+----------+-----------+------------------------+----------------------------------------------+
| id                             | estRows  | task      | access object          | operator info                                |
+--------------------------------+----------+-----------+------------------------+----------------------------------------------+
| Union_12                       | 8010.00  | root      |                        |                                              |
| ├─Projection_13                | 8000.00  | root      |                        | cast(test.t1.a, bigint(20) BINARY)->Column#5 |
| │ └─TableReader_16             | 8000.00  | root      |                        | data:Selection_15                            |
| │   └─Selection_15             | 8000.00  | cop[tikv] |                        | eq(cast(test.t1.a), 1)                       |
| │     └─TableFullScan_14       | 10000.00 | cop[tikv] | table:t1               | keep order:false, stats:pseudo               |
| └─IndexReader_22               | 10.00    | root      |                        | index:IndexRangeScan_21                      |
|   └─IndexRangeScan_21          | 10.00    | cop[tikv] | table:t2, index:idx(a) | range:[1,1], keep order:false, stats:pseudo  |
+--------------------------------+----------+-----------+------------------------+----------------------------------------------+
7 rows in set (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

master

[lzmhhh123]

I think it's not a bug. Because the union needs all columns from each child to have the same return type. The int column should be converted to big int by default. Then the equal condition also should match the cast function, otherwise if truncates happen, the query may get the wrong result.

[lzmhhh123]

@wshwsh12 If you agree with me, please close the issue.

[wshwsh12]

@lzmhhh123 Maybe it can be a feature? Mysql 8.0 can use index in this situation

[lzmhhh123]

@lzmhhh123 Maybe it can be a feature? Mysql 8.0 can use index in this situation

Yes, but we should check whether this is a truncated situation happen. In this case, 1 never be truncated when int convert to bigint.

[lzmhhh123]

This issue type should be type/enhancement.