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));