[MDEV-21042] REORGANIZE PARTITION is not working well on LIST COLUMNS partition type Created: 2019-11-13  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.3.13, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Cho Assignee: Igor Babaev
Resolution: Unresolved Votes: 1
Labels: None
Environment:

centos7



 Description   

Hi, I have a question about partition reorganizing while I'm trying to test.

I use LIST COLUMNS type and DEFAULT partition.

I want to split (reorganize) specific value of DEFAULT partition into other new partitions.

Here is the script.

---------------------------------------------------------------------------------------------------

CREATE TABLE PAR_TEST
(
COL VARCHAR(6)
);

ALTER TABLE PAR_TEST PARTITION BY LIST COLUMNS (COL) (
PARTITION P_00000 VALUES IN ('00000'),
PARTITION P_00001 VALUES IN ('00001'),
PARTITION P_00002 VALUES IN ('00002'),
PARTITION P_00003 VALUES IN ('00003'),
PARTITION P_00004 VALUES IN ('00004'),
PARTITION P_DEFAULT DEFAULT);

INSERT INTO PAR_TEST
VALUES ('00000'),('00001'),('00002'),('00003'),('00004'),('11111'),('22222'),('22222'), ('TEST'), ('TEST1');

-------------------------------------------------------------------------------------------------

<TEST1>
ALTER TABLE PAR_TEST
REORGANIZE PARTITION P_DEFAULT INTO
(PARTITION P_DEFAULT DEFAULT, PARTITION P_11111 VALUES IN ('11111'), PARTITION P_22222 VALUES IN ('22222')
);

<TEST2>
ALTER TABLE PAR_TEST
REORGANIZE PARTITION P_DEFAULT INTO
(PARTITION P_11111 VALUES IN ('11111'), PARTITION P_22222 VALUES IN ('22222'),PARTITION P_DEFAULT DEFAULT
);

--------------------------------------------------------------------------------------------------

The only difference between TEST1 AND TEST2 is just the order of 'P_11111' and 'P_DEFAULT'

in REORGANIZE PARTITION clause.

When I select a certain partition, TEST1 shows the result what I expected.

But, If I select DEFAULT partition, it shows nothing.
(SELECT * FROM PAR_TEST PARTITION(P_DEFAULT); -> no result)

And the values which should be in DEFAULT partition are in 'P_11111' partition.
(SELECT * FROM PAR_TEST PARTITION(P_11111); -> 11111, TEST, TEST1 (3 rows))

I wonder if it is intended or bug.

Thank you.

 Comments   
Comment by Alice Sherepa [ 2019-11-18 ]

Thanks a lot! Looks like a bug, I repeated on 10.2-10.4:

--source include/have_partition.inc
 
create table t1 (a varchar(6)) 
partition by list columns(a) (partition p_default default);
 
 
 
insert into t1 values ('11111'),('22222'),('22222'), ('test'), ('test1');
 
 
 
alter table t1 reorganize partition p_default into
 
(partition p_11111 values in ('11111'), partition p_22222 values in ('22222'), partition p_default default);
 
 
 
select * from t1 partition(p_default); 
select * from t1 partition(p_11111); 
 
 
show create table t1;
 
drop table t1;


MariaDB [test]> select * from t1 partition(p_default); 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1 partition(p_11111); 
+-------+
 
| a     |
 
+-------+
 
| 11111 |
 
| test  |
 
| test1 |
 
+-------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> show create table t1;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                                                                                  |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `a` varchar(6) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
 PARTITION BY LIST  COLUMNS(`a`)
 
(PARTITION `p_11111` VALUES IN ('11111') ENGINE = InnoDB,
 
 PARTITION `p_22222` VALUES IN ('22222') ENGINE = InnoDB,
 
 PARTITION `p_default` DEFAULT ENGINE = InnoDB) |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1 where a not in ('11111','22222');
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1;
 
+-------+
 
| a     |
 
+-------+
 
| 11111 |
 
| test  |
 
| test1 |
 
| 22222 |
 
| 22222 |
 
+-------+
 
5 rows in set (0.01 sec)
 
 
 
 
 
MariaDB [test]> explain partitions select * from t1 where a not in ('11111','22222');
 
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t1    | p_default  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
 
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 09:04:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.