[MDEV-21042] REORGANIZE PARTITION is not working well on LIST COLUMNS partition type - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3.13, 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4(EOL)
Component/s: Optimizer, Partitioning
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.

Attachments

Activity

Alice Sherepa added a comment - 2019-11-18 17:57

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)

Alice Sherepa added a comment - 2019-11-18 17:57 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)

MariaDB Server

REORGANIZE PARTITION is not working well on LIST COLUMNS partition type

Details

Description

Attachments

Activity

People

Dates

Git Integration