operator 'in' partition pruning doesn't take effect on date partition key

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1 (
`id` bigint(20)  NOT NULL AUTO_INCREMENT,
`dt` date,
PRIMARY KEY (`id`,`dt`)
)
PARTITION BY RANGE COLUMNS(dt) (
PARTITION `p20201125` VALUES LESS THAN ("20201126"),
PARTITION `p20201126` VALUES LESS THAN ("20201127"),
PARTITION `p20201127` VALUES LESS THAN ("20201128"),
PARTITION `p20201128` VALUES LESS THAN ("20201129"),
PARTITION `p20201129` VALUES LESS THAN ("20201130")
);

explain select count(1) from t1 where dt in ('2020-11-27','2020-11-28');

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

+-------------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------------+
| id                                  | estRows  | task      | access object                                        | operator info                                                          |
+-------------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------------+
| HashAgg_18                          | 1.00     | root      |                                                      | funcs:count(Column#5)->Column#4                                        |
| └─PartitionUnion_19                 | 5.00     | root      |                                                      |                                                                        |
|   ├─StreamAgg_99                    | 1.00     | root      |                                                      | funcs:count(Column#22)->Column#5                                       |
|   │ └─IndexReader_100               | 1.00     | root      |                                                      | index:StreamAgg_81                                                     |
|   │   └─StreamAgg_81                | 1.00     | cop[tikv] |                                                      | funcs:count(1)->Column#22                                              |
|   │     └─Selection_98              | 20.00    | cop[tikv] |                                                      | in(test.t1.dt, 2020-11-27 00:00:00.000000, 2020-11-28 00:00:00.000000) |
|   │       └─IndexFullScan_97        | 10000.00 | cop[tikv] | table:t1, partition:p20201127, index:PRIMARY(id, dt) | keep order:false, stats:pseudo                                         |
|   └─StreamAgg_128                   | 1.00     | root      |                                                      | funcs:count(Column#28)->Column#5                                       |
|     └─IndexReader_129               | 1.00     | root      |                                                      | index:StreamAgg_110                                                    |
|       └─StreamAgg_110               | 1.00     | cop[tikv] |                                                      | funcs:count(1)->Column#28                                              |
|         └─Selection_127             | 20.00    | cop[tikv] |                                                      | in(test.t1.dt, 2020-11-27 00:00:00.000000, 2020-11-28 00:00:00.000000) |
|           └─IndexFullScan_126       | 10000.00 | cop[tikv] | table:t1, partition:p20201128, index:PRIMARY(id, dt) | keep order:false, stats:pseudo                                         |
+-------------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------------+

3. What did you see instead (Required)

+-------------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------------+
| id                                  | estRows  | task      | access object                                        | operator info                                                          |
+-------------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------------+
| HashAgg_18                          | 1.00     | root      |                                                      | funcs:count(Column#5)->Column#4                                        |
| └─PartitionUnion_19                 | 5.00     | root      |                                                      |                                                                        |
|   ├─StreamAgg_41                    | 1.00     | root      |                                                      | funcs:count(Column#10)->Column#5                                       |
|   │ └─IndexReader_42                | 1.00     | root      |                                                      | index:StreamAgg_23                                                     |
|   │   └─StreamAgg_23                | 1.00     | cop[tikv] |                                                      | funcs:count(1)->Column#10                                              |
|   │     └─Selection_40              | 20.00    | cop[tikv] |                                                      | in(test.t1.dt, 2020-11-27 00:00:00.000000, 2020-11-28 00:00:00.000000) |
|   │       └─IndexFullScan_39        | 10000.00 | cop[tikv] | table:t1, partition:p20201125, index:PRIMARY(id, dt) | keep order:false, stats:pseudo                                         |
|   ├─StreamAgg_70                    | 1.00     | root      |                                                      | funcs:count(Column#16)->Column#5                                       |
|   │ └─IndexReader_71                | 1.00     | root      |                                                      | index:StreamAgg_52                                                     |
|   │   └─StreamAgg_52                | 1.00     | cop[tikv] |                                                      | funcs:count(1)->Column#16                                              |
|   │     └─Selection_69              | 20.00    | cop[tikv] |                                                      | in(test.t1.dt, 2020-11-27 00:00:00.000000, 2020-11-28 00:00:00.000000) |
|   │       └─IndexFullScan_68        | 10000.00 | cop[tikv] | table:t1, partition:p20201126, index:PRIMARY(id, dt) | keep order:false, stats:pseudo                                         |
|   ├─StreamAgg_99                    | 1.00     | root      |                                                      | funcs:count(Column#22)->Column#5                                       |
|   │ └─IndexReader_100               | 1.00     | root      |                                                      | index:StreamAgg_81                                                     |
|   │   └─StreamAgg_81                | 1.00     | cop[tikv] |                                                      | funcs:count(1)->Column#22                                              |
|   │     └─Selection_98              | 20.00    | cop[tikv] |                                                      | in(test.t1.dt, 2020-11-27 00:00:00.000000, 2020-11-28 00:00:00.000000) |
|   │       └─IndexFullScan_97        | 10000.00 | cop[tikv] | table:t1, partition:p20201127, index:PRIMARY(id, dt) | keep order:false, stats:pseudo                                         |
|   ├─StreamAgg_128                   | 1.00     | root      |                                                      | funcs:count(Column#28)->Column#5                                       |
|   │ └─IndexReader_129               | 1.00     | root      |                                                      | index:StreamAgg_110                                                    |
|   │   └─StreamAgg_110               | 1.00     | cop[tikv] |                                                      | funcs:count(1)->Column#28                                              |
|   │     └─Selection_127             | 20.00    | cop[tikv] |                                                      | in(test.t1.dt, 2020-11-27 00:00:00.000000, 2020-11-28 00:00:00.000000) |
|   │       └─IndexFullScan_126       | 10000.00 | cop[tikv] | table:t1, partition:p20201128, index:PRIMARY(id, dt) | keep order:false, stats:pseudo                                         |
|   └─StreamAgg_157                   | 1.00     | root      |                                                      | funcs:count(Column#34)->Column#5                                       |
|     └─IndexReader_158               | 1.00     | root      |                                                      | index:StreamAgg_139                                                    |
|       └─StreamAgg_139               | 1.00     | cop[tikv] |                                                      | funcs:count(1)->Column#34                                              |
|         └─Selection_156             | 20.00    | cop[tikv] |                                                      | in(test.t1.dt, 2020-11-27 00:00:00.000000, 2020-11-28 00:00:00.000000) |
|           └─IndexFullScan_155       | 10000.00 | cop[tikv] | table:t1, partition:p20201129, index:PRIMARY(id, dt) | keep order:false, stats:pseudo                                         |
+-------------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------------+

4. What is your TiDB version? (Required)

v5.1.0

[qw4990]

Since it doesn't affect the correctness, I convert it to an Enhancement.

[qw4990]

The root cause is that the partition pruner of range-columns doesn't support the expression IN. https://github.com/pingcap/tidb/blob/master/planner/core/rule_partition_processor.go#L960

[qw4990]

I'm working on this issue now.