The partition table scan reader missing the point get

[winoros]

Enhancement

CREATE TABLE tkey0 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 VARCHAR(12) NOT NULL, col4 INT NOT NULL,UNIQUE KEY (col3)) PARTITION BY KEY(col3) PARTITIONS 4;
INSERT INTO tkey0 VALUES(1, '2023-02-22', 'linpin', 1), (2, '2023-02-22', 'zhangsan', 2), (3, '2023-02-22', 'anqila', 3), (4, '2023-02-22', 'xingtian', 4),(1, '2023-02-22', 'renleifeng', 5), (2, '2023-02-22', 'peilin', 2),(1, '2023-02-22', 'abcdeeg', 7), (2, '2023-02-22', 'rpstdfed', 8);

explain SELECT count(*) FROM tkey0 WHERE col3 = 'zhangsan' or col3 = 'linpin';

create table tkey1 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 VARCHAR(12) NOT NULL, col4 INT NOT NULL,UNIQUE KEY (col3)) ;

explain SELECT count(*) FROM tkey1 WHERE col3 = 'zhangsan' or col3 = 'linpin';

mysql> explain SELECT count(*) FROM tkey6 WHERE col3 = 'zhangsan' or col3 = 'linpin';
+-----------------------------+---------+-----------+-------------------------------+----------------------------------------------------------------------+
| id                          | estRows | task      | access object                 | operator info                                                        |
+-----------------------------+---------+-----------+-------------------------------+----------------------------------------------------------------------+
| StreamAgg_17                | 1.00    | root      |                               | funcs:count(Column#8)->Column#6                                      |
| └─IndexReader_18            | 1.00    | root      | partition:p2,p3               | index:StreamAgg_9                                                    |
|   └─StreamAgg_9             | 1.00    | cop[tikv] |                               | funcs:count(1)->Column#8                                             |
|     └─IndexRangeScan_16     | 2.00    | cop[tikv] | table:tkey6, index:col3(col3) | range:["linpin","linpin"], ["zhangsan","zhangsan"], keep order:false |
+-----------------------------+---------+-----------+-------------------------------+----------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> explain SELECT count(*) FROM tkey1 WHERE col3 = 'zhangsan' or col3 = 'linpin';
+--------------------------+---------+------+-------------------------------+------------------------------+
| id                       | estRows | task | access object                 | operator info                |
+--------------------------+---------+------+-------------------------------+------------------------------+
| StreamAgg_9              | 1.00    | root |                               | funcs:count(1)->Column#6     |
| └─Batch_Point_Get_13     | 2.00    | root | table:tkey1, index:col3(col3) | keep order:false, desc:false |
+--------------------------+---------+------+-------------------------------+------------------------------+
2 rows in set (0.00 sec)

[Defined2014]

A previous issue link #45532

[jiyfhust]

After pr #51405 merged, the batchpoint can be used by set the session variables:

set session tidb_skip_missing_partition_stats=0;
set session tidb_opt_fix_control = "";

That is:

mysql> CREATE TABLE tkey0 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 VARCHAR(12) NOT NULL, col4 INT NOT NULL,UNIQUE KEY (col3)) PARTITION BY KEY(col3) PARTITIONS 4;
INSERT INTO tkey0 VALUES(1, '2023-02-22', 'linpin', 1), (2, '2023-02-22', 'zhangsan', 2), (3, '2023-02-22', 'anqila', 3), (4, '2023-02-22', 'xingtian', 4),(1, '2023-02-22', 'renleifeng', 5), (2, '2023-02-22', 'peilin', 2),(1, '2023-02-22', 'abcdeeg', 7), (2, '2023-02-22', 'rpstdfed', 8);Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO tkey0 VALUES(1, '2023-02-22', 'linpin', 1), (2, '2023-02-22', 'zhangsan', 2), (3, '2023-02-22', 'anqila', 3), (4, '2023-02-22', 'xingtian', 4),(1, '2023-02-22', 'renleifeng', 5), (2, '2023-02-22', 'peilin', 2),(1, '2023-02-22', 'abcdeeg', 7), (2, '2023-02-22', 'rpstdfed', 8);
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> set session tidb_skip_missing_partition_stats=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set session tidb_opt_fix_control = "";
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT count(*) FROM tkey0 WHERE col3 = 'zhangsan' or col3 = 'linpin';
+--------------------------------+---------+------+-------------------------------+---------------------------------+
| id                             | estRows | task | access object                 | operator info                   |
+--------------------------------+---------+------+-------------------------------+---------------------------------+
| HashAgg_12                     | 1.00    | root |                               | funcs:count(Column#7)->Column#6 |
| └─PartitionUnion_13            | 2.00    | root |                               |                                 |
|   ├─StreamAgg_17               | 1.00    | root |                               | funcs:count(1)->Column#7        |
|   │ └─Batch_Point_Get_21       | 2.00    | root | table:tkey0, index:col3(col3) | keep order:false, desc:false    |
|   └─StreamAgg_26               | 1.00    | root |                               | funcs:count(1)->Column#7        |
|     └─Batch_Point_Get_30       | 2.00    | root | table:tkey0, index:col3(col3) | keep order:false, desc:false    |
+--------------------------------+---------+------+-------------------------------+---------------------------------+
6 rows in set, 1 warning (0.01 sec)

[winoros]

After pr #51405 merged, the batchpoint can be used by set the session variables:

set session tidb_skip_missing_partition_stats=0;
set session tidb_opt_fix_control = "";

That is:

mysql> CREATE TABLE tkey0 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 VARCHAR(12) NOT NULL, col4 INT NOT NULL,UNIQUE KEY (col3)) PARTITION BY KEY(col3) PARTITIONS 4;
INSERT INTO tkey0 VALUES(1, '2023-02-22', 'linpin', 1), (2, '2023-02-22', 'zhangsan', 2), (3, '2023-02-22', 'anqila', 3), (4, '2023-02-22', 'xingtian', 4),(1, '2023-02-22', 'renleifeng', 5), (2, '2023-02-22', 'peilin', 2),(1, '2023-02-22', 'abcdeeg', 7), (2, '2023-02-22', 'rpstdfed', 8);Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO tkey0 VALUES(1, '2023-02-22', 'linpin', 1), (2, '2023-02-22', 'zhangsan', 2), (3, '2023-02-22', 'anqila', 3), (4, '2023-02-22', 'xingtian', 4),(1, '2023-02-22', 'renleifeng', 5), (2, '2023-02-22', 'peilin', 2),(1, '2023-02-22', 'abcdeeg', 7), (2, '2023-02-22', 'rpstdfed', 8);
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> set session tidb_skip_missing_partition_stats=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set session tidb_opt_fix_control = "";
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT count(*) FROM tkey0 WHERE col3 = 'zhangsan' or col3 = 'linpin';
+--------------------------------+---------+------+-------------------------------+---------------------------------+
| id                             | estRows | task | access object                 | operator info                   |
+--------------------------------+---------+------+-------------------------------+---------------------------------+
| HashAgg_12                     | 1.00    | root |                               | funcs:count(Column#7)->Column#6 |
| └─PartitionUnion_13            | 2.00    | root |                               |                                 |
|   ├─StreamAgg_17               | 1.00    | root |                               | funcs:count(1)->Column#7        |
|   │ └─Batch_Point_Get_21       | 2.00    | root | table:tkey0, index:col3(col3) | keep order:false, desc:false    |
|   └─StreamAgg_26               | 1.00    | root |                               | funcs:count(1)->Column#7        |
|     └─Batch_Point_Get_30       | 2.00    | root | table:tkey0, index:col3(col3) | keep order:false, desc:false    |
+--------------------------------+---------+------+-------------------------------+---------------------------------+
6 rows in set, 1 warning (0.01 sec)

@jiyfhust
That plan is not expected to be generated by default. It's not related to this thread.

Set tidb_skip_missing_partition_stats to 0 is not an encouraged behavior.

[winoros]

A previous issue link #45532

Wow, i closed this one since there's already one.