about RANGE COLUMNS partitioning:partition clipping does not take effect

[WholeWorld-Timothy]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1.create table like:

CREATE TABLE test.table_day (
summ_date varchar (8) NOT NULL
)
PARTITION BY RANGE COLUMNS (summ_date)
( PARTITION prt_before VALUES LESS THAN (“20210101”),
PARTITION prt_20210101 VALUES LESS THAN (“20210102”),
PARTITION prt_20210102 VALUES LESS THAN (“20210103”))

2. execute sql like:
   explain select * from test.table_day where summ_date in (“20210101”,“20210102”);

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

id                    |estRows |task     |access object                          |operator info                                       |
----------------------+--------+---------+---------------------------------------+----------------------------------------------------+
PartitionUnion_9      |60.00   |root     |                                       |                                                    |
├─TableReader_12      |20.00   |root     |                                       |data:Selection_11                                   |
│ └─Selection_11      |20.00   |cop[tikv]|                                       |in(test.table_day.summ_date, "20210101", "20210102")|
│   └─TableFullScan_10|10000.00|cop[tikv]|table:table_day, partition:prt_before  |keep order:false, stats:pseudo                      |
├─TableReader_15      |20.00   |root     |                                       |data:Selection_14                                   |
│ └─Selection_14      |20.00   |cop[tikv]|                                       |in(test.table_day.summ_date, "20210101", "20210102")|
│   └─TableFullScan_13|10000.00|cop[tikv]|table:table_day, partition:prt_20210101|keep order:false, stats:pseudo                      |
└─TableReader_18      |20.00   |root     |                                       |data:Selection_17                                   |
  └─Selection_17      |20.00   |cop[tikv]|                                       |in(test.table_day.summ_date, "20210101", "20210102")|
    └─TableFullScan_16|10000.00|cop[tikv]|table:table_day, partition:prt_20210102|keep order:false, stats:pseudo                      |

All partitions are scanned

3. What did you see instead (Required)

partition clipping is performed at query time

4. What is your TiDB version? (Required)

5.4.0

[mjonss]

The issue does not affect int partitioning columns and seems only to be affecting IN with multiple values.

[mjonss]

Only int and time type pruning for IN is currently implemented for RANGE COLUMNS partitioning.

[WholeWorld-Timothy]

Can partition clipping be implemented for VARCHAR or other string types for RANGE COLUMNS partitioning?There are a number of cases in our enterprise that require this implementation.

[mjonss]

@WholeWorld-Timothy yes it can be implemented, it is fairly simple to do, but it also needs testing with different collations etc.

What character set and collation do you use (so I can add tests for them)?

[WholeWorld-Timothy]

We use the default character set and collation rules: utf8mb4, utf8mb4_bin
Thank you very much for your help

[mjonss]

/label affects-5.3

[mjonss]

/assign @mjonss

[mjonss]

/label affects-5.2

[mjonss]

/label affects-5.1

[mjonss]

/label affects-5.0

[mjonss]

/label affects-4.0