Range COLUMNS partitioning inserted into the wrong partition, violating a unique constraint

[harry1129]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- 创建分区表
CREATE TABLE `p1` (
  `id` bigint(12) NOT NULL,
  `create_time` datetime NOT NULL,
  `brand_type` varchar(8) NOT NULL DEFAULT 'Y' COMMENT '品牌',
  PRIMARY KEY (`id`,`create_time`,`brand_type`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE COLUMNS(`brand_type`,`create_time`)
(
PARTITION `p20240520A` VALUES LESS THAN ('A','2024-05-20 00:00:00'),
PARTITION `p20240520B` VALUES LESS THAN ('B','2024-05-20 00:00:00'),
PARTITION `p20240520C` VALUES LESS THAN ('C','2024-05-20 00:00:00'),
PARTITION `p20240520Z` VALUES LESS THAN ('Z','2024-05-20 00:00:00'));
-- 插入数据   
INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (1, '2024-05-19 00:00:01', 'A');
INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (2, '2024-05-19 00:00:01', 'B');
INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (3, '2024-05-19 00:00:01', 'C');
INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (4, '2024-05-19 00:00:01', 'Z');
-- 添加分区
 alter table test.p1 add PARTITION (
PARTITION `p20240521A` VALUES LESS THAN ('A','2024-05-21 00:00:00'),
PARTITION `p20240521B` VALUES LESS THAN ('B','2024-05-21 00:00:00'),
PARTITION `p20240521C` VALUES LESS THAN ('C','2024-05-21 00:00:00'),
PARTITION `p20240521Z` VALUES LESS THAN ('Z','2024-05-21 00:00:00'));
 
 -- 本该失败，却插入成功
INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (2, '2024-05-19 00:00:01', 'B');
INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (3, '2024-05-19 00:00:01', 'C');
INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (4, '2024-05-19 00:00:01', 'Z');
 
select * from test.p1;
select * from test.p1 partition(p20240520A);
select * from test.p1 partition(p20240520B);
select * from test.p1 partition(p20240520C);
select * from test.p1 partition(p20240520Z);
select * from test.p1 partition(p20240521A);
select * from test.p1 partition(p20240521B);
select * from test.p1 partition(p20240521C);
select * from test.p1 partition(p20240521Z);

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

The last three insert statements report errors (ERROR 1062)

3. What did you see instead (Required)

MySQL [test]> 
MySQL [test]> 
MySQL [test]> -- 创建分区表
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> CREATE TABLE `p1` (
    ->   `id` bigint(12) NOT NULL,
    ->   `create_time` datetime NOT NULL,
    ->   `brand_type` varchar(8) NOT NULL DEFAULT 'Y' COMMENT '品牌',
    ->   PRIMARY KEY (`id`,`create_time`,`brand_type`) /*T![clustered_index] CLUSTERED */
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    -> PARTITION BY RANGE COLUMNS(`brand_type`,`create_time`)
    -> (
    -> PARTITION `p20240520A` VALUES LESS THAN ('A','2024-05-20 00:00:00'),
    -> PARTITION `p20240520B` VALUES LESS THAN ('B','2024-05-20 00:00:00'),
    -> PARTITION `p20240520C` VALUES LESS THAN ('C','2024-05-20 00:00:00'),
    -> PARTITION `p20240520Z` VALUES LESS THAN ('Z','2024-05-20 00:00:00'));
Query OK, 0 rows affected (0.54 sec)

MySQL [test]> -- 插入数据   
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (1, '2024-05-19 00:00:01', 'A');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (2, '2024-05-19 00:00:01', 'B');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (3, '2024-05-19 00:00:01', 'C');
Query OK, 1 row affected (0.01 sec)

MySQL [test]> INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (4, '2024-05-19 00:00:01', 'Z');
Query OK, 1 row affected (0.01 sec)

MySQL [test]> -- 添加分区
Query OK, 1 row affected (0.00 sec)

MySQL [test]>  alter table test.p1 add PARTITION (
    -> PARTITION `p20240521A` VALUES LESS THAN ('A','2024-05-21 00:00:00'),
    -> PARTITION `p20240521B` VALUES LESS THAN ('B','2024-05-21 00:00:00'),
    -> PARTITION `p20240521C` VALUES LESS THAN ('C','2024-05-21 00:00:00'),
    -> PARTITION `p20240521Z` VALUES LESS THAN ('Z','2024-05-21 00:00:00'));
Query OK, 0 rows affected (0.53 sec)

MySQL [test]>  
    ->  -- 本该失败，却插入成功
    -> INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (2, '2024-05-19 00:00:01', 'B');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (3, '2024-05-19 00:00:01', 'C');
Query OK, 1 row affected (0.01 sec)

MySQL [test]> INSERT INTO `test`.`p1`(`id`, `create_time`, `brand_type`) VALUES (4, '2024-05-19 00:00:01', 'Z');
Query OK, 1 row affected (0.00 sec)

MySQL [test]>  
    -> select * from test.p1;
+----+---------------------+------------+
| id | create_time         | brand_type |
+----+---------------------+------------+
|  2 | 2024-05-19 00:00:01 | B          |
|  4 | 2024-05-19 00:00:01 | Z          |
|  1 | 2024-05-19 00:00:01 | A          |
|  2 | 2024-05-19 00:00:01 | B          |
|  4 | 2024-05-19 00:00:01 | Z          |
|  3 | 2024-05-19 00:00:01 | C          |
|  3 | 2024-05-19 00:00:01 | C          |
+----+---------------------+------------+
7 rows in set (0.01 sec)

MySQL [test]> select * from test.p1 partition(p20240520A);
+----+---------------------+------------+
| id | create_time         | brand_type |
+----+---------------------+------------+
|  1 | 2024-05-19 00:00:01 | A          |
+----+---------------------+------------+
1 row in set (0.00 sec)

MySQL [test]> select * from test.p1 partition(p20240520B);
+----+---------------------+------------+
| id | create_time         | brand_type |
+----+---------------------+------------+
|  2 | 2024-05-19 00:00:01 | B          |
+----+---------------------+------------+
1 row in set (0.00 sec)

MySQL [test]> select * from test.p1 partition(p20240520C);
+----+---------------------+------------+
| id | create_time         | brand_type |
+----+---------------------+------------+
|  3 | 2024-05-19 00:00:01 | C          |
+----+---------------------+------------+
1 row in set (0.00 sec)

MySQL [test]> select * from test.p1 partition(p20240520Z);
+----+---------------------+------------+
| id | create_time         | brand_type |
+----+---------------------+------------+
|  4 | 2024-05-19 00:00:01 | Z          |
+----+---------------------+------------+
1 row in set (0.01 sec)

MySQL [test]> select * from test.p1 partition(p20240521A);
Empty set (0.00 sec)

MySQL [test]> select * from test.p1 partition(p20240521B);
+----+---------------------+------------+
| id | create_time         | brand_type |
+----+---------------------+------------+
|  2 | 2024-05-19 00:00:01 | B          |
+----+---------------------+------------+
1 row in set (0.00 sec)

MySQL [test]> select * from test.p1 partition(p20240521C);
+----+---------------------+------------+
| id | create_time         | brand_type |
+----+---------------------+------------+
|  3 | 2024-05-19 00:00:01 | C          |
+----+---------------------+------------+
1 row in set (0.00 sec)

MySQL [test]> select * from test.p1 partition(p20240521Z);
+----+---------------------+------------+
| id | create_time         | brand_type |
+----+---------------------+------------+
|  4 | 2024-05-19 00:00:01 | Z          |
+----+---------------------+------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

MySQL [test]> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:51:57
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

[Defined2014]

We shouldn't support such tables, MySQL will report error like

mysql>  alter table test.p1 add PARTITION (
    -> PARTITION `p20240521A` VALUES LESS THAN ('A','2024-05-21 00:00:00'),
    -> PARTITION `p20240521B` VALUES LESS THAN ('B','2024-05-21 00:00:00'),
    -> PARTITION `p20240521C` VALUES LESS THAN ('C','2024-05-21 00:00:00'),
    -> PARTITION `p20240521Z` VALUES LESS THAN ('Z','2024-05-21 00:00:00'));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

[seiya-annie]

/report community