[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

Ascending order - Click to sort in descending order

Cho created issue - 2019-11-13 08:12

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)

Alice Sherepa made changes - 2019-11-18 17:57

Field	Original Value	New Value
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-11-18 17:57

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-11-18 17:57

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2019-11-18 17:59

Assignee

Igor Babaev [ igor ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 101114 ]

MariaDB v4 [ 144191 ]

Ralf Gebhardt made changes - 2022-08-04 08:40

Fix Version/s

10.2 [ 14601 ]

Julien Fritsch made changes - 2023-04-27 13:58

Fix Version/s

10.3 [ 22126 ]

Michael Widenius made changes - 2025-04-01 13:07

Component/s

Optimizer [ 10200 ]

Michael Widenius made changes - 2025-04-01 13:14

Assignee

Igor Babaev [ igor ]

People

Assignee:: Unassigned

Reporter:: Cho

Votes:: 1 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2019-11-13 08:12

Updated:: 2025-04-01 13:14

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration