path: root/mysql-test/main/partition_list.result

drop table if exists 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`	NULL,2
LIST	`a`	NULL,2
LIST	`a`	3
LIST	`a`	3
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);
explain partitions select * from t1 where a is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1	ALL	NULL	NULL	NULL	NULL	4	Using where
select * from t1 where a is null;
a	b
NULL	0
NULL	1
explain partitions select * from t1 where a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	pnull_pnullsp0,pnull_pnullsp1	ALL	NULL	NULL	NULL	NULL	4	Using where
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) tablespace ts1);
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
#
# MDEV-11681: PARTITION BY LIST COLUMNS with default partition:
# Assertion `part_info->num_list_values' failed in
# get_part_iter_for_interval_cols_via_map
#
CREATE TABLE t1 (f int) PARTITION BY LIST COLUMNS (f) (PARTITION pdef DEFAULT);
insert into t1 values (1),(2);
select * from t1 where f = 1;
f
1
drop table t1;
CREATE TABLE t1 (f int, d int) PARTITION BY LIST COLUMNS (f,d) (PARTITION pdef DEFAULT);
insert into t1 values (1,1),(2,2);
select * from t1 where f = 1 and d = 1 ;
f	d
1	1
drop table t1;
CREATE TABLE t1 (f int) PARTITION BY LIST (f) (PARTITION pdef DEFAULT);
insert into t1 values (1),(2);
select * from t1 where f = 1;
f
1
drop table t1;
#end of 10.2 tests
#
# Bug MDEV-16101: More than MAX_REF_PARTS values in a list on ALTER TABLE.
#                 Currently MAX_REF_PARTS = 32.
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));
INSERT INTO ts1
VALUES (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);
INSERT INTO ts1 VALUES (134);
ERROR HY000: Table has no partition for value 134
SELECT * FROM ts1;
a
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
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));
INSERT INTO ts1
VALUES (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);
INSERT INTO ts1 VALUES(34);
ERROR HY000: Table has no partition for value 34
SELECT * FROM ts1;
a
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
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
ALTER TABLE ts1 REORGANIZE PARTITION `p ts_1` INTO
(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,34,35));
INSERT INTO ts1 VALUES (34), (35);
INSERT INTO ts1 VALUES (36);
ERROR HY000: Table has no partition for value 36
SELECT * FROM ts1;
a
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
34
35
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
DROP TABLE ts1;