The partition prune result is not expected

[windtalker]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `settle_time` datetime NOT NULL DEFAULT '2100-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (TO_DAYS(`settle_time`))
(PARTITION `p20240603` VALUES LESS THAN (739406),
 PARTITION `p20240604` VALUES LESS THAN (739407),
 PARTITION `p20240605` VALUES LESS THAN (739408),
 PARTITION `p20240606` VALUES LESS THAN (739409),
 PARTITION `p20240607` VALUES LESS THAN (739410),
 PARTITION `p20240608` VALUES LESS THAN (739411),
 PARTITION `p20240609` VALUES LESS THAN (739412),
 PARTITION `p20240610` VALUES LESS THAN (739413),
 PARTITION `p20240611` VALUES LESS THAN (739414),
 PARTITION `p20240612` VALUES LESS THAN (739415)) |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set tidb_partition_prune_mode=static;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from test where settle_time >= '2024-06-07' and settle_time < '2024-06-08';
+------------------------------------+----------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------+
| id                                 | estRows  | task      | access object                   | operator info                                                                                                |                                                                +------------------------------------+----------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------+                                                                | HashAgg_13                         | 1.00     | root      |                                 | funcs:count(Column#5)->Column#4                                                                              |
| └─PartitionUnion_14                | 2.00     | root      |                                 |                                                                                                              |
|   ├─StreamAgg_29                   | 1.00     | root      |                                 | funcs:count(Column#7)->Column#5                                                                              |
|   │ └─TableReader_30               | 1.00     | root      |                                 | data:StreamAgg_18                                                                                            |
|   │   └─StreamAgg_18               | 1.00     | cop[tikv] |                                 | funcs:count(1)->Column#7                                                                                     |
|   │     └─Selection_28             | 250.00   | cop[tikv] |                                 | ge(test.test.settle_time, 2024-06-07 00:00:00.000000), lt(test.test.settle_time, 2024-06-08 00:00:00.000000) |
|   │       └─TableFullScan_27       | 10000.00 | cop[tikv] | table:test, partition:p20240607 | keep order:false, stats:pseudo                                                                               |
|   └─StreamAgg_48                   | 1.00     | root      |                                 | funcs:count(Column#9)->Column#5                                                                              |
|     └─TableReader_49               | 1.00     | root      |                                 | data:StreamAgg_37                                                                                            |
|       └─StreamAgg_37               | 1.00     | cop[tikv] |                                 | funcs:count(1)->Column#9                                                                                     |
|         └─Selection_47             | 250.00   | cop[tikv] |                                 | ge(test.test.settle_time, 2024-06-07 00:00:00.000000), lt(test.test.settle_time, 2024-06-08 00:00:00.000000) |
|           └─TableFullScan_46       | 10000.00 | cop[tikv] | table:test, partition:p20240608 | keep order:false, stats:pseudo                                                                               |
+------------------------------------+----------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

mysql> set tidb_partition_prune_mode=dynamic;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> explain select count(*) from test where settle_time >= '2024-06-07' and settle_time < '2024-06-08';
+------------------------------+----------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------+
| id                           | estRows  | task      | access object                 | operator info                                                                                                |
+------------------------------+----------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------+
| StreamAgg_20                 | 1.00     | root      |                               | funcs:count(Column#6)->Column#4                                                                              |
| └─TableReader_21             | 1.00     | root      | partition:p20240607,p20240608 | data:StreamAgg_9                                                                                             |
|   └─StreamAgg_9              | 1.00     | cop[tikv] |                               | funcs:count(1)->Column#6                                                                                     |
|     └─Selection_19           | 250.00   | cop[tikv] |                               | ge(test.test.settle_time, 2024-06-07 00:00:00.000000), lt(test.test.settle_time, 2024-06-08 00:00:00.000000) |
|       └─TableFullScan_18     | 10000.00 | cop[tikv] | table:test                    | keep order:false, stats:pseudo                                                                               |
+------------------------------+----------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

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

when the filter condition is settle_time >= '2024-06-07' and settle_time < '2024-06-08', only p20240607 should be used.

3. What did you see instead (Required)

Both p20240607 and p20240608 is used after partition prune, in both static and dynamic partition prune mode.

4. What is your TiDB version? (Required)

master @ 5598bdee8afc245d3fe5f49492d412a0ad29c255

[mjonss]

Seems related to #19941.

[windtalker]

19941

Any plan to fix this? Seems the bug is reported more than 2 years.