planner: unexpected FullScan when using prepared plan cache

[qw4990]

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)

[qw4990]

Compared with skipping the optimization and then caching a bad plan, maybe we should process this optimization to generate an optimal plan and not cache it.

[time-and-fate]

A relevant but different issue: #34561