Deprecate and remove table partition in optimize hint

[mjonss]

Enhancement

This was added to resolve some confusion in https://github.com/pingcap/tidb/issues/17113 but with dynamic prune mode it does not make any sense any longer, since all partitions will be using the same Index or Table reader, so just specifying the table should be enough, no need to support hints per partition.

Example where it does not work:

tidb> create table t (a int, b int, c varchar(20), primary key(a), key(b), key(c)) partition by range columns(a) (partition p0 values less than(6), partition p1 values less than(11), partition p2 values less than(16));
Query OK, 0 rows affected (0.01 sec)

tidb> insert into t values (1,1,"1"), (2,2,"2"), (8,8,"8"), (11,11,"11"), (15,15,"15");
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

tidb> set @@tidb_enable_index_merge = off;
Query OK, 0 rows affected (0.00 sec)

tidb> select /*+ use_index(t partition(p0, p1) b, c) */ * from t partition(p1,p2);
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 11 |   11 | 11   |
| 15 |   15 | 15   |
|  8 |    8 | 8    |
+----+------+------+
3 rows in set, 1 warning (0.00 sec)

tidb> explain select /*+ use_index(t partition(p0, p1) b, c) */ * from t partition(p1,p2);
+-----------------------------------+---------+-----------+-----------------------------------+--------------------------------+
| id                                | estRows | task      | access object                     | operator info                  |
+-----------------------------------+---------+-----------+-----------------------------------+--------------------------------+
| PartitionUnion_7                  | 3.00    | root      |                                   |                                |
| ├─IndexLookUp_10                  | 1.00    | root      |                                   |                                |
| │ ├─IndexFullScan_8(Build)        | 1.00    | cop[tikv] | table:t, partition:p1, index:b(b) | keep order:false, stats:pseudo |
| │ └─TableRowIDScan_9(Probe)       | 1.00    | cop[tikv] | table:t, partition:p1             | keep order:false, stats:pseudo |
| └─TableReader_15                  | 2.00    | root      |                                   | data:TableFullScan_14          |
|   └─TableFullScan_14              | 2.00    | cop[tikv] | table:t, partition:p2             | keep order:false, stats:pseudo |
+-----------------------------------+---------+-----------+-----------------------------------+--------------------------------+
6 rows in set, 1 warning (0.00 sec)

tidb> show warnings;
+---------+------+--------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                              |
+---------+------+--------------------------------------------------------------------------------------+
| Warning | 1105 | Unknown partitions (p0) in optimizer hint /*+ USE_INDEX(t PARTITION(p0, p1) b, c) */ |
+---------+------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

tidb> set @@tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

tidb> explain select /*+ use_index(t partition(p0, p1) b, c) */ * from t partition(p1,p2);
+-------------------------------+----------+-----------+---------------------+--------------------------------+
| id                            | estRows  | task      | access object       | operator info                  |
+-------------------------------+----------+-----------+---------------------+--------------------------------+
| IndexLookUp_6                 | 10000.00 | root      | partition:p1,p2     |                                |
| ├─IndexFullScan_4(Build)      | 10000.00 | cop[tikv] | table:t, index:b(b) | keep order:false, stats:pseudo |
| └─TableRowIDScan_5(Probe)     | 10000.00 | cop[tikv] | table:t             | keep order:false, stats:pseudo |
+-------------------------------+----------+-----------+---------------------+--------------------------------+
3 rows in set (0.00 sec)

tidb> show warnings;
Empty set (0.00 sec)

[Defined2014]

According pingcap/parser#880, this syntax used for resolve #17113 and never mentioned in docs. I think we could remove it directly.