where conditions with virtual columns get lost in the query plan #30965
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL,
KEY `ib` (`b`)
);
insert into t (a,b,c) value(3,4,5);explain select count(*) from t where d = 2 and b = 4 and a = 3 and c = 5;2. What did you expect to see? (Required)
All four where conditions are in the execution plan.
> select count(*) from t where d = 2 and b = 4 and a = 3 and c = 5;
+----------+
| count(*) |
+----------+
| 0 |
+----------+3. What did you see instead (Required)
The d = 2 is lost.
+----------------------------------+---------+-----------+----------------------+---------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+----------------------+---------------------------------------------------+
| StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#6 |
| └─IndexLookUp_26 | 0.00 | root | | |
| ├─IndexRangeScan_23(Build) | 10.00 | cop[tikv] | table:t, index:ib(b) | range:[4,4], keep order:false, stats:pseudo |
| └─Selection_25(Probe) | 0.00 | cop[tikv] | | eq(test.t.a, 3), eq(test.t.c, 5), eq(test.t.c, 5) |
| └─TableRowIDScan_24 | 10.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+----------------------+---------------------------------------------------+
> select count(*) from t where d = 2 and b = 4 and a = 3 and c = 5;
+----------+
| count(*) |
+----------+
| 1 |
+----------+4. What is your TiDB version? (Required)
from 4.0.0 to master