PARTITION BY RANGE COLUMNS allows multiple MAXVALUE partitions

[mjonss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t (d datetime) partition by range columns (d) (partition p1 values less than ('2022-01-01'), partition p2 values less than (maxvalue), partition p3 values less than (MAXVALUE));

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

mysql>  create table t (d datetime) partition by range columns (d) (partition p1 values less than ('2022-01-01'), partition p2 values less than (maxvalue), partition p3 values less than (MAXVALUE));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

3. What did you see instead (Required)

tidb> create table t (d datetime) partition by range columns (d) (partition p1 values less than ('2022-01-01'), partition p2 values less than (maxvalue), partition p3 values less than (MAXVALUE));
Query OK, 0 rows affected (0,00 sec)

tidb> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `d` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE COLUMNS(`d`)
(PARTITION `p1` VALUES LESS THAN ('2022-01-01'),
 PARTITION `p2` VALUES LESS THAN (MAXVALUE),
 PARTITION `p3` VALUES LESS THAN (MAXVALUE))
1 row in set (0,00 sec)

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v6.2.0-alpha-378-g987bdd3014
Edition: Community
Git Commit Hash: 987bdd3014d8c90302caac3e99092d8014c47109
Git Branch: master
UTC Build Time: 2022-07-19 09:33:05
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
Store: unistore

[mjonss]

Issue is in checkTwoRangeColumns, where it only checks if the current partition (and current col) has MAXVALUE, not skipping if both has MAXVALUE.

[u5surf]

@mjonss Hi, I’d like to work on this issue.
Is this patch like that you expected?

diff --git a/ddl/ddl_api.go b/ddl/ddl_api.go
index dd2a63215..ec3b0953c 100644
--- a/ddl/ddl_api.go
+++ b/ddl/ddl_api.go
@@ -2762,6 +2762,10 @@ func checkTwoRangeColumns(ctx sessionctx.Context, curr, prev *model.PartitionDef
        for i := 0; i < len(pi.Columns); i++ {
                // Special handling for MAXVALUE.
                if strings.EqualFold(curr.LessThan[i], partitionMaxValue) {
+                       if strings.EqualFold(prev.LessThan[i], partitionMaxValue) {
+                               // Current and previous are both maxvalue.
+                               return false, nil
+                       }
                        // If current is maxvalue, it certainly >= previous.
                        return true, nil
                }

[MimeLyc]

/remove-label affects-6.2