redundant selection operator when `between and` expression is used for datetime column
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
create table `tt1` (
id int,
`c1` datetime default null,
`c2` varchar(20) not null default '105',
`c3` varchar(10) not null,
key `i1` (`c1`,`c2`,`c3`)
);
explain delete from `tt1` where ((`c1` between '2025-08-05 21:30:08' and '2025-08-05 21:30:08') and ((`c1` >= '2025-08-05 21:30:00') and (`c1` < '2025-08-05 22:30:00')));
```
Actually `Selection_8` can be removed, because `IndeRangeScan_17` has smaller range, which will make `Selection_8` always true.
```
TiDB root@127.0.0.1:test> CREATE TABLE `tt1` (
-> id int,
-> `c1` datetime DEFAULT NULL,
-> `c2` varchar(20) NOT NULL DEFAULT '105',
-> `c3` varchar(10) NOT NULL,
-> KEY `i1` (`c1`,`c2`,`c3`)
-> );
Query OK, 0 rows affected
Time: 0.026s
TiDB root@127.0.0.1:test> EXPLAIN DELETE FROM `tt1` WHERE ((`c1` BETWEEN '2025-08-05 21:30:08' AND '2025-08-05 21:30:0
-> 8') AND ((`c1` >= '2025-08-05 21:30:00') AND (`c1` < '2025-08-05 22:30:00')));
+------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------------------------+
| Delete_3 | N/A | root | | N/A |
| └─IndexReader_9 | 10.00 | root | | index:Selection_8 |
| └─Selection_8 | 10.00 | cop[tikv] | | lt(test.tt1.c1, 2025-08-05 22:30:00.000000) |
| └─IndexRangeScan_7 | 10.00 | cop[tikv] | table:tt1, index:i1(c1, c2, c3) | range:[2025-08-05 21:30:08,2025-08-05 21:30:08], keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------------------------+
4 rows in set
Time: 0.007s
```
TiDB root@127.0.0.1:test> select tidb_version();
+-----------------------------------------------------------+
| tidb_version() |
+-----------------------------------------------------------+
| Release Version: v9.0.0-beta.2.pre-338-g93c90afb65-dirty |
| Edition: Community |
| Git Commit Hash: 93c90afb657fd02f91e194bf06a11b639d88ea79 |
| Git Branch: learn_planner |
| UTC Build Time: 2025-08-18 07:18:17 |
| GoVersion: go1.24.5 |
| Race Enabled: false |
| Check Table Before Drop: false |
| Store: tikv |
| Kernel Type: Classic |
+-----------------------------------------------------------+
1 row in set
Contributor guide
Assessment
This issue has not been assessed yet.