Debian Codegraph @

mysql-test/suite/parts/r/partition-dml-1-1-innodb-modes.result

raw
1 set @save_sqlmode= @@global.sql_mode;
2 # Additional tests for WL#5217 by QA, testplan 1.1
3 CREATE TABLE t1
4 (a INT NOT NULL,
5 b varchar (64),
6 INDEX ind_t1 (b,a),
7 PRIMARY KEY (a))
8 ENGINE = InnoDB
9 PARTITION BY RANGE (a)
10 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
11 (PARTITION pNeg VALUES LESS THAN (0)
12 (SUBPARTITION subp0 ,
13 SUBPARTITION subp1 ,
14 SUBPARTITION subp2 ),
15 PARTITION `p0-29` VALUES LESS THAN (30)
16 (SUBPARTITION subp3 ,
17 SUBPARTITION subp4 ,
18 SUBPARTITION subp5 ),
19 PARTITION `p30-299` VALUES LESS THAN (300)
20 (SUBPARTITION subp6 ,
21 SUBPARTITION subp7 ,
22 SUBPARTITION subp8 ),
23 PARTITION `p300-2999` VALUES LESS THAN (3000)
24 (SUBPARTITION subp9 ,
25 SUBPARTITION subp10 ,
26 SUBPARTITION subp11 ),
27 PARTITION `p3000-299999` VALUES LESS THAN (300000)
28 (SUBPARTITION subp12 ,
29 SUBPARTITION subp13 ,
30 SUBPARTITION subp14 ));
31 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
32 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
33 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
34 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
35 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
36 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
37 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
38 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
39 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
40 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
41 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
42 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
43 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
44 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
45 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
46 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
47 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
48 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
49 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
50 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
51 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
52 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
53 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
54 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
55 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
56 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
57 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
58 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
59 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
60 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
61 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
62 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
63 CREATE USER test_user_1 IDENTIFIED BY 'testpw';
64 CREATE USER test_user_2 IDENTIFIED BY 'testpw';
65 GRANT ALL PRIVILEGES ON test.* TO test_user_1 ;
66 GRANT ALL PRIVILEGES ON test.* TO test_user_2 ;
67 # Run in empty mode
68 set @@global.sql_mode= '';
69 #######################################################
70 # UPDATE without partition selection
71 connect session1, localhost, test_user_1,'testpw',test;
72 SELECT * FROM t1 PARTITION (`p0-29`);
73 a b
74 1 (p0-29-)subp3
75 2 (p0-29-)subp3
76 21 (p0-29-)subp5
77 22 (p0-29-)subp5
78 23 (p0-29-)subp5
79 24 (p0-29-)subp5
80 3 (p0-29-)subp3
81 4 (p0-29-)subp3
82 send
83 UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9;
84 connect session2, localhost, test_user_2,'testpw',test;
85 UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9;
86 SELECT * FROM t1 PARTITION (`p0-29`);
87 a b
88 1 table-upd-2
89 2 table-upd-2
90 21 (p0-29-)subp5
91 22 (p0-29-)subp5
92 23 (p0-29-)subp5
93 24 (p0-29-)subp5
94 3 table-upd-2
95 4 table-upd-2
96 INSERT INTO t1 VALUES (11, 'table-ins');
97 INSERT INTO t1 VALUES (12, 'table-ins');
98 SELECT * FROM t1 PARTITION (`p0-29`);
99 a b
100 1 table-upd-2
101 11 table-ins
102 12 table-ins
103 2 table-upd-2
104 21 (p0-29-)subp5
105 22 (p0-29-)subp5
106 23 (p0-29-)subp5
107 24 (p0-29-)subp5
108 3 table-upd-2
109 4 table-upd-2
110 DELETE FROM t1 WHERE b= 'table-ins';
111 SELECT * FROM t1 PARTITION (`p0-29`);
112 a b
113 1 table-upd-2
114 2 table-upd-2
115 21 (p0-29-)subp5
116 22 (p0-29-)subp5
117 23 (p0-29-)subp5
118 24 (p0-29-)subp5
119 3 table-upd-2
120 4 table-upd-2
121 connection session1;
122 reap
123 SELECT * FROM t1 PARTITION (`p0-29`);
124 a b
125 1 table-upd-2
126 2 table-upd-2
127 21 (p0-29-)subp5
128 22 (p0-29-)subp5
129 23 (p0-29-)subp5
130 24 (p0-29-)subp5
131 3 table-upd-2
132 4 table-upd-2
133 connection session2;
134 SELECT * FROM t1 PARTITION (`p0-29`);
135 a b
136 1 table-upd-2
137 2 table-upd-2
138 21 (p0-29-)subp5
139 22 (p0-29-)subp5
140 23 (p0-29-)subp5
141 24 (p0-29-)subp5
142 3 table-upd-2
143 4 table-upd-2
144 #######################################################
145 # UPDATE with partition selection
146 connection session1;
147 SELECT * FROM t1 PARTITION (`p0-29`);
148 a b
149 1 table-upd-2
150 2 table-upd-2
151 21 (p0-29-)subp5
152 22 (p0-29-)subp5
153 23 (p0-29-)subp5
154 24 (p0-29-)subp5
155 3 table-upd-2
156 4 table-upd-2
157 send
158 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
159 connection session2;
160 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
161 SELECT * FROM t1 PARTITION (`p0-29`);
162 a b
163 1 p0-29-upd-2
164 2 p0-29-upd-2
165 21 (p0-29-)subp5
166 22 (p0-29-)subp5
167 23 (p0-29-)subp5
168 24 (p0-29-)subp5
169 3 p0-29-upd-2
170 4 p0-29-upd-2
171 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins');
172 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins');
173 SELECT * FROM t1 PARTITION (`p0-29`);
174 a b
175 1 p0-29-upd-2
176 11 (p0-29-)ins
177 12 (p0-29-)ins
178 2 p0-29-upd-2
179 21 (p0-29-)subp5
180 22 (p0-29-)subp5
181 23 (p0-29-)subp5
182 24 (p0-29-)subp5
183 3 p0-29-upd-2
184 4 p0-29-upd-2
185 DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins';
186 SELECT * FROM t1 PARTITION (`p0-29`);
187 a b
188 1 p0-29-upd-2
189 2 p0-29-upd-2
190 21 (p0-29-)subp5
191 22 (p0-29-)subp5
192 23 (p0-29-)subp5
193 24 (p0-29-)subp5
194 3 p0-29-upd-2
195 4 p0-29-upd-2
196 connection session1;
197 reap
198 SELECT * FROM t1 PARTITION (`p0-29`);
199 a b
200 1 p0-29-upd-2
201 2 p0-29-upd-2
202 21 (p0-29-)subp5
203 22 (p0-29-)subp5
204 23 (p0-29-)subp5
205 24 (p0-29-)subp5
206 3 p0-29-upd-2
207 4 p0-29-upd-2
208 connection session2;
209 SELECT * FROM t1 PARTITION (`p0-29`);
210 a b
211 1 p0-29-upd-2
212 2 p0-29-upd-2
213 21 (p0-29-)subp5
214 22 (p0-29-)subp5
215 23 (p0-29-)subp5
216 24 (p0-29-)subp5
217 3 p0-29-upd-2
218 4 p0-29-upd-2
219 disconnect session1;
220 disconnect session2;
221 connection default;
222 DROP TABLE t1;
223 CREATE TABLE t1
224 (a INT NOT NULL,
225 b varchar (64),
226 INDEX ind_t1 (b,a),
227 PRIMARY KEY (a))
228 ENGINE = InnoDB
229 PARTITION BY RANGE (a)
230 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
231 (PARTITION pNeg VALUES LESS THAN (0)
232 (SUBPARTITION subp0 ,
233 SUBPARTITION subp1 ,
234 SUBPARTITION subp2 ),
235 PARTITION `p0-29` VALUES LESS THAN (30)
236 (SUBPARTITION subp3 ,
237 SUBPARTITION subp4 ,
238 SUBPARTITION subp5 ),
239 PARTITION `p30-299` VALUES LESS THAN (300)
240 (SUBPARTITION subp6 ,
241 SUBPARTITION subp7 ,
242 SUBPARTITION subp8 ),
243 PARTITION `p300-2999` VALUES LESS THAN (3000)
244 (SUBPARTITION subp9 ,
245 SUBPARTITION subp10 ,
246 SUBPARTITION subp11 ),
247 PARTITION `p3000-299999` VALUES LESS THAN (300000)
248 (SUBPARTITION subp12 ,
249 SUBPARTITION subp13 ,
250 SUBPARTITION subp14 ));
251 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
252 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
253 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
254 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
255 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
256 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
257 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
258 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
259 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
260 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
261 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
262 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
263 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
264 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
265 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
266 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
267 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
268 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
269 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
270 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
271 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
272 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
273 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
274 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
275 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
276 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
277 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
278 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
279 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
280 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
281 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
282 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
283 # Run in ANSI mode
284 set @@global.sql_mode= ansi;
285 #######################################################
286 # UPDATE without partition selection
287 connect session1, localhost, test_user_1,'testpw',test;
288 SELECT * FROM t1 PARTITION (`p0-29`);
289 a b
290 1 (p0-29-)subp3
291 2 (p0-29-)subp3
292 21 (p0-29-)subp5
293 22 (p0-29-)subp5
294 23 (p0-29-)subp5
295 24 (p0-29-)subp5
296 3 (p0-29-)subp3
297 4 (p0-29-)subp3
298 send
299 UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9;
300 connect session2, localhost, test_user_2,'testpw',test;
301 UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9;
302 SELECT * FROM t1 PARTITION (`p0-29`);
303 a b
304 1 table-upd-2
305 2 table-upd-2
306 21 (p0-29-)subp5
307 22 (p0-29-)subp5
308 23 (p0-29-)subp5
309 24 (p0-29-)subp5
310 3 table-upd-2
311 4 table-upd-2
312 INSERT INTO t1 VALUES (11, 'table-ins');
313 INSERT INTO t1 VALUES (12, 'table-ins');
314 SELECT * FROM t1 PARTITION (`p0-29`);
315 a b
316 1 table-upd-2
317 11 table-ins
318 12 table-ins
319 2 table-upd-2
320 21 (p0-29-)subp5
321 22 (p0-29-)subp5
322 23 (p0-29-)subp5
323 24 (p0-29-)subp5
324 3 table-upd-2
325 4 table-upd-2
326 DELETE FROM t1 WHERE b= 'table-ins';
327 SELECT * FROM t1 PARTITION (`p0-29`);
328 a b
329 1 table-upd-2
330 2 table-upd-2
331 21 (p0-29-)subp5
332 22 (p0-29-)subp5
333 23 (p0-29-)subp5
334 24 (p0-29-)subp5
335 3 table-upd-2
336 4 table-upd-2
337 connection session1;
338 reap
339 SELECT * FROM t1 PARTITION (`p0-29`);
340 a b
341 1 table-upd-2
342 2 table-upd-2
343 21 (p0-29-)subp5
344 22 (p0-29-)subp5
345 23 (p0-29-)subp5
346 24 (p0-29-)subp5
347 3 table-upd-2
348 4 table-upd-2
349 connection session2;
350 SELECT * FROM t1 PARTITION (`p0-29`);
351 a b
352 1 table-upd-2
353 2 table-upd-2
354 21 (p0-29-)subp5
355 22 (p0-29-)subp5
356 23 (p0-29-)subp5
357 24 (p0-29-)subp5
358 3 table-upd-2
359 4 table-upd-2
360 #######################################################
361 # UPDATE with partition selection
362 connection session1;
363 SELECT * FROM t1 PARTITION (`p0-29`);
364 a b
365 1 table-upd-2
366 2 table-upd-2
367 21 (p0-29-)subp5
368 22 (p0-29-)subp5
369 23 (p0-29-)subp5
370 24 (p0-29-)subp5
371 3 table-upd-2
372 4 table-upd-2
373 send
374 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
375 connection session2;
376 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
377 SELECT * FROM t1 PARTITION (`p0-29`);
378 a b
379 1 p0-29-upd-2
380 2 p0-29-upd-2
381 21 (p0-29-)subp5
382 22 (p0-29-)subp5
383 23 (p0-29-)subp5
384 24 (p0-29-)subp5
385 3 p0-29-upd-2
386 4 p0-29-upd-2
387 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins');
388 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins');
389 SELECT * FROM t1 PARTITION (`p0-29`);
390 a b
391 1 p0-29-upd-2
392 11 (p0-29-)ins
393 12 (p0-29-)ins
394 2 p0-29-upd-2
395 21 (p0-29-)subp5
396 22 (p0-29-)subp5
397 23 (p0-29-)subp5
398 24 (p0-29-)subp5
399 3 p0-29-upd-2
400 4 p0-29-upd-2
401 DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins';
402 SELECT * FROM t1 PARTITION (`p0-29`);
403 a b
404 1 p0-29-upd-2
405 2 p0-29-upd-2
406 21 (p0-29-)subp5
407 22 (p0-29-)subp5
408 23 (p0-29-)subp5
409 24 (p0-29-)subp5
410 3 p0-29-upd-2
411 4 p0-29-upd-2
412 connection session1;
413 reap
414 SELECT * FROM t1 PARTITION (`p0-29`);
415 a b
416 1 p0-29-upd-2
417 2 p0-29-upd-2
418 21 (p0-29-)subp5
419 22 (p0-29-)subp5
420 23 (p0-29-)subp5
421 24 (p0-29-)subp5
422 3 p0-29-upd-2
423 4 p0-29-upd-2
424 connection session2;
425 SELECT * FROM t1 PARTITION (`p0-29`);
426 a b
427 1 p0-29-upd-2
428 2 p0-29-upd-2
429 21 (p0-29-)subp5
430 22 (p0-29-)subp5
431 23 (p0-29-)subp5
432 24 (p0-29-)subp5
433 3 p0-29-upd-2
434 4 p0-29-upd-2
435 disconnect session1;
436 disconnect session2;
437 connection default;
438 DROP TABLE t1;
439 CREATE TABLE t1
440 (a INT NOT NULL,
441 b varchar (64),
442 INDEX ind_t1 (b,a),
443 PRIMARY KEY (a))
444 ENGINE = InnoDB
445 PARTITION BY RANGE (a)
446 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
447 (PARTITION pNeg VALUES LESS THAN (0)
448 (SUBPARTITION subp0 ,
449 SUBPARTITION subp1 ,
450 SUBPARTITION subp2 ),
451 PARTITION `p0-29` VALUES LESS THAN (30)
452 (SUBPARTITION subp3 ,
453 SUBPARTITION subp4 ,
454 SUBPARTITION subp5 ),
455 PARTITION `p30-299` VALUES LESS THAN (300)
456 (SUBPARTITION subp6 ,
457 SUBPARTITION subp7 ,
458 SUBPARTITION subp8 ),
459 PARTITION `p300-2999` VALUES LESS THAN (3000)
460 (SUBPARTITION subp9 ,
461 SUBPARTITION subp10 ,
462 SUBPARTITION subp11 ),
463 PARTITION `p3000-299999` VALUES LESS THAN (300000)
464 (SUBPARTITION subp12 ,
465 SUBPARTITION subp13 ,
466 SUBPARTITION subp14 ));
467 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
468 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
469 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
470 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
471 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
472 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
473 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
474 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
475 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
476 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
477 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
478 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
479 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
480 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
481 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
482 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
483 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
484 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
485 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
486 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
487 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
488 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
489 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
490 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
491 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
492 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
493 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
494 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
495 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
496 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
497 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
498 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
499 # Run in traditional mode
500 set @@global.sql_mode= traditional;
501 #######################################################
502 # UPDATE without partition selection
503 connect session1, localhost, test_user_1,'testpw',test;
504 SELECT * FROM t1 PARTITION (`p0-29`);
505 a b
506 1 (p0-29-)subp3
507 2 (p0-29-)subp3
508 21 (p0-29-)subp5
509 22 (p0-29-)subp5
510 23 (p0-29-)subp5
511 24 (p0-29-)subp5
512 3 (p0-29-)subp3
513 4 (p0-29-)subp3
514 send
515 UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9;
516 connect session2, localhost, test_user_2,'testpw',test;
517 UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9;
518 SELECT * FROM t1 PARTITION (`p0-29`);
519 a b
520 1 table-upd-2
521 2 table-upd-2
522 21 (p0-29-)subp5
523 22 (p0-29-)subp5
524 23 (p0-29-)subp5
525 24 (p0-29-)subp5
526 3 table-upd-2
527 4 table-upd-2
528 INSERT INTO t1 VALUES (11, 'table-ins');
529 INSERT INTO t1 VALUES (12, 'table-ins');
530 SELECT * FROM t1 PARTITION (`p0-29`);
531 a b
532 1 table-upd-2
533 11 table-ins
534 12 table-ins
535 2 table-upd-2
536 21 (p0-29-)subp5
537 22 (p0-29-)subp5
538 23 (p0-29-)subp5
539 24 (p0-29-)subp5
540 3 table-upd-2
541 4 table-upd-2
542 DELETE FROM t1 WHERE b= 'table-ins';
543 SELECT * FROM t1 PARTITION (`p0-29`);
544 a b
545 1 table-upd-2
546 2 table-upd-2
547 21 (p0-29-)subp5
548 22 (p0-29-)subp5
549 23 (p0-29-)subp5
550 24 (p0-29-)subp5
551 3 table-upd-2
552 4 table-upd-2
553 connection session1;
554 reap
555 SELECT * FROM t1 PARTITION (`p0-29`);
556 a b
557 1 table-upd-2
558 2 table-upd-2
559 21 (p0-29-)subp5
560 22 (p0-29-)subp5
561 23 (p0-29-)subp5
562 24 (p0-29-)subp5
563 3 table-upd-2
564 4 table-upd-2
565 connection session2;
566 SELECT * FROM t1 PARTITION (`p0-29`);
567 a b
568 1 table-upd-2
569 2 table-upd-2
570 21 (p0-29-)subp5
571 22 (p0-29-)subp5
572 23 (p0-29-)subp5
573 24 (p0-29-)subp5
574 3 table-upd-2
575 4 table-upd-2
576 #######################################################
577 # UPDATE with partition selection
578 connection session1;
579 SELECT * FROM t1 PARTITION (`p0-29`);
580 a b
581 1 table-upd-2
582 2 table-upd-2
583 21 (p0-29-)subp5
584 22 (p0-29-)subp5
585 23 (p0-29-)subp5
586 24 (p0-29-)subp5
587 3 table-upd-2
588 4 table-upd-2
589 send
590 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
591 connection session2;
592 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
593 SELECT * FROM t1 PARTITION (`p0-29`);
594 a b
595 1 p0-29-upd-2
596 2 p0-29-upd-2
597 21 (p0-29-)subp5
598 22 (p0-29-)subp5
599 23 (p0-29-)subp5
600 24 (p0-29-)subp5
601 3 p0-29-upd-2
602 4 p0-29-upd-2
603 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins');
604 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins');
605 SELECT * FROM t1 PARTITION (`p0-29`);
606 a b
607 1 p0-29-upd-2
608 11 (p0-29-)ins
609 12 (p0-29-)ins
610 2 p0-29-upd-2
611 21 (p0-29-)subp5
612 22 (p0-29-)subp5
613 23 (p0-29-)subp5
614 24 (p0-29-)subp5
615 3 p0-29-upd-2
616 4 p0-29-upd-2
617 DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins';
618 SELECT * FROM t1 PARTITION (`p0-29`);
619 a b
620 1 p0-29-upd-2
621 2 p0-29-upd-2
622 21 (p0-29-)subp5
623 22 (p0-29-)subp5
624 23 (p0-29-)subp5
625 24 (p0-29-)subp5
626 3 p0-29-upd-2
627 4 p0-29-upd-2
628 connection session1;
629 reap
630 SELECT * FROM t1 PARTITION (`p0-29`);
631 a b
632 1 p0-29-upd-2
633 2 p0-29-upd-2
634 21 (p0-29-)subp5
635 22 (p0-29-)subp5
636 23 (p0-29-)subp5
637 24 (p0-29-)subp5
638 3 p0-29-upd-2
639 4 p0-29-upd-2
640 connection session2;
641 SELECT * FROM t1 PARTITION (`p0-29`);
642 a b
643 1 p0-29-upd-2
644 2 p0-29-upd-2
645 21 (p0-29-)subp5
646 22 (p0-29-)subp5
647 23 (p0-29-)subp5
648 24 (p0-29-)subp5
649 3 p0-29-upd-2
650 4 p0-29-upd-2
651 disconnect session1;
652 disconnect session2;
653 connection default;
654 DROP TABLE t1;
655 DROP USER test_user_1;
656 DROP USER test_user_2;
657 set @@global.sql_mode= @save_sqlmode;