planner: unexpected FullScan when using prepared plan cache #38533
Description
Enhancement
prepare st from "select /*+ use_index(t, a) */ a from t where a=? and a=?";
-- prepare st from "select /*+ use_index(t, a) */ a from t where a>=? and a<=?";
set @a=1;
execute st using @a, @a;
The Plan:
+---------------------------+----------+---------+-----------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+--------+------+
| Selection_8 | 10.00 | 0 | root | | time:820.7µs, loops:1 | eq(test.t.a, 1), eq(test.t.a, 1) | N/A | N/A |
| └─IndexReader_7 | 10.00 | 0 | root | | time:816.9µs, loops:1, cop_task: {num: 1, max: 677.4µs, proc_keys: 0, rpc_num: 1, rpc_time: 593.4µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | index:Selection_6 | N/A | N/A |
| └─Selection_6 | 10.00 | 0 | cop[tikv] | | tikv_task:{time:533.6µs, loops:0} | eq(test.t.a, 1), eq(test.t.a, 1) | N/A | N/A |
| └─IndexFullScan_5 | 10000.00 | 0 | cop[tikv] | table:t, index:a(a) | tikv_task:{time:533.6µs, loops:0} | keep order:false, stats:pseudo | N/A | N/A |
+---------------------------+----------+---------+-----------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+--------+------+
The reason is to avoid invalid plans, the optimizer skips to build ranges for prepared statements in this case. (see https://github.com/pingcap/tidb/blob/master/util/ranger/detacher.go#L581)