planner: use dynamic mode to access partitioning tables when no global stats

[qw4990]

Enhancement

Support to use set @@tidb_opt_fix_control = "44262:ON" to control whether to use dynamic-mode to access partitioning tables without global-stats.

Currently, if a partitioning table has no global stats, the optimizer can not use dynamic mode to access this table, which can cause some suboptimal plans.
This behavior is not flexible. It's better to allow the user to specify the optimizer to use dynamic mode without global stats.

See the example below, the optimizer can not use IndexJoin since t2 has no global stats:

mysql> create table t1 (a int, b int);
Query OK, 0 rows affected (0.05 sec)

mysql> create table t2_part (a int, b int, key(a)) partition by hash(a) partitions 4;
Query OK, 0 rows affected (0.04 sec)

mysql> explain select /*+ TIDB_INLJ(t2_part@sel_2) */ * from t1 where t1.b<10 and not exists (
    ->     select 1 from t2_part where t1.a=t2_part.a and t2_part.b<20
    -> );
+----------------------------------+----------+-----------+-----------------------------+-------------------------------------------------------+
| id                               | estRows  | task      | access object               | operator info                                         |
+----------------------------------+----------+-----------+-----------------------------+-------------------------------------------------------+
| HashJoin_15                      | 2658.67  | root      |                             | anti semi join, equal:[eq(test.t1.a, test.t2_part.a)] |
| ├─PartitionUnion_19(Build)       | 13293.33 | root      |                             |                                                       |
| │ ├─TableReader_22               | 3323.33  | root      |                             | data:Selection_21                                     |
| │ │ └─Selection_21               | 3323.33  | cop[tikv] |                             | lt(test.t2_part.b, 20)                                |
| │ │   └─TableFullScan_20         | 10000.00 | cop[tikv] | table:t2_part, partition:p0 | keep order:false, stats:pseudo                        |
| │ ├─TableReader_25               | 3323.33  | root      |                             | data:Selection_24                                     |
| │ │ └─Selection_24               | 3323.33  | cop[tikv] |                             | lt(test.t2_part.b, 20)                                |
| │ │   └─TableFullScan_23         | 10000.00 | cop[tikv] | table:t2_part, partition:p1 | keep order:false, stats:pseudo                        |
| │ ├─TableReader_28               | 3323.33  | root      |                             | data:Selection_27                                     |
| │ │ └─Selection_27               | 3323.33  | cop[tikv] |                             | lt(test.t2_part.b, 20)                                |
| │ │   └─TableFullScan_26         | 10000.00 | cop[tikv] | table:t2_part, partition:p2 | keep order:false, stats:pseudo                        |
| │ └─TableReader_31               | 3323.33  | root      |                             | data:Selection_30                                     |
| │   └─Selection_30               | 3323.33  | cop[tikv] |                             | lt(test.t2_part.b, 20)                                |
| │     └─TableFullScan_29         | 10000.00 | cop[tikv] | table:t2_part, partition:p3 | keep order:false, stats:pseudo                        |
| └─TableReader_18(Probe)          | 3323.33  | root      |                             | data:Selection_17                                     |
|   └─Selection_17                 | 3323.33  | cop[tikv] |                             | lt(test.t1.b, 10)                                     |
|     └─TableFullScan_16           | 10000.00 | cop[tikv] | table:t1                    | keep order:false, stats:pseudo                        |
+----------------------------------+----------+-----------+-----------------------------+-------------------------------------------------------+
17 rows in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1105 | disable dynamic pruning due to t2_part has no global stats |
| Warning | 1815 | Optimizer Hint INL_JOIN or TIDB_INLJ is inapplicable       |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> analyze table t2_part;
Query OK, 0 rows affected, 4 warnings (0.07 sec)

mysql> explain select /*+ TIDB_INLJ(t2_part@sel_2) */ * from t1 where t1.b<10 and not exists (     select 1 from t2_part where t1.a=t2_part.a and t2_part.b<20 );
+---------------------------------+----------+-----------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| id                              | estRows  | task      | access object             | operator info                                                                                                                 |
+---------------------------------+----------+-----------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_13                    | 2658.67  | root      |                           | anti semi join, inner:IndexLookUp_12, outer key:test.t1.a, inner key:test.t2_part.a, equal cond:eq(test.t1.a, test.t2_part.a) |
| ├─TableReader_18(Build)         | 3323.33  | root      |                           | data:Selection_17                                                                                                             |
| │ └─Selection_17                | 3323.33  | cop[tikv] |                           | lt(test.t1.b, 10)                                                                                                             |
| │   └─TableFullScan_16          | 10000.00 | cop[tikv] | table:t1                  | keep order:false, stats:pseudo                                                                                                |
| └─IndexLookUp_12(Probe)         | 4154.17  | root      | partition:all             |                                                                                                                               |
|   ├─IndexRangeScan_9(Build)     | 12500.00 | cop[tikv] | table:t2_part, index:a(a) | range: decided by [eq(test.t2_part.a, test.t1.a)], keep order:false, stats:pseudo                                             |
|   └─Selection_11(Probe)         | 4154.17  | cop[tikv] |                           | lt(test.t2_part.b, 20)                                                                                                        |
|     └─TableRowIDScan_10         | 12500.00 | cop[tikv] | table:t2_part             | keep order:false, stats:pseudo                                                                                                |
+---------------------------------+----------+-----------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)