1
#--disable_abort_on_error
3
# Simple test for the partition storage engine
4
# testing list partitioning
6
-- source include/have_partition.inc
9
drop table if exists t1;
13
# Bug 20733: Zerofill columns gives wrong result with partitioned tables
15
create table t1 (a int unsigned)
17
(partition p0 values in (0),
18
partition p1 values in (1),
19
partition pnull values in (null),
20
partition p2 values in (2));
22
insert into t1 values (null),(0),(1),(2);
23
select * from t1 where a < 2;
24
select * from t1 where a <= 0;
25
select * from t1 where a < 1;
26
select * from t1 where a > 0;
27
select * from t1 where a > 1;
28
select * from t1 where a >= 0;
29
select * from t1 where a >= 1;
30
select * from t1 where a is null;
31
select * from t1 where a is not null;
32
select * from t1 where a is null or a > 0;
35
create table t1 (a int unsigned, b int)
37
subpartition by hash (b)
39
(partition p0 values in (0),
40
partition p1 values in (1),
41
partition pnull values in (null, 2),
42
partition p3 values in (3));
43
insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1);
44
insert into t1 values (2,0),(2,1),(3,0),(3,1);
46
explain partitions select * from t1 where a is null;
47
select * from t1 where a is null;
48
explain partitions select * from t1 where a = 2;
49
select * from t1 where a = 2;
50
select * from t1 where a <= 0;
51
select * from t1 where a < 3;
52
select * from t1 where a >= 1 or a is null;
56
# Test ordinary list partitioning that it works ok
64
(partition x123 values in (1,5,6),
65
partition x234 values in (4,7,8));
67
INSERT into t1 VALUES (1,1,1);
68
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
69
INSERT into t1 VALUES (2,1,1);
70
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
71
INSERT into t1 VALUES (3,1,1);
72
INSERT into t1 VALUES (4,1,1);
73
INSERT into t1 VALUES (5,1,1);
74
INSERT into t1 VALUES (6,1,1);
75
INSERT into t1 VALUES (7,1,1);
76
INSERT into t1 VALUES (8,1,1);
77
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
78
INSERT into t1 VALUES (9,1,1);
79
INSERT into t1 VALUES (1,2,1);
80
INSERT into t1 VALUES (1,3,1);
81
INSERT into t1 VALUES (1,4,1);
82
INSERT into t1 VALUES (7,2,1);
83
INSERT into t1 VALUES (7,3,1);
84
INSERT into t1 VALUES (7,4,1);
87
SELECT * from t1 WHERE a=1;
88
SELECT * from t1 WHERE a=7;
89
SELECT * from t1 WHERE b=2;
91
UPDATE t1 SET a=8 WHERE a=7 AND b=3;
93
UPDATE t1 SET a=8 WHERE a=5 AND b=1;
96
DELETE from t1 WHERE a=8;
98
DELETE from t1 WHERE a=2;
100
DELETE from t1 WHERE a=5 OR a=6;
106
(partition x123 values in (1,5,6),
107
partition x234 values in (4,7,8));
109
INSERT into t1 VALUES (6,2,1);
110
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
111
INSERT into t1 VALUES (2,2,1);
115
# Subpartition by hash, two partitions and two subpartitions
123
partition by list (a)
124
subpartition by hash (a+b)
125
( partition x1 values in (1,2,3)
126
( subpartition x11 nodegroup 0,
127
subpartition x12 nodegroup 1),
128
partition x2 values in (4,5,6)
129
( subpartition x21 nodegroup 0,
130
subpartition x22 nodegroup 1)
133
INSERT into t1 VALUES (1,1,1);
134
INSERT into t1 VALUES (4,1,1);
135
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
136
INSERT into t1 VALUES (7,1,1);
137
UPDATE t1 SET a=5 WHERE a=1;
139
UPDATE t1 SET a=6 WHERE a=4;
141
DELETE from t1 WHERE a=6;
152
partition by list (a)
153
(partition x1 values in (1,2,9,4) tablespace ts1);
158
#Bug #17173 Partitions: less-than search fails
160
CREATE TABLE t1 (s1 int) PARTITION BY LIST (s1)
161
(PARTITION p1 VALUES IN (1),
162
PARTITION p2 VALUES IN (2),
163
PARTITION p3 VALUES IN (3),
164
PARTITION p4 VALUES IN (4),
165
PARTITION p5 VALUES IN (5));
166
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
167
SELECT COUNT(*) FROM t1 WHERE s1 < 3;
171
# Bug 19281 Partitions: Auto-increment value lost
173
create table t1 (a int auto_increment primary key)
175
partition by list (a)
176
(partition p0 values in (1, 100));
177
create index inx on t1 (a);
178
insert into t1 values (null);
182
--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
183
create table t1 (a char(1))
184
partition by list (ascii(ucase(a)))
185
(partition p1 values in (2));