545 lines
17 KiB
Plaintext
545 lines
17 KiB
Plaintext
drop table if exists t1;
|
|
#
|
|
# Bug#62505: ALTER TABLE ADD PARTITION fails for LIST partitions with
|
|
# more than 16 items
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
# SUCCESS with 20 items because this is initial partitioning action
|
|
# (The parser already knows that it is only on column)
|
|
ALTER TABLE t1
|
|
PARTITION BY LIST(a)
|
|
(PARTITION p1 VALUES IN (1,2,3,4,5,6,7,8,9,10,
|
|
11,12,13,14,15,16,17,18,19,20));
|
|
# BUG: FAILED, because number of items > 16 during partition add
|
|
# (The parser do not know how many columns the table is partitioned on)
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30,
|
|
31,32,33,34,35,36,37,38,39,40));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION p1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB) */
|
|
# Test with single column LIST COLUMNS too
|
|
ALTER TABLE t1
|
|
PARTITION BY LIST COLUMNS (a)
|
|
(PARTITION p1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20));
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p2 VALUES IN ((71),(72),(73),(74),(75),(76),(77),(78),(79),(80),
|
|
(81),(82),(83),(84),(85),(86),(87),(88),(89),(90)));
|
|
ERROR 42000: Row expressions in VALUES IN only allowed for multi-field column partitioning near '))' at line 3
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30,
|
|
31,32,33,34,35,36,37,38,39,40));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(a)
|
|
(PARTITION p1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB) */
|
|
DROP TABLE t1;
|
|
# Test with two columns in LIST COLUMNS partitioning
|
|
CREATE TABLE t1
|
|
(a INT,
|
|
b CHAR(2))
|
|
PARTITION BY LIST COLUMNS (a, b)
|
|
(PARTITION p0_a VALUES IN
|
|
((0, "a0"), (0, "a1"), (0, "a2"), (0, "a3"), (0, "a4"), (0, "a5"), (0, "a6"),
|
|
(0, "a7"), (0, "a8"), (0, "a9"), (0, "aa"), (0, "ab"), (0, "ac"), (0, "ad"),
|
|
(0, "ae"), (0, "af"), (0, "ag"), (0, "ah"), (0, "ai"), (0, "aj"), (0, "ak"),
|
|
(0, "al")));
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p1_a VALUES IN
|
|
((1, "a0"), (1, "a1"), (1, "a2"), (1, "a3"), (1, "a4"), (1, "a5"), (1, "a6"),
|
|
(1, "a7"), (1, "a8"), (1, "a9"), (1, "aa"), (1, "ab"), (1, "ac"), (1, "ad"),
|
|
(1, "ae"), (1, "af"), (1, "ag"), (1, "ah"), (1, "ai"), (1, "aj"), (1, "ak"),
|
|
(1, "al")));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` char(2) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(a,b)
|
|
(PARTITION p0_a VALUES IN ((0,'a0'),(0,'a1'),(0,'a2'),(0,'a3'),(0,'a4'),(0,'a5'),(0,'a6'),(0,'a7'),(0,'a8'),(0,'a9'),(0,'aa'),(0,'ab'),(0,'ac'),(0,'ad'),(0,'ae'),(0,'af'),(0,'ag'),(0,'ah'),(0,'ai'),(0,'aj'),(0,'ak'),(0,'al')) ENGINE = InnoDB,
|
|
PARTITION p1_a VALUES IN ((1,'a0'),(1,'a1'),(1,'a2'),(1,'a3'),(1,'a4'),(1,'a5'),(1,'a6'),(1,'a7'),(1,'a8'),(1,'a9'),(1,'aa'),(1,'ab'),(1,'ac'),(1,'ad'),(1,'ae'),(1,'af'),(1,'ag'),(1,'ah'),(1,'ai'),(1,'aj'),(1,'ak'),(1,'al')) ENGINE = InnoDB) */
|
|
# Test of the parser for '('
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p2_a VALUES IN
|
|
(((1 + 1), "a0"), (2, "a1"), (2, "a2"), (2, "a3"), (2, "a4"), (2, "a5"),
|
|
(2, "a6"), (2, "a7"), (2, "a8"), (2, "a9"), (2, "aa"), (2, "ab"), (2, "ac"),
|
|
(2, "ad"), (2, "ae"), (2, "af"), (2, "ag"), (2, "ah"), (2, "ai"), (2, "aj"),
|
|
(2, "ak"), (2, "al")));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` char(2) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(a,b)
|
|
(PARTITION p0_a VALUES IN ((0,'a0'),(0,'a1'),(0,'a2'),(0,'a3'),(0,'a4'),(0,'a5'),(0,'a6'),(0,'a7'),(0,'a8'),(0,'a9'),(0,'aa'),(0,'ab'),(0,'ac'),(0,'ad'),(0,'ae'),(0,'af'),(0,'ag'),(0,'ah'),(0,'ai'),(0,'aj'),(0,'ak'),(0,'al')) ENGINE = InnoDB,
|
|
PARTITION p1_a VALUES IN ((1,'a0'),(1,'a1'),(1,'a2'),(1,'a3'),(1,'a4'),(1,'a5'),(1,'a6'),(1,'a7'),(1,'a8'),(1,'a9'),(1,'aa'),(1,'ab'),(1,'ac'),(1,'ad'),(1,'ae'),(1,'af'),(1,'ag'),(1,'ah'),(1,'ai'),(1,'aj'),(1,'ak'),(1,'al')) ENGINE = InnoDB,
|
|
PARTITION p2_a VALUES IN ((2,'a0'),(2,'a1'),(2,'a2'),(2,'a3'),(2,'a4'),(2,'a5'),(2,'a6'),(2,'a7'),(2,'a8'),(2,'a9'),(2,'aa'),(2,'ab'),(2,'ac'),(2,'ad'),(2,'ae'),(2,'af'),(2,'ag'),(2,'ah'),(2,'ai'),(2,'aj'),(2,'ak'),(2,'al')) ENGINE = InnoDB) */
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p3_a VALUES IN ((1 + 1 + 1), "a0"));
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"a0"))' at line 2
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p3_a VALUES IN (1 + 1 + 1, "a0"));
|
|
ERROR HY000: Inconsistency in usage of column lists for partitioning
|
|
# Test with 3 columns when it only has 2.
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p3_a VALUES IN ((3, "a1", 0), (3, "a2", 0)));
|
|
ERROR HY000: Inconsistency in usage of column lists for partitioning
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p3_a VALUES IN ((1 + 1 + 1, "a0")));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` char(2) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(a,b)
|
|
(PARTITION p0_a VALUES IN ((0,'a0'),(0,'a1'),(0,'a2'),(0,'a3'),(0,'a4'),(0,'a5'),(0,'a6'),(0,'a7'),(0,'a8'),(0,'a9'),(0,'aa'),(0,'ab'),(0,'ac'),(0,'ad'),(0,'ae'),(0,'af'),(0,'ag'),(0,'ah'),(0,'ai'),(0,'aj'),(0,'ak'),(0,'al')) ENGINE = InnoDB,
|
|
PARTITION p1_a VALUES IN ((1,'a0'),(1,'a1'),(1,'a2'),(1,'a3'),(1,'a4'),(1,'a5'),(1,'a6'),(1,'a7'),(1,'a8'),(1,'a9'),(1,'aa'),(1,'ab'),(1,'ac'),(1,'ad'),(1,'ae'),(1,'af'),(1,'ag'),(1,'ah'),(1,'ai'),(1,'aj'),(1,'ak'),(1,'al')) ENGINE = InnoDB,
|
|
PARTITION p2_a VALUES IN ((2,'a0'),(2,'a1'),(2,'a2'),(2,'a3'),(2,'a4'),(2,'a5'),(2,'a6'),(2,'a7'),(2,'a8'),(2,'a9'),(2,'aa'),(2,'ab'),(2,'ac'),(2,'ad'),(2,'ae'),(2,'af'),(2,'ag'),(2,'ah'),(2,'ai'),(2,'aj'),(2,'ak'),(2,'al')) ENGINE = InnoDB,
|
|
PARTITION p3_a VALUES IN ((3,'a0')) ENGINE = InnoDB) */
|
|
# Test with more than 16 columns (cause of regression)
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION part_2 VALUES IN ((21 ,22, 23, 24, 25, 26, 27, 28, 29, 30,
|
|
31 ,32, 33, 34, 35, 36, 37, 38, 39, 40),
|
|
(41 ,42, 43, 44, 45, 46, 47, 48, 49, 50,
|
|
51 ,52, 53, 54, 55, 56, 57, 58, 59, 60)));
|
|
ERROR 42000: Row expressions in VALUES IN only allowed for multi-field column partitioning near '))' at line 5
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION part_2 VALUES IN (21 ,22, 23, 24, 25, 26, 27, 28, 29, 30,
|
|
31 ,32, 33, 34, 35, 36, 37, 38, 39, 40));
|
|
ERROR HY000: Inconsistency in usage of column lists for partitioning
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` char(2) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(a,b)
|
|
(PARTITION p0_a VALUES IN ((0,'a0'),(0,'a1'),(0,'a2'),(0,'a3'),(0,'a4'),(0,'a5'),(0,'a6'),(0,'a7'),(0,'a8'),(0,'a9'),(0,'aa'),(0,'ab'),(0,'ac'),(0,'ad'),(0,'ae'),(0,'af'),(0,'ag'),(0,'ah'),(0,'ai'),(0,'aj'),(0,'ak'),(0,'al')) ENGINE = InnoDB,
|
|
PARTITION p1_a VALUES IN ((1,'a0'),(1,'a1'),(1,'a2'),(1,'a3'),(1,'a4'),(1,'a5'),(1,'a6'),(1,'a7'),(1,'a8'),(1,'a9'),(1,'aa'),(1,'ab'),(1,'ac'),(1,'ad'),(1,'ae'),(1,'af'),(1,'ag'),(1,'ah'),(1,'ai'),(1,'aj'),(1,'ak'),(1,'al')) ENGINE = InnoDB,
|
|
PARTITION p2_a VALUES IN ((2,'a0'),(2,'a1'),(2,'a2'),(2,'a3'),(2,'a4'),(2,'a5'),(2,'a6'),(2,'a7'),(2,'a8'),(2,'a9'),(2,'aa'),(2,'ab'),(2,'ac'),(2,'ad'),(2,'ae'),(2,'af'),(2,'ag'),(2,'ah'),(2,'ai'),(2,'aj'),(2,'ak'),(2,'al')) ENGINE = InnoDB,
|
|
PARTITION p3_a VALUES IN ((3,'a0')) ENGINE = InnoDB) */
|
|
DROP TABLE t1;
|
|
create table t1 (a int unsigned)
|
|
partition by list (a)
|
|
(partition p0 values in (0),
|
|
partition p1 values in (1),
|
|
partition pnull values in (null),
|
|
partition p2 values in (2));
|
|
insert into t1 values (null),(0),(1),(2);
|
|
select * from t1 where a < 2;
|
|
a
|
|
0
|
|
1
|
|
select * from t1 where a <= 0;
|
|
a
|
|
0
|
|
select * from t1 where a < 1;
|
|
a
|
|
0
|
|
select * from t1 where a > 0;
|
|
a
|
|
1
|
|
2
|
|
select * from t1 where a > 1;
|
|
a
|
|
2
|
|
select * from t1 where a >= 0;
|
|
a
|
|
0
|
|
1
|
|
2
|
|
select * from t1 where a >= 1;
|
|
a
|
|
1
|
|
2
|
|
select * from t1 where a is null;
|
|
a
|
|
NULL
|
|
select * from t1 where a is not null;
|
|
a
|
|
0
|
|
1
|
|
2
|
|
select * from t1 where a is null or a > 0;
|
|
a
|
|
1
|
|
NULL
|
|
2
|
|
drop table t1;
|
|
create table t1 (a int unsigned, b int)
|
|
partition by list (a)
|
|
subpartition by hash (b)
|
|
subpartitions 2
|
|
(partition p0 values in (0),
|
|
partition p1 values in (1),
|
|
partition pnull values in (null, 2),
|
|
partition p3 values in (3));
|
|
select partition_method, partition_expression, partition_description
|
|
from information_schema.partitions where table_name = "t1";
|
|
PARTITION_METHOD PARTITION_EXPRESSION PARTITION_DESCRIPTION
|
|
LIST `a` 0
|
|
LIST `a` 0
|
|
LIST `a` 1
|
|
LIST `a` 1
|
|
LIST `a` 3
|
|
LIST `a` 3
|
|
LIST `a` NULL,2
|
|
LIST `a` NULL,2
|
|
insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1);
|
|
insert into t1 values (2,0),(2,1),(3,0),(3,1);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain select * from t1 where a is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` is null)
|
|
select * from t1 where a is null;
|
|
a b
|
|
NULL 0
|
|
NULL 1
|
|
explain select * from t1 where a = 2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 2)
|
|
select * from t1 where a = 2;
|
|
a b
|
|
2 0
|
|
2 1
|
|
select * from t1 where a <= 0;
|
|
a b
|
|
0 0
|
|
0 1
|
|
select * from t1 where a < 3;
|
|
a b
|
|
0 0
|
|
0 1
|
|
1 0
|
|
1 1
|
|
2 0
|
|
2 1
|
|
select * from t1 where a >= 1 or a is null;
|
|
a b
|
|
1 0
|
|
1 1
|
|
NULL 0
|
|
2 0
|
|
NULL 1
|
|
2 1
|
|
3 0
|
|
3 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null)
|
|
partition by list(a)
|
|
partitions 2
|
|
(partition x123 values in (1,5,6),
|
|
partition x234 values in (4,7,8));
|
|
INSERT into t1 VALUES (1,1,1);
|
|
INSERT into t1 VALUES (2,1,1);
|
|
ERROR HY000: Table has no partition for value 2
|
|
INSERT into t1 VALUES (3,1,1);
|
|
ERROR HY000: Table has no partition for value 3
|
|
INSERT into t1 VALUES (4,1,1);
|
|
INSERT into t1 VALUES (5,1,1);
|
|
INSERT into t1 VALUES (6,1,1);
|
|
INSERT into t1 VALUES (7,1,1);
|
|
INSERT into t1 VALUES (8,1,1);
|
|
INSERT into t1 VALUES (9,1,1);
|
|
ERROR HY000: Table has no partition for value 9
|
|
INSERT into t1 VALUES (1,2,1);
|
|
INSERT into t1 VALUES (1,3,1);
|
|
INSERT into t1 VALUES (1,4,1);
|
|
INSERT into t1 VALUES (7,2,1);
|
|
INSERT into t1 VALUES (7,3,1);
|
|
INSERT into t1 VALUES (7,4,1);
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
5 1 1
|
|
6 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
4 1 1
|
|
7 1 1
|
|
8 1 1
|
|
7 2 1
|
|
7 3 1
|
|
7 4 1
|
|
SELECT * from t1 WHERE a=1;
|
|
a b c
|
|
1 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
SELECT * from t1 WHERE a=7;
|
|
a b c
|
|
7 1 1
|
|
7 2 1
|
|
7 3 1
|
|
7 4 1
|
|
SELECT * from t1 WHERE b=2;
|
|
a b c
|
|
1 2 1
|
|
7 2 1
|
|
UPDATE t1 SET a=8 WHERE a=7 AND b=3;
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
5 1 1
|
|
6 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
4 1 1
|
|
7 1 1
|
|
8 1 1
|
|
7 2 1
|
|
8 3 1
|
|
7 4 1
|
|
UPDATE t1 SET a=8 WHERE a=5 AND b=1;
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
4 1 1
|
|
7 1 1
|
|
8 1 1
|
|
7 2 1
|
|
8 3 1
|
|
7 4 1
|
|
8 1 1
|
|
DELETE from t1 WHERE a=8;
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
4 1 1
|
|
7 1 1
|
|
7 2 1
|
|
7 4 1
|
|
DELETE from t1 WHERE a=2;
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
4 1 1
|
|
7 1 1
|
|
7 2 1
|
|
7 4 1
|
|
DELETE from t1 WHERE a=5 OR a=6;
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
4 1 1
|
|
7 1 1
|
|
7 2 1
|
|
7 4 1
|
|
ALTER TABLE t1
|
|
partition by list(a)
|
|
partitions 2
|
|
(partition x123 values in (1,5,6),
|
|
partition x234 values in (4,7,8));
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
4 1 1
|
|
7 1 1
|
|
7 2 1
|
|
7 4 1
|
|
INSERT into t1 VALUES (6,2,1);
|
|
INSERT into t1 VALUES (2,2,1);
|
|
ERROR HY000: Table has no partition for value 2
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a,b))
|
|
partition by list (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values in (1,2,3)
|
|
( subpartition x11 nodegroup 0,
|
|
subpartition x12 nodegroup 1),
|
|
partition x2 values in (4,5,6)
|
|
( subpartition x21 nodegroup 0,
|
|
subpartition x22 nodegroup 1)
|
|
);
|
|
INSERT into t1 VALUES (1,1,1);
|
|
INSERT into t1 VALUES (4,1,1);
|
|
INSERT into t1 VALUES (7,1,1);
|
|
ERROR HY000: Table has no partition for value 7
|
|
UPDATE t1 SET a=5 WHERE a=1;
|
|
SELECT * from t1;
|
|
a b c
|
|
5 1 1
|
|
4 1 1
|
|
UPDATE t1 SET a=6 WHERE a=4;
|
|
SELECT * from t1;
|
|
a b c
|
|
5 1 1
|
|
6 1 1
|
|
DELETE from t1 WHERE a=6;
|
|
SELECT * from t1;
|
|
a b c
|
|
5 1 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by list (a)
|
|
(partition x1 values in (1,2,9,4));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION x1 VALUES IN (1,2,9,4) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (s1 int) PARTITION BY LIST (s1)
|
|
(PARTITION p1 VALUES IN (1),
|
|
PARTITION p2 VALUES IN (2),
|
|
PARTITION p3 VALUES IN (3),
|
|
PARTITION p4 VALUES IN (4),
|
|
PARTITION p5 VALUES IN (5));
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
|
|
SELECT COUNT(*) FROM t1 WHERE s1 < 3;
|
|
COUNT(*)
|
|
2
|
|
DROP TABLE t1;
|
|
create table t1 (a int auto_increment primary key)
|
|
auto_increment=100
|
|
partition by list (a)
|
|
(partition p0 values in (1, 100));
|
|
create index inx on t1 (a);
|
|
insert into t1 values (null);
|
|
select * from t1;
|
|
a
|
|
100
|
|
drop table t1;
|
|
create table t1 (a char(1))
|
|
partition by list (ascii(ucase(a)))
|
|
(partition p1 values in (2));
|
|
ERROR HY000: This partition function is not allowed
|
|
# partition list directory
|
|
SET innodb_strict_mode=OFF;
|
|
CREATE TABLE t1 (id INTEGER NOT NULL, name VARCHAR(30), adate DATE)
|
|
PARTITION BY LIST(YEAR(adate))
|
|
(
|
|
PARTITION p1999 VALUES IN (1995, 1999, 2003)
|
|
DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p1999/data'
|
|
INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p1999/idx',
|
|
PARTITION p2000 VALUES IN (1996, 2000, 2004)
|
|
DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p2000/data'
|
|
INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p2000/idx',
|
|
PARTITION p2001 VALUES IN (1997, 2001, 2005)
|
|
DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p2001/data'
|
|
INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/tc_partition_list_directory/p2001/idx'
|
|
);
|
|
INSERT INTO t1 VALUES(1,'abc','1994-01-01');
|
|
ERROR HY000: Table has no partition for value 1994
|
|
INSERT INTO t1 VALUES(2,'abc','1995-01-01');
|
|
INSERT INTO t1 VALUES(3,'abc','1996-01-01');
|
|
INSERT INTO t1 VALUES(4,'abc','1997-01-01');
|
|
INSERT INTO t1 VALUES(5,'abc','1998-01-01');
|
|
ERROR HY000: Table has no partition for value 1998
|
|
INSERT INTO t1 VALUES(6,'abc','1999-01-01');
|
|
INSERT INTO t1 VALUES(7,'abc','2000-01-01');
|
|
INSERT INTO t1 VALUES(8,'abc','2001-01-01');
|
|
INSERT INTO t1 VALUES(9,'abc','2002-01-01');
|
|
ERROR HY000: Table has no partition for value 2002
|
|
INSERT INTO t1 VALUES(10,'abc','2003-01-01');
|
|
INSERT INTO t1 VALUES(11,'abc','2004-01-01');
|
|
INSERT INTO t1 VALUES(12,'abc','2005-01-01');
|
|
INSERT INTO t1 VALUES(13,'abc','2006-01-01');
|
|
ERROR HY000: Table has no partition for value 2006
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#28387488: DATA DICTINARY CRASH ON DEBUG BUILD
|
|
#
|
|
# Create table with partition value which is not legal utf-8.
|
|
CREATE TABLE t1 (c1 varbinary(64) NOT NULL) PARTITION BY LIST COLUMNS (c1) (PARTITION custom_p1 VALUES IN (0x98000));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` varbinary(64) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(c1)
|
|
(PARTITION custom_p1 VALUES IN (_binary 0x098000) ENGINE = InnoDB) */
|
|
SELECT TABLE_NAME, PARTITION_DESCRIPTION FROM information_schema.partitions WHERE table_name = 't1';
|
|
TABLE_NAME PARTITION_DESCRIPTION
|
|
t1 _binary 0x098000
|
|
# Create table with partition value which IS legal utf-8
|
|
CREATE TABLE t2 (c1 varbinary(64) NOT NULL) PARTITION BY LIST COLUMNS (c1) (PARTITION custom_p1 VALUES IN (0x24212b2b));
|
|
# Verify that the partition value is still shown as a hex string
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`c1` varbinary(64) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(c1)
|
|
(PARTITION custom_p1 VALUES IN (_binary 0x24212B2B) ENGINE = InnoDB) */
|
|
SELECT TABLE_NAME, PARTITION_DESCRIPTION FROM information_schema.partitions WHERE table_name = 't2';
|
|
TABLE_NAME PARTITION_DESCRIPTION
|
|
t2 _binary 0x24212B2B
|
|
# Creating t3 using the SHOW CREATE output for t2
|
|
CREATE TABLE `t3` (
|
|
`c1` varbinary(64) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(c1)
|
|
(PARTITION custom_p1 VALUES IN (_binary 0x24212B2B) ENGINE = InnoDB) */;
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`c1` varbinary(64) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50500 PARTITION BY LIST COLUMNS(c1)
|
|
(PARTITION custom_p1 VALUES IN (_binary 0x24212B2B) ENGINE = InnoDB) */
|
|
# Verify that we can insert into the tables
|
|
INSERT INTO t1 VALUES (0x98000);
|
|
INSERT INTO t2 VALUES (0x24212b2b);
|
|
# also when the table is created from SHOW CREATE output
|
|
INSERT INTO t3 VALUES (0x24212b2b);
|
|
# Cleanup
|
|
DROP TABLE t3;
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|