plan cache: the plan can be optimized when the range expression is an equal expression

[Reminiscent]

Enhancement

mysql> create table t(a int, b int, c int, index idx_ab(a, b));
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from t where a = 1 and b >= 2 and b <= 2;
+-------------------------------+---------+-----------+-----------------------------+-------------------------------------------------+
| id                            | estRows | task      | access object               | operator info                                   |
+-------------------------------+---------+-----------+-----------------------------+-------------------------------------------------+
| IndexLookUp_10                | 0.10    | root      |                             |                                                 |
| ├─IndexRangeScan_8(Build)     | 0.10    | cop[tikv] | table:t, index:idx_ab(a, b) | range:[1 2,1 2], keep order:false, stats:pseudo |
| └─TableRowIDScan_9(Probe)     | 0.10    | cop[tikv] | table:t                     | keep order:false, stats:pseudo                  |
+-------------------------------+---------+-----------+-----------------------------+-------------------------------------------------+
3 rows in set (0.00 sec)

mysql> prepare stmt from 'select * from t where a = ? and b >= ? and b <= ?;';
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=1, @b=2, @c=2;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a, @b, @c;
Empty set (0.00 sec)

mysql> explain for connection 2199023255955;
+---------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+--------+------+
| id                        | estRows  | actRows | task      | access object | execution info                                                                                                                    | operator info                                     | memory | disk |
+---------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+--------+------+
| Selection_8               | 0.01     | 0       | root      |               | time:3.46ms, loops:1                                                                                                              | eq(test.t.a, 1), ge(test.t.b, 2), le(test.t.b, 2) | N/A    | N/A  |
| └─TableReader_7           | 0.01     | 0       | root      |               | time:3.45ms, loops:1, cop_task: {num: 1, max: 411.2µs, proc_keys: 0, rpc_num: 2, rpc_time: 414.2µs, copr_cache_hit_ratio: 0.00}   | data:Selection_6                                  | N/A    | N/A  |
|   └─Selection_6           | 0.01     | 0       | cop[tikv] |               | tikv_task:{time:335.7µs, loops:0}                                                                                                 | eq(test.t.a, 1), ge(test.t.b, 2), le(test.t.b, 2) | N/A    | N/A  |
|     └─TableFullScan_5     | 10000.00 | 0       | cop[tikv] | table:t       | tikv_task:{time:335.7µs, loops:0}                                                                                                 | keep order:false, stats:pseudo                    | N/A    | N/A  |
+---------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+--------+------+
4 rows in set (0.00 sec)

Expected to get the indexRangeScan, but get the indexFullScan + selection.

[glkappe]

ding

[qw4990]

Already fixed by #38537