【 使用环境 】测试环境
【 使用版本 】 OceanBase 3.2.2
【问题描述】alter table 添加一级分区和二级分区, 子分区名称相同添加成功了。但是用修改后得表查询出得创建sql在创建又会出现相同得子分区名的错误。
【复现sql】
CREATE TABLE zore_t2_f_rrr(col1 INT,col2 INT)
PARTITION BY RANGE(col1)
SUBPARTITION BY RANGE(col2)
(PARTITION p0 VALUES LESS THAN(100)
(SUBPARTITION sp0 VALUES LESS THAN(2020),
SUBPARTITION sp1 VALUES LESS THAN(2021)
),
PARTITION p1 VALUES LESS THAN(200)
(SUBPARTITION sp2 VALUES LESS THAN(2020),
SUBPARTITION sp3 VALUES LESS THAN(2021),
SUBPARTITION sp4 VALUES LESS THAN(2022)
)
);
添加一级分区:
ALTER TABLE zore_t2_f_rrr ADD PARTITION p2 VALUES LESS THAN(300)
(SUBPARTITION sp5 VALUES LESS THAN(2020),
SUBPARTITION sp6 VALUES LESS THAN(2021),
SUBPARTITION sp7 VALUES LESS THAN(2022)
);
添加二级分区:
ALTER TABLE zore_t2_f_rrr MODIFY PARTITION p1 ADD
SUBPARTITION sp5 VALUES LESS THAN(2023),
SUBPARTITION sp6 VALUES LESS THAN(2024);
用show create table 查询出的sql 语句创建表报错重复的分区名
obclient> show create table ZORE_T2_F_RRR;
CREATE TABLE “ZORE_T2_F_RRR” (
“COL1” NUMBER(38),
“COL2” NUMBER(38)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by range(col1) subpartition by range(col2)
(partition P0 values less than (100) (
subpartition SP0 values less than (2020),
subpartition SP1 values less than (2021)),
partition P1 values less than (200) (
subpartition SP2 values less than (2020),
subpartition SP3 values less than (2021),
subpartition SP4 values less than (2022),
subpartition SP5 values less than (2023),
subpartition SP6 values less than (2024)),
partition P2 values less than (300) (
subpartition SP5 values less than (2020),
subpartition SP6 values less than (2021),
subpartition SP7 values less than (2022)))
在 OB 3.2.4.1 ORACLE 租户复现了这个问题 , OB 在二级分区表的新增子分区和新建分区表上对二级分区名的唯一性没有执行相同的逻辑,导致出现这种异常/矛盾的数据。 这点跟 ORACLE 行为不一致,大概率是 BUG 。 等后续版本修复。
OB 执行报错
obclient [SYS]> CREATE TABLE "T_PART_ERR" (
-> "COL1" NUMBER,
-> "COL2" NUMBER
-> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
-> partition by range(col1) subpartition by range(col2)
-> (partition P0 values less than (100) (
-> subpartition SP0 values less than (2020),
-> subpartition SP1 values less than (2021)),
-> partition P1 values less than (200) (
-> subpartition SP2 values less than (2020),
-> subpartition SP3 values less than (2021),
-> subpartition SP4 values less than (2022),
-> subpartition SP5 values less than (2023),
-> subpartition SP6 values less than (2024)),
-> partition P2 values less than (300) (
-> subpartition SP5 values less than (2020),
-> subpartition SP6 values less than (2021),
-> subpartition SP7 values less than (2022)));
ORA-14159: duplicate subpartition name SP5
obclient [SYS]> select last_trace_id() from dual;
+-----------------------------------+
| LAST_TRACE_ID() |
+-----------------------------------+
| YB420A00003E-0005FFA6D6CC9755-0-0 |
+-----------------------------------+
1 row in set (0.004 sec)