Allow user to disable the limitation that index merge path won't be generated when normal index lookup path exists

[time-and-fate]

Enhancement

As said in the user doc, currently tidb won't generate an index merge path when a normal index lookup path exists.

If the optimizer can choose the single index scan method (other than full table scan) for a query plan, the optimizer will not automatically use index merge. For the optimizer to use index merge, you need to use the optimizer hint.

Now we plan to enable the user to disable this limitation through the optimizer fix control.

Example & Usage

use test;
create table t (a int, b int, c int, d int, index ia(a), index ib(b), index ic(c), index id(d));
explain select * from t where a > 1 and (b = 1 or c = 2 or d = 3);
explain select /*+ set_var(tidb_opt_fix_control='52869:on') */ * from t where a > 1 and (b = 1 or c = 2 or d = 3);
set @@tidb_opt_fix_control='52869:on';
explain select * from t where a > 1 and (b = 1 or c = 2 or d = 3);

> create table t (a int, b int, c int, d int, index ia(a), index ib(b), index ic(c), index id(d));
Query OK, 0 rows affected (0.12 sec)

> explain select * from t where a > 1 and (b = 1 or c = 2 or d = 3);
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+
| id                      | estRows  | task      | access object | operator info                                                                  |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+
| TableReader_7           | 9.99     | root      |               | data:Selection_6                                                               |
| └─Selection_6           | 9.99     | cop[tikv] |               | gt(test3.t.a, 1), or(eq(test3.t.b, 1), or(eq(test3.t.c, 2), eq(test3.t.d, 3))) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                 |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

> explain select /*+ set_var(tidb_opt_fix_control='52869:on') */ * from t where a > 1 and (b = 1 or c = 2 or d = 3);
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
| id                             | estRows | task      | access object        | operator info                               |
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
| IndexMerge_17                  | 9.99    | root      |                      | type: union                                 |
| ├─IndexRangeScan_12(Build)     | 10.00   | cop[tikv] | table:t, index:ib(b) | range:[1,1], keep order:false, stats:pseudo |
| ├─IndexRangeScan_13(Build)     | 10.00   | cop[tikv] | table:t, index:ic(c) | range:[2,2], keep order:false, stats:pseudo |
| ├─IndexRangeScan_14(Build)     | 10.00   | cop[tikv] | table:t, index:id(d) | range:[3,3], keep order:false, stats:pseudo |
| └─Selection_16(Probe)          | 9.99    | cop[tikv] |                      | gt(test3.t.a, 1)                            |
|   └─TableRowIDScan_15          | 29.97   | cop[tikv] | table:t              | keep order:false, stats:pseudo              |
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
6 rows in set (0.00 sec)

> set @@tidb_opt_fix_control='52869:on';
Query OK, 0 rows affected (0.00 sec)

> explain select * from t where a > 1 and (b = 1 or c = 2 or d = 3);
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
| id                             | estRows | task      | access object        | operator info                               |
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
| IndexMerge_17                  | 9.99    | root      |                      | type: union                                 |
| ├─IndexRangeScan_12(Build)     | 10.00   | cop[tikv] | table:t, index:ib(b) | range:[1,1], keep order:false, stats:pseudo |
| ├─IndexRangeScan_13(Build)     | 10.00   | cop[tikv] | table:t, index:ic(c) | range:[2,2], keep order:false, stats:pseudo |
| ├─IndexRangeScan_14(Build)     | 10.00   | cop[tikv] | table:t, index:id(d) | range:[3,3], keep order:false, stats:pseudo |
| └─Selection_16(Probe)          | 9.99    | cop[tikv] |                      | gt(test3.t.a, 1)                            |
|   └─TableRowIDScan_15          | 29.97   | cop[tikv] | table:t              | keep order:false, stats:pseudo              |
+--------------------------------+---------+-----------+----------------------+---------------------------------------------+
6 rows in set (0.00 sec)