Bug #106011 REORGANIZE LIST partitions must be in consecutive order
Submitted: 30 Dec 2021 16:09 Modified: 13 Jan 2022 14:06
Reporter: XIAOJING LI Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: LIST Partition, reorganize partition

[30 Dec 2021 16:09] XIAOJING LI
Description:
It is not reseanable that when reorganizing a set of LIST partitions they must be in consecutive order.
When I want to merge partition p0 and p2, it returns an error. 

The table is partitioned by LIST, not by RANGE,  why only partitions  in consecutive order can be merged together?

How to repeat:
mysql> show create table t_add;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_add | CREATE TABLE `t_add` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`a`)
(PARTITION p0 VALUES IN (1,2,3,4) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (6,7,8,9,10) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (11,12) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (13,14,15,16) ENGINE = InnoDB) */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t_add reorganize partition p0,p2 into (partition p0  VALUES IN (1,2,3,4,11,12));
ERROR 1519 (HY000): When reorganizing a set of partitions they must be in consecutive order
mysql>

Suggested fix:
I suggest merging list partitions not consecutive through "REORGANIZE PARTITION" is allowed in MySQL server.
[31 Dec 2021 5:11] MySQL Verification Team
Hi,

Why - by design.

If you agree I can change this to a feature request (as it is not a bug)

thanks
[31 Dec 2021 6:56] XIAOJING LI
As a feature  is OK,  "merge partition"  will provide a lot of convenience。
Thank you
[13 Jan 2022 14:06] MySQL Verification Team
Hi Mr. Li,

This report is now accepted as a fully valid feature request.

Thanks.