'explain for connection' result wrong for partition table

[tiancaiamao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> show create table xx;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| xx    | CREATE TABLE `xx` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH( `id` )
PARTITIONS 10 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from xx where id = 1;
Empty set (0.01 sec)

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

mysql> explain select * from xx where id = 1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      | partition:p1  | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.xx.id, 1)              |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:xx      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> select * from xx where id = 1;
Empty set (0.00 sec)

mysql> explain for connection 1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.xx.id, 1)              |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:xx      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

2. What did you expect to see? (Required)

+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      | partition:p1  | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.xx.id, 1)              |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:xx      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+

3. What did you see instead (Required)

+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.xx.id, 1)              |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:xx      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+

Note the access object partition:p1, it should be there.

4. What is your TiDB version? (Required)

master branch f699bd8

Release Version: v4.0.0-beta.2-1230-gf699bd81a-dirty
Edition: Community
Git Commit Hash: f699bd81a24fafca6f96ea04fb5095bd9047c2ff
Git Branch: master
UTC Build Time: 2020-09-15 17:15:27
GoVersion: go1.15
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false 

This bug is introduced after the partition table refactoring.

[tiancaiamao]

I find that set process info seems to cache the plan explain info.

After executing a SQL, session.SetProcessInfo is called, and at that time,
ctx is not set, so there is no partition pruning information in the explain info.

When running explain for connection, the cached explain info is used, with no partition pruning information.

[tiancaiamao]

The problem exists after set @@tidb_partition_prune_mode = 'dynamic-only' which is not the default configuration.
So I change the label to severity/minor.

[qw4990]

Hi, @mahjonp , would you like to take a look at this issue?

[mjonss]

This seems to be fixed in v5.4.0:

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

tidb> select * from xx where id = 1;
Empty set (0,00 sec)

tidb> explain for connection 5;
+-------------------------+----------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
| id                      | estRows  | actRows | task      | access object          | execution info                                                                                                                                                                                                                          | operator info                  | memory | disk |
+-------------------------+----------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
| TableReader_9           | 10.00    | 0       | root      |                        | time:1.3ms, loops:1, cop_task: {num: 1, max: 1.16ms, proc_keys: 0, rpc_num: 1, rpc_time: 1.13ms, copr_cache_hit_ratio: 0.00}                                                                                                            | data:Selection_8               | N/A    | N/A  |
| └─Selection_8           | 10.00    | 0       | cop[tikv] |                        | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 0, total_process_keys_size: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | eq(test.xx.id, 1)              | N/A    | N/A  |
|   └─TableFullScan_7     | 10000.00 | 0       | cop[tikv] | table:xx, partition:p1 | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                            | keep order:false, stats:pseudo | N/A    | N/A  |
+-------------------------+----------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
3 rows in set (0,00 sec)

tidb> explain select * from xx where id = 1;
+-------------------------+----------+-----------+------------------------+--------------------------------+
| id                      | estRows  | task      | access object          | operator info                  |
+-------------------------+----------+-----------+------------------------+--------------------------------+
| TableReader_9           | 10.00    | root      |                        | data:Selection_8               |
| └─Selection_8           | 10.00    | cop[tikv] |                        | eq(test.xx.id, 1)              |
|   └─TableFullScan_7     | 10000.00 | cop[tikv] | table:xx, partition:p1 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+------------------------+--------------------------------+
3 rows in set (0,00 sec)

[mjonss]

/close

[ti-chi-bot]

@mjonss: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[xuyifangreeneyes]

@mjonss I tried it on the latest master and it seemed lose partition:p1 for explain for connection:

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

mysql> explain select * from xx where id = 1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      | partition:p1  | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.xx.id, 1)              |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:xx      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> select * from xx where id = 1;
Empty set (0.00 sec)

mysql> explain for connection 3;
+-------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
| id                      | estRows  | actRows | task      | access object | execution info                                                                                                                      | operator info                  | memory | disk |
+-------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
| TableReader_7           | 10.00    | 0       | root      |               | time:328.5µs, loops:1, cop_task: {num: 1, max: 222.5µs, proc_keys: 0, rpc_num: 1, rpc_time: 203.8µs, copr_cache_hit_ratio: 0.00}    | data:Selection_6               | N/A    | N/A  |
| └─Selection_6           | 10.00    | 0       | cop[tikv] |               | tikv_task:{time:181.3µs, loops:0}                                                                                                   | eq(test.xx.id, 1)              | N/A    | N/A  |
|   └─TableFullScan_5     | 10000.00 | 0       | cop[tikv] | table:xx      | tikv_task:{time:181.3µs, loops:0}                                                                                                   | keep order:false, stats:pseudo | N/A    | N/A  |
+-------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
3 rows in set (0.00 sec)