[MDEV-16101] ADD PARTITION on table partitioned by list does not work with more than 32 list values. - Jira

Richard Stracke created issue - 2018-05-07 10:10

Julien Fritsch made changes - 2018-05-08 13:29

Field	Original Value	New Value
Assignee		Jacob Mathew [ jacob-mathew ]

Jacob Mathew (Inactive) made changes - 2018-05-08 21:42

Component/s		Data Definition - Alter Table [ 10114 ]
Component/s		Partitioning [ 10802 ]
Component/s	Storage Engine - Spider [ 10132 ]
Description	Create "remote" table ts1 {code:sql} create database test; use test; create table ts1 (a int); {code} Create Spider table with 2 partitions. Work also fine with more than 32 values. {code:sql} create table spider_ts1 (a int, PRIMARY KEY (`a`) ) ENGINE=SPIDER PARTITION BY LIST (`a`) (PARTITION `p ts_0` VALUES IN ( 101,102,103,104,105,106,107,108,109,110, 111,112,113,114,115,116,117,118,119,120, 121,122,123,124,125,126,127,128,129,130, 131,132) COMMENT 'table "ts1"' ENGINE = SPIDER , PARTITION `p ts_1` VALUES IN (46,47) COMMENT 'table "ts1"' ENGINE = SPIDER ) ; {code} Now alter table with more than 32 Values runs into an error "SQL Error (1657) Cannot have more than one value for this type of LIST partitioning" {code:sql} ALTER TABLE spider_ts1 add partition ( PARTITION `p ts_3` VALUES IN (1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32,33) COMMENT 'table "ts1"' ENGINE = SPIDER); {code} With 32 or less values, it works fine {code:sql} ALTER TABLE spider_ts1 add partition ( PARTITION `p ts_3` VALUES IN (1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32) COMMENT 'table "ts1"' ENGINE = SPIDER); {code}	Create table with 1 partition. {code:sql} CREATE OR REPLACE TABLE ts1 (a INT, PRIMARY KEY (`a`)) PARTITION BY LIST (`a`) (PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110, 111,112,113,114,115,116,117,118,119,120, 121,122,123,124,125,126,127,128,129,130, 131,132,133)); {code} Now alter table with more than 32 list values runs yields the error "SQL Error (1657) Cannot have more than one value for this type of LIST partitioning" {code:sql} ALTER TABLE ts1 ADD PARTITION (PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32,33)); {code} With 32 or less list values, it works fine. {code:sql} ALTER TABLE ts1 ADD PARTITION (PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32)); {code}
Environment	Ubuntu 04.18
Summary	Add partitions on spider table (partitions by list) not work with more than 32 Values	ADD PARTITION on table partitioned by list does not work with more than 32 list values.

Jacob Mathew (Inactive) made changes - 2018-05-08 21:45

Description

Create table with 1 partition.

{code:sql}
CREATE OR REPLACE TABLE ts1 (a INT, PRIMARY KEY (`a`))
PARTITION BY LIST (`a`)
(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
                                                       111,112,113,114,115,116,117,118,119,120,
                                                       121,122,123,124,125,126,127,128,129,130,
                                                       131,132,133));

{code}

Now alter table with more than 32 list values runs yields the error
"SQL Error (1657) Cannot have more than one value for this type of LIST partitioning"

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                                                       11,12,13,14,15,16,17,18,19,20,
                                                       21,22,23,24,25,26,27,28,29,30,
                                                       31,32,33));
{code}

With 32 or less list values, it works fine.

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                                                       11,12,13,14,15,16,17,18,19,20,
                                                       21,22,23,24,25,26,27,28,29,30,
                                                       31,32));
{code}

Create table with 1 partition.

{code:sql}
CREATE OR REPLACE TABLE ts1 (a INT, PRIMARY KEY (`a`))
PARTITION BY LIST (`a`)
(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
                               111,112,113,114,115,116,117,118,119,120,
                               121,122,123,124,125,126,127,128,129,130,
                               131,132,133));

{code}

Now alter table with more than 32 list values runs yields the error
"SQL Error (1657) Cannot have more than one value for this type of LIST partitioning"

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32,33));
{code}

With 32 or less list values, it works fine.

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32));
{code}

Jacob Mathew (Inactive) made changes - 2018-05-08 21:46

Description

Create table with 1 partition.

{code:sql}
CREATE OR REPLACE TABLE ts1 (a INT, PRIMARY KEY (`a`))
PARTITION BY LIST (`a`)
(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
                               111,112,113,114,115,116,117,118,119,120,
                               121,122,123,124,125,126,127,128,129,130,
                               131,132,133));

{code}

Now alter table with more than 32 list values runs yields the error
"SQL Error (1657) Cannot have more than one value for this type of LIST partitioning"

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32,33));
{code}

With 32 or less list values, it works fine.

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32));
{code}

Create table with 1 partition.

{code:sql}
CREATE OR REPLACE TABLE ts1 (a INT, PRIMARY KEY (`a`))
PARTITION BY LIST (`a`)
(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
                               111,112,113,114,115,116,117,118,119,120,
                               121,122,123,124,125,126,127,128,129,130,
                               131,132,133));

{code}

Now alter table with more than 32 list values yields the error
"SQL Error (1657) Cannot have more than one value for this type of LIST partitioning"

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32,33));
{code}

With 32 or less list values, it works fine.

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32));
{code}

Jacob Mathew (Inactive) made changes - 2018-05-08 21:49

Description

Create table with 1 partition.

{code:sql}
CREATE OR REPLACE TABLE ts1 (a INT, PRIMARY KEY (`a`))
PARTITION BY LIST (`a`)
(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
                               111,112,113,114,115,116,117,118,119,120,
                               121,122,123,124,125,126,127,128,129,130,
                               131,132,133));

{code}

Now alter table with more than 32 list values yields the error
"SQL Error (1657) Cannot have more than one value for this type of LIST partitioning"

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32,33));
{code}

With 32 or less list values, it works fine.

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32));
{code}

Create table with 1 partition.

{code:sql}
CREATE TABLE ts1 (a INT, PRIMARY KEY (`a`))
PARTITION BY LIST (`a`)
(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
                               111,112,113,114,115,116,117,118,119,120,
                               121,122,123,124,125,126,127,128,129,130,
                               131,132,133));

{code}

Now alter table with more than 32 list values yields the error
"SQL Error (1657) Cannot have more than one value for this type of LIST partitioning"

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32,33));
{code}

With 32 or less list values, it works fine.

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32));
{code}

Jacob Mathew (Inactive) made changes - 2018-05-08 21:50

Status

Open [ 1 ]

Confirmed [ 10101 ]

Jacob Mathew (Inactive) made changes - 2018-05-09 00:33

Description

Create table with 1 partition.

{code:sql}
CREATE TABLE ts1 (a INT, PRIMARY KEY (`a`))
PARTITION BY LIST (`a`)
(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
                               111,112,113,114,115,116,117,118,119,120,
                               121,122,123,124,125,126,127,128,129,130,
                               131,132,133));

{code}

Now alter table with more than 32 list values yields the error
"SQL Error (1657) Cannot have more than one value for this type of LIST partitioning"

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32,33));
{code}

With 32 or less list values, it works fine.

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_2` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32));
{code}

Create table with 1 partition.

{code:sql}
CREATE TABLE ts1 (a INT, PRIMARY KEY (`a`))
PARTITION BY LIST (`a`)
(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
                               111,112,113,114,115,116,117,118,119,120,
                               121,122,123,124,125,126,127,128,129,130,
                               131,132,133));

{code}

Now alter table with more than 32 list values yields the error
"SQL Error (1657) Cannot have more than one value for this type of LIST partitioning"

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_1` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32,33));
{code}

With 32 or less list values, it works fine.

{code:sql}
ALTER TABLE ts1 ADD PARTITION
(PARTITION `p ts_1` VALUES IN (1,2,3,4,5,6,7,8,9,10,
                               11,12,13,14,15,16,17,18,19,20,
                               21,22,23,24,25,26,27,28,29,30,
                               31,32));
{code}

Jacob Mathew (Inactive) made changes - 2018-05-09 01:53

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Jacob Mathew (Inactive) added a comment - 2018-05-12 04:47

Holyfoot, please review my fix for this problem in commit 85bbea8 on my branch.

Jacob Mathew (Inactive) added a comment - 2018-05-12 04:47 Holyfoot, please review my fix for this problem in commit 85bbea8 on my branch .

Jacob Mathew (Inactive) made changes - 2018-05-12 04:47

Assignee	Jacob Mathew [ jacob-mathew ]	Alexey Botchkov [ holyfoot ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Jacob Mathew (Inactive) made changes - 2018-05-12 07:39

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

Alexey Botchkov made changes - 2018-05-13 21:37

Assignee	Alexey Botchkov [ holyfoot ]	Jacob Mathew [ jacob-mathew ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Jacob Mathew (Inactive) made changes - 2018-05-14 20:43

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Jacob Mathew (Inactive) added a comment - 2018-05-14 23:36

Fix 8e0159 is pushed to 10.3 and 10.2.

Jacob Mathew (Inactive) added a comment - 2018-05-14 23:36 Fix 8e0159 is pushed to 10.3 and 10.2.

Jacob Mathew (Inactive) made changes - 2018-05-14 23:36

Fix Version/s		10.2.15 [ 23006 ]
Fix Version/s		10.3.7 [ 23005 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 87008 ]

MariaDB v4 [ 154313 ]

Jira Automation (IT) made changes - 2024-07-04 07:39

Zendesk Related Tickets

119658

MariaDB Server

ADD PARTITION on table partitioned by list does not work with more than 32 list values.

Details

Description

Attachments

Activity

People

Dates

Git Integration