| blob | a68a67c63862293f204ea8dd02529098e961a0f8 |
1 drop table if exists t1;
2 create table t1 (a int unsigned)
3 partition by list (a)
4 (partition p0 values in (0),
5 partition p1 values in (1),
6 partition pnull values in (null),
7 partition p2 values in (2));
8 insert into t1 values (null),(0),(1),(2);
9 select * from t1 where a < 2;
10 a
11 0
12 1
13 select * from t1 where a <= 0;
14 a
15 0
16 select * from t1 where a < 1;
17 a
18 0
19 select * from t1 where a > 0;
20 a
21 1
22 2
23 select * from t1 where a > 1;
24 a
25 2
26 select * from t1 where a >= 0;
27 a
28 0
29 1
30 2
31 select * from t1 where a >= 1;
32 a
33 1
34 2
35 select * from t1 where a is null;
36 a
37 NULL
38 select * from t1 where a is not null;
39 a
40 0
41 1
42 2
43 select * from t1 where a is null or a > 0;
44 a
45 1
46 NULL
47 2
48 drop table t1;
49 create table t1 (a int unsigned, b int)
50 partition by list (a)
51 subpartition by hash (b)
52 subpartitions 2
53 (partition p0 values in (0),
54 partition p1 values in (1),
55 partition pnull values in (null, 2),
56 partition p3 values in (3));
57 insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1);
58 insert into t1 values (2,0),(2,1),(3,0),(3,1);
59 explain partitions select * from t1 where a is null;
60 id select_type table partitions type possible_keys key key_len ref rows Extra
61 1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 4 Using where
62 select * from t1 where a is null;
63 a b
64 NULL 0
65 NULL 1
66 explain partitions select * from t1 where a = 2;
67 id select_type table partitions type possible_keys key key_len ref rows Extra
68 1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 4 Using where
69 select * from t1 where a = 2;
70 a b
71 2 0
72 2 1
73 select * from t1 where a <= 0;
74 a b
75 0 0
76 0 1
77 select * from t1 where a < 3;
78 a b
79 0 0
80 0 1
81 1 0
82 1 1
83 2 0
84 2 1
85 select * from t1 where a >= 1 or a is null;
86 a b
87 1 0
88 1 1
89 NULL 0
90 2 0
91 NULL 1
92 2 1
93 3 0
94 3 1
95 drop table t1;
96 CREATE TABLE t1 (
97 a int not null,
98 b int not null,
99 c int not null)
100 partition by list(a)
101 partitions 2
102 (partition x123 values in (1,5,6),
103 partition x234 values in (4,7,8));
104 INSERT into t1 VALUES (1,1,1);
105 INSERT into t1 VALUES (2,1,1);
106 ERROR HY000: Table has no partition for value 2
107 INSERT into t1 VALUES (3,1,1);
108 ERROR HY000: Table has no partition for value 3
109 INSERT into t1 VALUES (4,1,1);
110 INSERT into t1 VALUES (5,1,1);
111 INSERT into t1 VALUES (6,1,1);
112 INSERT into t1 VALUES (7,1,1);
113 INSERT into t1 VALUES (8,1,1);
114 INSERT into t1 VALUES (9,1,1);
115 ERROR HY000: Table has no partition for value 9
116 INSERT into t1 VALUES (1,2,1);
117 INSERT into t1 VALUES (1,3,1);
118 INSERT into t1 VALUES (1,4,1);
119 INSERT into t1 VALUES (7,2,1);
120 INSERT into t1 VALUES (7,3,1);
121 INSERT into t1 VALUES (7,4,1);
122 SELECT * from t1;
123 a b c
124 1 1 1
125 5 1 1
126 6 1 1
127 1 2 1
128 1 3 1
129 1 4 1
130 4 1 1
131 7 1 1
132 8 1 1
133 7 2 1
134 7 3 1
135 7 4 1
136 SELECT * from t1 WHERE a=1;
137 a b c
138 1 1 1
139 1 2 1
140 1 3 1
141 1 4 1
142 SELECT * from t1 WHERE a=7;
143 a b c
144 7 1 1
145 7 2 1
146 7 3 1
147 7 4 1
148 SELECT * from t1 WHERE b=2;
149 a b c
150 1 2 1
151 7 2 1
152 UPDATE t1 SET a=8 WHERE a=7 AND b=3;
153 SELECT * from t1;
154 a b c
155 1 1 1
156 5 1 1
157 6 1 1
158 1 2 1
159 1 3 1
160 1 4 1
161 4 1 1
162 7 1 1
163 8 1 1
164 7 2 1
165 8 3 1
166 7 4 1
167 UPDATE t1 SET a=8 WHERE a=5 AND b=1;
168 SELECT * from t1;
169 a b c
170 1 1 1
171 6 1 1
172 1 2 1
173 1 3 1
174 1 4 1
175 4 1 1
176 7 1 1
177 8 1 1
178 7 2 1
179 8 3 1
180 7 4 1
181 8 1 1
182 DELETE from t1 WHERE a=8;
183 SELECT * from t1;
184 a b c
185 1 1 1
186 6 1 1
187 1 2 1
188 1 3 1
189 1 4 1
190 4 1 1
191 7 1 1
192 7 2 1
193 7 4 1
194 DELETE from t1 WHERE a=2;
195 SELECT * from t1;
196 a b c
197 1 1 1
198 6 1 1
199 1 2 1
200 1 3 1
201 1 4 1
202 4 1 1
203 7 1 1
204 7 2 1
205 7 4 1
206 DELETE from t1 WHERE a=5 OR a=6;
207 SELECT * from t1;
208 a b c
209 1 1 1
210 1 2 1
211 1 3 1
212 1 4 1
213 4 1 1
214 7 1 1
215 7 2 1
216 7 4 1
217 ALTER TABLE t1
218 partition by list(a)
219 partitions 2
220 (partition x123 values in (1,5,6),
221 partition x234 values in (4,7,8));
222 SELECT * from t1;
223 a b c
224 1 1 1
225 1 2 1
226 1 3 1
227 1 4 1
228 4 1 1
229 7 1 1
230 7 2 1
231 7 4 1
232 INSERT into t1 VALUES (6,2,1);
233 INSERT into t1 VALUES (2,2,1);
234 ERROR HY000: Table has no partition for value 2
235 drop table t1;
236 CREATE TABLE t1 (
237 a int not null,
238 b int not null,
239 c int not null,
240 primary key (a,b))
241 partition by list (a)
242 subpartition by hash (a+b)
243 ( partition x1 values in (1,2,3)
244 ( subpartition x11 nodegroup 0,
245 subpartition x12 nodegroup 1),
246 partition x2 values in (4,5,6)
247 ( subpartition x21 nodegroup 0,
248 subpartition x22 nodegroup 1)
249 );
250 INSERT into t1 VALUES (1,1,1);
251 INSERT into t1 VALUES (4,1,1);
252 INSERT into t1 VALUES (7,1,1);
253 ERROR HY000: Table has no partition for value 7
254 UPDATE t1 SET a=5 WHERE a=1;
255 SELECT * from t1;
256 a b c
257 5 1 1
258 4 1 1
259 UPDATE t1 SET a=6 WHERE a=4;
260 SELECT * from t1;
261 a b c
262 5 1 1
263 6 1 1
264 DELETE from t1 WHERE a=6;
265 SELECT * from t1;
266 a b c
267 5 1 1
268 drop table t1;
269 CREATE TABLE t1 (
270 a int not null,
271 b int not null,
272 c int not null,
273 primary key(a,b))
274 partition by list (a)
275 (partition x1 values in (1,2,9,4) tablespace ts1);
276 drop table t1;
277 CREATE TABLE t1 (s1 int) PARTITION BY LIST (s1)
278 (PARTITION p1 VALUES IN (1),
279 PARTITION p2 VALUES IN (2),
280 PARTITION p3 VALUES IN (3),
281 PARTITION p4 VALUES IN (4),
282 PARTITION p5 VALUES IN (5));
283 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
284 SELECT COUNT(*) FROM t1 WHERE s1 < 3;
285 COUNT(*)
286 2
287 DROP TABLE t1;
288 create table t1 (a int auto_increment primary key)
289 auto_increment=100
290 partition by list (a)
291 (partition p0 values in (1, 100));
292 create index inx on t1 (a);
293 insert into t1 values (null);
294 select * from t1;
295 a
296 100
297 drop table t1;
298 create table t1 (a char(1))
299 partition by list (ascii(ucase(a)))
300 (partition p1 values in (2));
301 ERROR HY000: This partition function is not allowed