Debian Codegraph @

mysql-test/suite/parts/r/part_exch_valid_list_innodb.result

raw
1 use test;
2 call mtr.add_suppression("row in wrong partition.*from REBUILD/REORGANIZED");
3 DROP TABLE IF EXISTS t_10;
4 DROP TABLE IF EXISTS t_100;
5 DROP TABLE IF EXISTS t_1000;
6 DROP TABLE IF EXISTS tp_r;
7 DROP TABLE IF EXISTS tp_rvar;
8 DROP TABLE IF EXISTS tp_rintvar;
9 DROP TABLE IF EXISTS tp_rdate;
10 DROP TABLE IF EXISTS tp_rintdate;
11 DROP TABLE IF EXISTS tsp_r;
12 DROP TABLE IF EXISTS tsp_rvar;
13 DROP TABLE IF EXISTS tsp_rvar1;
14 DROP TABLE IF EXISTS tp_l;
15 DROP TABLE IF EXISTS tsp_l;
16 DROP TABLE IF EXISTS tsp_lvar;
17 DROP TABLE IF EXISTS tsp_ldate;
18 DROP TABLE IF EXISTS tp_k;
19 DROP TABLE IF EXISTS tp_kvar;
20 DROP TABLE IF EXISTS tp_kdate;
21 DROP TABLE IF EXISTS tp_h;
22 DROP TABLE IF EXISTS tp_hvar;
23 DROP TABLE IF EXISTS tp_hdate;
24 DROP TABLE IF EXISTS tsp_00;
25 DROP TABLE IF EXISTS tsp_01;
26 DROP TABLE IF EXISTS tsp_02;
27 DROP TABLE IF EXISTS tsp_03;
28 DROP TABLE IF EXISTS tsp_04;
29 DROP TABLE IF EXISTS t_empty;
30 DROP TABLE IF EXISTS t_null;
31 CREATE TABLE t_10 (a INT,
32 b VARCHAR(25),
33 c DATE,
34 PRIMARY KEY (a))
35 charset latin1
36 ENGINE = InnoDB;
37 CREATE TABLE t_100 (a INT,
38 b VARCHAR(25),
39 c DATE,
40 PRIMARY KEY (a))
41 charset latin1
42 ENGINE = InnoDB;
43 CREATE TABLE t_1000 (a INT,
44 b VARCHAR(25),
45 c DATE,
46 PRIMARY KEY (a))
47 charset latin1
48 ENGINE = InnoDB;
49 INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01');
50 INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01');
51 INSERT INTO t_100 VALUES (91, "Ninety-one", '2091-01-01'), (93, "Ninety-three", '2093-01-01'), (95, "Ninety-five", '2095-01-01'), (99, "Ninety-nine", '2099-01-01');
52 INSERT INTO t_1000 VALUES (111, "Hundred elven", '2111-01-01'), (113, "Hundred thirdteen", '2113-01-01'), (115, "Hundred fiveteen", '2115-01-01'), (119, "Hundred nineteen", '2119-01-01');
53 INSERT INTO t_1000 VALUES (131, "Hundred thirty-one", '2131-01-01'), (133, "Hundred thirty-three", '2133-01-01'), (135, "Hundred thirty-five", '2135-01-01'), (139, "Hundred thirty-nine", '2139-01-01');
54 INSERT INTO t_1000 VALUES (151, "Hundred fifty-one", '2151-01-01'), (153, "Hundred fifty-three", '2153-01-01'), (155, "Hundred fity-five", '2155-01-01'), (159, "Hundred fifty-nine", '2159-01-01');
55 INSERT INTO t_1000 VALUES (191, "Hundred ninety-one", '2191-01-01'), (193, "Hundred ninety-three", '2193-01-01'), (195, "Hundred ninety-five", '2195-01-01'), (199, "Hundred ninety-nine", '2199-01-01');
56 CREATE TABLE t_empty (a INT,
57 b VARCHAR(25),
58 c DATE,
59 PRIMARY KEY (a))
60 charset latin1
61 ENGINE = InnoDB;
62 CREATE TABLE t_null (a INT,
63 b VARCHAR(25),
64 c DATE,
65 PRIMARY KEY (a))
66 charset latin1
67 ENGINE = InnoDB;
68 CREATE TABLE tsp_01(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
69 charset latin1
70 ENGINE = InnoDB
71 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
72 CREATE TABLE tsp_02(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
73 charset latin1
74 ENGINE = InnoDB
75 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
76 CREATE TABLE tsp_03(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
77 charset latin1
78 ENGINE = InnoDB
79 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
80 CREATE TABLE tsp_04(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
81 charset latin1
82 ENGINE = InnoDB
83 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
84 CREATE TABLE tsp_00(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
85 charset latin1
86 ENGINE = InnoDB
87 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
88 CREATE TABLE tp_r (a INT,
89 b VARCHAR(25),
90 c DATE,
91 PRIMARY KEY (a))
92 charset latin1
93 ENGINE = InnoDB
94 PARTITION BY RANGE (a)
95 (PARTITION p0 VALUES LESS THAN (10) ,
96 PARTITION p1 VALUES LESS THAN (100) ,
97 PARTITION p2 VALUES LESS THAN (1000) );
98 CREATE TABLE tsp_r (a INT,
99 b VARCHAR(25),
100 c DATE,
101 PRIMARY KEY (a))
102 charset latin1
103 ENGINE = InnoDB
104 PARTITION BY RANGE (a)
105 SUBPARTITION BY HASH(a)
106 (PARTITION p0 VALUES LESS THAN (10)
107 (SUBPARTITION sp00,
108 SUBPARTITION sp01,
109 SUBPARTITION sp02,
110 SUBPARTITION sp03,
111 SUBPARTITION sp04),
112 PARTITION p1 VALUES LESS THAN (100)
113 (SUBPARTITION sp10 ,
114 SUBPARTITION sp11 ,
115 SUBPARTITION sp12 ,
116 SUBPARTITION sp13 ,
117 SUBPARTITION sp14 ),
118 PARTITION p2 VALUES LESS THAN (1000)
119 (SUBPARTITION sp20,
120 SUBPARTITION sp21,
121 SUBPARTITION sp22,
122 SUBPARTITION sp23,
123 SUBPARTITION sp24));
124 INSERT INTO tp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
125 INSERT INTO tp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
126 INSERT INTO tp_r VALUES (112, "Hundred twelve", '2112-01-01'), (114, "Hundred fourteen", '2114-01-01'), (116, "Hundred sixteen", '2116-01-01'), (118, "Hundred eightteen", '2118-01-01');
127 INSERT INTO tp_r VALUES (122, "Hundred twenty-two", '2122-01-01'), (124, "Hundred twenty-four", '2124-01-01'), (126, "Hundred twenty-six", '2126-01-01'), (128, "Hundred twenty-eight", '2128-01-01');
128 INSERT INTO tp_r VALUES (162, "Hundred sixty-two", '2162-01-01'), (164, "Hundred sixty-four", '2164-01-01'), (166, "Hundred sixty-six", '2166-01-01'), (168, "Hundred sixty-eight", '2168-01-01');
129 INSERT INTO tp_r VALUES (182, "Hundred eighty-two", '2182-01-01'), (184, "Hundred eighty-four", '2184-01-01'), (186, "Hundred eighty-six", '2186-01-01'), (188, "Hundred eighty-eight", '2188-01-01');
130 INSERT INTO tsp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
131 INSERT INTO tsp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
132 INSERT INTO tsp_r VALUES (112, "Hundred twelve", '2112-01-01'), (114, "Hundred fourteen", '2114-01-01'), (116, "Hundred sixteen", '2116-01-01'), (118, "Hundred eightteen", '2118-01-01');
133 INSERT INTO tsp_r VALUES (122, "Hundred twenty-two", '2122-01-01'), (124, "Hundred twenty-four", '2124-01-01'), (126, "Hundred twenty-six", '2126-01-01'), (128, "Hundred twenty-eight", '2128-01-01');
134 INSERT INTO tsp_r VALUES (162, "Hundred sixty-two", '2162-01-01'), (164, "Hundred sixty-four", '2164-01-01'), (166, "Hundred sixty-six", '2166-01-01'), (168, "Hundred sixty-eight", '2168-01-01');
135 INSERT INTO tsp_r VALUES (182, "Hundred eight-two", '2182-01-01'), (184, "Hundred eighty-four", '2184-01-01'), (186, "Hundred eighty-six", '2186-01-01'), (188, "Hundred eighty-eight", '2188-01-01');
136 CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (b))
137 charset latin1
138 ENGINE = InnoDB
139 PARTITION BY RANGE (b)
140 (PARTITION p0 VALUES LESS THAN ('H') ,
141 PARTITION p1 VALUES LESS THAN ('Q') ,
142 PARTITION p2 VALUES LESS THAN ('Z') ,
143 PARTITION p3 VALUES LESS THAN (MAXVALUE) )
144 AS SELECT a, b FROM tp_r;
145 ERROR HY000: VALUES value for partition 'p0' must have type INT
146 CREATE TABLE tp_rintvar(a INT,b VARCHAR(25),c DATE,
147 PRIMARY KEY (a,b))
148 charset latin1
149 ENGINE = InnoDB
150 PARTITION BY RANGE COLUMNS (a,b)
151 (PARTITION p0 VALUES LESS THAN (10,'HHHHHHHHHHHHHHHHHHHHHHHHH'),
152 PARTITION p1 VALUES LESS THAN (100,'PPPPPPPPPPPPPPPPPPPPPPPPP'),
153 PARTITION p2 VALUES LESS THAN (1000,'WWWWWWWWWWWWWWWWWWWWWWWWW'),
154 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE))
155 AS SELECT a, b, c FROM tp_r;
156 CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE)
157 charset latin1
158 ENGINE = InnoDB
159 PARTITION BY RANGE COLUMNS (b)
160 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'),
161 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'),
162 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'),
163 PARTITION p3 VALUES LESS THAN (MAXVALUE))
164 AS SELECT a, b, c FROM tp_r;
165 CREATE TABLE tp_rintdate(a INT,b VARCHAR(25),c DATE,
166 PRIMARY KEY (a,c))
167 charset latin1
168 ENGINE = InnoDB
169 PARTITION BY RANGE COLUMNS (a,c)
170 (PARTITION p0 VALUES LESS THAN (10,'2010-01-01'),
171 PARTITION p1 VALUES LESS THAN (100,'2200-01-01'),
172 PARTITION p2 VALUES LESS THAN (1000,'2300-01-01'),
173 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE))
174 AS SELECT a, b, c FROM t_10;
175 INSERT tp_rintdate SELECT * FROM t_100;
176 INSERT tp_rintdate SELECT * FROM t_1000;
177 CREATE TABLE tp_rdate(a INT,b VARCHAR(25),c DATE,
178 PRIMARY KEY (c))
179 charset latin1
180 ENGINE = InnoDB
181 PARTITION BY RANGE COLUMNS (c)
182 (PARTITION p0 VALUES LESS THAN ('2010-01-01'),
183 PARTITION p1 VALUES LESS THAN ('2200-01-01'),
184 PARTITION p2 VALUES LESS THAN ('2300-01-01'),
185 PARTITION p3 VALUES LESS THAN (MAXVALUE))
186 AS SELECT a, b, c FROM tp_r;
187 CREATE TABLE tsp_rvar(a INT,b VARCHAR(25),c DATE)
188 charset latin1
189 ENGINE = InnoDB
190 PARTITION BY RANGE COLUMNS (b)
191 SUBPARTITION BY HASH(a)
192 SUBPARTITIONS 5
193 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH')
194 (SUBPARTITION sp00,
195 SUBPARTITION sp01,
196 SUBPARTITION sp02,
197 SUBPARTITION sp03,
198 SUBPARTITION sp04),
199 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP')
200 (SUBPARTITION sp10,
201 SUBPARTITION sp11,
202 SUBPARTITION sp12,
203 SUBPARTITION sp13,
204 SUBPARTITION sp14),
205 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW')
206 (SUBPARTITION sp20,
207 SUBPARTITION sp21,
208 SUBPARTITION sp22,
209 SUBPARTITION sp23,
210 SUBPARTITION sp24))
211 AS SELECT a, b, c FROM tsp_r;
212 CREATE TABLE tsp_rvar1(a INT,b VARCHAR(25),c DATE)
213 charset latin1
214 ENGINE = InnoDB
215 PARTITION BY RANGE COLUMNS (b)
216 SUBPARTITION BY HASH(a)
217 SUBPARTITIONS 5
218 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'),
219 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'),
220 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'))
221 AS SELECT a, b, c FROM tsp_r;
222 CREATE TABLE tp_l (a INT,
223 b VARCHAR(25),
224 c DATE,
225 PRIMARY KEY (a))
226 charset latin1
227 ENGINE = InnoDB
228 PARTITION BY LIST (a)
229 (PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9),
230 PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99),
231 PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119,
232 120,121,122,123,124,125,126,127,128,129,
233 130,131,132,133,134,135,136,137,138,139,
234 150,151,152,153,154,155,156,157,158,159,
235 160,161,162,163,164,165,166,167,168,169,
236 180,181,182,183,184,185,186,187,188,189,
237 190,191,192,193,194,195,196,197,198,199))
238 AS SELECT a, b, c FROM tp_r;
239 CREATE TABLE tsp_l (a INT,
240 b VARCHAR(25),
241 c DATE,
242 PRIMARY KEY (a))
243 charset latin1
244 ENGINE = InnoDB
245 PARTITION BY LIST (a)
246 SUBPARTITION BY HASH(a)
247 (PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9)
248 (SUBPARTITION sp00,
249 SUBPARTITION sp01,
250 SUBPARTITION sp02,
251 SUBPARTITION sp03,
252 SUBPARTITION sp04),
253 PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99)
254 (SUBPARTITION sp10,
255 SUBPARTITION sp11,
256 SUBPARTITION sp12,
257 SUBPARTITION sp13,
258 SUBPARTITION sp14),
259 PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119,
260 120,121,122,123,124,125,126,127,128,129,
261 130,131,132,133,134,135,136,137,138,139,
262 150,151,152,153,154,155,156,157,158,159,
263 160,161,162,163,164,165,166,167,168,169,
264 180,181,182,183,184,185,186,187,188,189,
265 190,191,192,193,194,195,196,197,198,199)
266 (SUBPARTITION sp20,
267 SUBPARTITION sp21,
268 SUBPARTITION sp22,
269 SUBPARTITION sp23,
270 SUBPARTITION sp24))
271 AS SELECT a, b, c FROM tsp_r;
272 CREATE TABLE tp_k (a INT,
273 b VARCHAR(25),
274 c DATE,
275 PRIMARY KEY (a))
276 charset latin1
277 ENGINE = InnoDB
278 PARTITION BY KEY (a)
279 PARTITIONS 3
280 AS SELECT a, b, c FROM tp_r;
281 CREATE TABLE tp_kvar (a INT,
282 b VARCHAR(25),
283 c DATE,
284 PRIMARY KEY (b))
285 charset latin1
286 ENGINE = InnoDB
287 PARTITION BY KEY (b)
288 PARTITIONS 3
289 AS SELECT a, b, c FROM tp_r;
290 CREATE TABLE tp_kdate (a INT,
291 b VARCHAR(25),
292 c DATE,
293 PRIMARY KEY (c))
294 charset latin1
295 ENGINE = InnoDB
296 PARTITION BY KEY (c)
297 PARTITIONS 3
298 AS SELECT a, b, c FROM tp_r;
299 CREATE TABLE tp_h (a INT,
300 b VARCHAR(25),
301 c DATE,
302 PRIMARY KEY (a))
303 charset latin1
304 ENGINE = InnoDB
305 PARTITION BY HASH (a)
306 PARTITIONS 3
307 AS SELECT a, b, c FROM tp_r;
308 ########################################################################
309 ### List partitioned tables with/out validation
310 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
311 SELECT * FROM tp_l PARTITION (p0);
312 a b c
313 1 One 2001-01-01
314 3 Three 2003-01-01
315 5 Five 2005-01-01
316 9 Nine 2009-01-01
317 SELECT * FROM tp_l PARTITION (p1);
318 a b c
319 12 twelve 2012-01-01
320 14 Fourteen 2014-01-01
321 16 Sixteen 2016-01-01
322 18 Eightteen 2018-01-01
323 SELECT * FROM tp_l PARTITION (p2);
324 a b c
325 112 Hundred twelve 2112-01-01
326 114 Hundred fourteen 2114-01-01
327 116 Hundred sixteen 2116-01-01
328 118 Hundred eightteen 2118-01-01
329 122 Hundred twenty-two 2122-01-01
330 124 Hundred twenty-four 2124-01-01
331 126 Hundred twenty-six 2126-01-01
332 128 Hundred twenty-eight 2128-01-01
333 162 Hundred sixty-two 2162-01-01
334 164 Hundred sixty-four 2164-01-01
335 166 Hundred sixty-six 2166-01-01
336 168 Hundred sixty-eight 2168-01-01
337 182 Hundred eighty-two 2182-01-01
338 184 Hundred eighty-four 2184-01-01
339 186 Hundred eighty-six 2186-01-01
340 188 Hundred eighty-eight 2188-01-01
341 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION;
342 ERROR HY000: Found a row that does not match the partition
343 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION;
344 SELECT * FROM tp_l PARTITION (p0);
345 a b c
346 11 Eleven 2011-01-01
347 13 Thirdteen 2013-01-01
348 15 Fifeteen 2015-01-01
349 19 Nineteen 2019-01-01
350 91 Ninety-one 2091-01-01
351 93 Ninety-three 2093-01-01
352 95 Ninety-five 2095-01-01
353 99 Ninety-nine 2099-01-01
354 SELECT * FROM tp_l PARTITION (p1);
355 a b c
356 12 twelve 2012-01-01
357 14 Fourteen 2014-01-01
358 16 Sixteen 2016-01-01
359 18 Eightteen 2018-01-01
360 SELECT * FROM tp_l PARTITION (p2);
361 a b c
362 112 Hundred twelve 2112-01-01
363 114 Hundred fourteen 2114-01-01
364 116 Hundred sixteen 2116-01-01
365 118 Hundred eightteen 2118-01-01
366 122 Hundred twenty-two 2122-01-01
367 124 Hundred twenty-four 2124-01-01
368 126 Hundred twenty-six 2126-01-01
369 128 Hundred twenty-eight 2128-01-01
370 162 Hundred sixty-two 2162-01-01
371 164 Hundred sixty-four 2164-01-01
372 166 Hundred sixty-six 2166-01-01
373 168 Hundred sixty-eight 2168-01-01
374 182 Hundred eighty-two 2182-01-01
375 184 Hundred eighty-four 2184-01-01
376 186 Hundred eighty-six 2186-01-01
377 188 Hundred eighty-eight 2188-01-01
378 ALTER TABLE tp_l EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
379 ALTER TABLE tp_l EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
380 SELECT * FROM tp_l PARTITION (p2);
381 a b c
382 112 Hundred twelve 2112-01-01
383 114 Hundred fourteen 2114-01-01
384 116 Hundred sixteen 2116-01-01
385 118 Hundred eightteen 2118-01-01
386 122 Hundred twenty-two 2122-01-01
387 124 Hundred twenty-four 2124-01-01
388 126 Hundred twenty-six 2126-01-01
389 128 Hundred twenty-eight 2128-01-01
390 162 Hundred sixty-two 2162-01-01
391 164 Hundred sixty-four 2164-01-01
392 166 Hundred sixty-six 2166-01-01
393 168 Hundred sixty-eight 2168-01-01
394 182 Hundred eighty-two 2182-01-01
395 184 Hundred eighty-four 2184-01-01
396 186 Hundred eighty-six 2186-01-01
397 188 Hundred eighty-eight 2188-01-01
398 ALTER TABLE tp_l ANALYZE PARTITION p0;
399 Table Op Msg_type Msg_text
400 test.tp_l analyze status OK
401 ALTER TABLE tp_l OPTIMIZE PARTITION p0;
402 Table Op Msg_type Msg_text
403 test.tp_l optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
404 test.tp_l optimize status OK
405 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
406 SELECT * FROM tp_l PARTITION (p0);
407 a b c
408 2 Two 2002-01-01
409 4 Four 2004-01-01
410 6 Six 2006-01-01
411 8 Eight 2008-01-01
412 SELECT * FROM tp_l PARTITION (p1);
413 a b c
414 12 twelve 2012-01-01
415 14 Fourteen 2014-01-01
416 16 Sixteen 2016-01-01
417 18 Eightteen 2018-01-01
418 SELECT * FROM tp_l PARTITION (p2);
419 a b c
420 112 Hundred twelve 2112-01-01
421 114 Hundred fourteen 2114-01-01
422 116 Hundred sixteen 2116-01-01
423 118 Hundred eightteen 2118-01-01
424 122 Hundred twenty-two 2122-01-01
425 124 Hundred twenty-four 2124-01-01
426 126 Hundred twenty-six 2126-01-01
427 128 Hundred twenty-eight 2128-01-01
428 162 Hundred sixty-two 2162-01-01
429 164 Hundred sixty-four 2164-01-01
430 166 Hundred sixty-six 2166-01-01
431 168 Hundred sixty-eight 2168-01-01
432 182 Hundred eighty-two 2182-01-01
433 184 Hundred eighty-four 2184-01-01
434 186 Hundred eighty-six 2186-01-01
435 188 Hundred eighty-eight 2188-01-01
436 CREATE TABLE t_11 LIKE tp_l;
437 ALTER TABLE t_11 REMOVE PARTITIONING;
438 INSERT INTO t_11 SELECT * FROM t_10;
439 INSERT INTO t_11 SELECT * FROM t_100;
440 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
441 SELECT * FROM tp_l PARTITION (p0);
442 a b c
443 1 One 2001-01-01
444 11 Eleven 2011-01-01
445 13 Thirdteen 2013-01-01
446 15 Fifeteen 2015-01-01
447 19 Nineteen 2019-01-01
448 3 Three 2003-01-01
449 5 Five 2005-01-01
450 9 Nine 2009-01-01
451 91 Ninety-one 2091-01-01
452 93 Ninety-three 2093-01-01
453 95 Ninety-five 2095-01-01
454 99 Ninety-nine 2099-01-01
455 DELETE FROM tp_l PARTITION (p1);
456 SELECT * FROM tp_l PARTITION (p1);
457 a b c
458 ALTER TABLE tp_l CHECK PARTITION p0 ;
459 Table Op Msg_type Msg_text
460 test.tp_l check error Found a misplaced row in part 0 should be in part 1:
461 a:11
462 test.tp_l check error Partition p0 returned error
463 test.tp_l check error Table upgrade required. Please do "REPAIR TABLE `tp_l`" or dump/reload to fix it!
464 ALTER TABLE tp_l REPAIR PARTITION p0 ;
465 Table Op Msg_type Msg_text
466 test.tp_l repair warning Moved 8 misplaced rows
467 test.tp_l repair status OK
468 SELECT * FROM tp_l PARTITION (p0);
469 a b c
470 1 One 2001-01-01
471 3 Three 2003-01-01
472 5 Five 2005-01-01
473 9 Nine 2009-01-01
474 SELECT * FROM tp_l PARTITION (p1);
475 a b c
476 11 Eleven 2011-01-01
477 13 Thirdteen 2013-01-01
478 15 Fifeteen 2015-01-01
479 19 Nineteen 2019-01-01
480 91 Ninety-one 2091-01-01
481 93 Ninety-three 2093-01-01
482 95 Ninety-five 2095-01-01
483 99 Ninety-nine 2099-01-01
484 SELECT * FROM tp_l PARTITION (p2);
485 a b c
486 112 Hundred twelve 2112-01-01
487 114 Hundred fourteen 2114-01-01
488 116 Hundred sixteen 2116-01-01
489 118 Hundred eightteen 2118-01-01
490 122 Hundred twenty-two 2122-01-01
491 124 Hundred twenty-four 2124-01-01
492 126 Hundred twenty-six 2126-01-01
493 128 Hundred twenty-eight 2128-01-01
494 162 Hundred sixty-two 2162-01-01
495 164 Hundred sixty-four 2164-01-01
496 166 Hundred sixty-six 2166-01-01
497 168 Hundred sixty-eight 2168-01-01
498 182 Hundred eighty-two 2182-01-01
499 184 Hundred eighty-four 2184-01-01
500 186 Hundred eighty-six 2186-01-01
501 188 Hundred eighty-eight 2188-01-01
502 DROP TABLE IF EXISTS t_11;
503 DELETE FROM t_10;
504 INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01');
505 DELETE FROM t_100;
506 INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01');
507 INSERT INTO t_100 VALUES (91, "Ninety-one", '2091-01-01'), (93, "Ninety-three", '2093-01-01'), (95, "Ninety-five", '2095-01-01'), (99, "Ninety-nine", '2099-01-01');
508 DELETE FROM tsp_l;
509 INSERT INTO tsp_l VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
510 INSERT INTO tsp_l VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
511 INSERT INTO tsp_l VALUES (112, "Hundred twelve", '2112-01-01'), (114, "Hundred fourteen", '2114-01-01'), (116, "Hundred sixteen", '2116-01-01'), (118, "Hundred eightteen", '2118-01-01');
512 INSERT INTO tsp_l VALUES (122, "Hundred twenty-two", '2122-01-01'), (124, "Hundred twenty-four", '2124-01-01'), (126, "Hundred twenty-six", '2126-01-01'), (128, "Hundred twenty-eight", '2128-01-01');
513 INSERT INTO tsp_l VALUES (162, "Hundred sixty-two", '2162-01-01'), (164, "Hundred sixty-four", '2164-01-01'), (166, "Hundred sixty-six", '2166-01-01'), (168, "Hundred sixty-eight", '2168-01-01');
514 INSERT INTO tsp_l VALUES (182, "Hundred eight-two", '2182-01-01'), (184, "Hundred eighty-four", '2184-01-01'), (186, "Hundred eighty-six", '2186-01-01'), (188, "Hundred eighty-eight", '2188-01-01');
515 ### Range sub/partitioned tables with/out validation
516 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
517 ERROR HY000: Found a row that does not match the partition
518 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_100 WITH VALIDATION;
519 ERROR HY000: Found a row that does not match the partition
520 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_100 WITHOUT VALIDATION;
521 SELECT * FROM t_100;
522 a b c
523 SELECT * FROM tsp_l PARTITION (sp00);
524 a b c
525 11 Eleven 2011-01-01
526 13 Thirdteen 2013-01-01
527 15 Fifeteen 2015-01-01
528 19 Nineteen 2019-01-01
529 91 Ninety-one 2091-01-01
530 93 Ninety-three 2093-01-01
531 95 Ninety-five 2095-01-01
532 99 Ninety-nine 2099-01-01
533 SELECT * FROM tsp_l PARTITION (sp00);
534 a b c
535 11 Eleven 2011-01-01
536 13 Thirdteen 2013-01-01
537 15 Fifeteen 2015-01-01
538 19 Nineteen 2019-01-01
539 91 Ninety-one 2091-01-01
540 93 Ninety-three 2093-01-01
541 95 Ninety-five 2095-01-01
542 99 Ninety-nine 2099-01-01
543 SELECT * FROM tsp_l PARTITION (sp22);
544 a b c
545 112 Hundred twelve 2112-01-01
546 122 Hundred twenty-two 2122-01-01
547 162 Hundred sixty-two 2162-01-01
548 182 Hundred eight-two 2182-01-01
549 ALTER TABLE tsp_l EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
550 SELECT * FROM tsp_l PARTITION (sp22);
551 a b c
552 SELECT * FROM tsp_l PARTITION (sp22);
553 a b c
554 ALTER TABLE tsp_l EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
555 SELECT * FROM tsp_l PARTITION (sp22);
556 a b c
557 112 Hundred twelve 2112-01-01
558 122 Hundred twenty-two 2122-01-01
559 162 Hundred sixty-two 2162-01-01
560 182 Hundred eight-two 2182-01-01
561 ALTER TABLE tsp_l ANALYZE PARTITION p0;
562 Table Op Msg_type Msg_text
563 test.tsp_l analyze status OK
564 ALTER TABLE tsp_l OPTIMIZE PARTITION p0;
565 Table Op Msg_type Msg_text
566 test.tsp_l optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
567 test.tsp_l optimize status OK
568 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
569 ERROR HY000: Found a row that does not match the partition
570 CREATE TABLE t_11 LIKE t_10;
571 INSERT INTO t_11 SELECT * FROM t_10;
572 INSERT INTO t_11 SELECT * FROM t_100;
573 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION;
574 SELECT * FROM tsp_l PARTITION (sp00);
575 a b c
576 1 One 2001-01-01
577 3 Three 2003-01-01
578 5 Five 2005-01-01
579 9 Nine 2009-01-01
580 SELECT * FROM tsp_l PARTITION (sp01);
581 a b c
582 6 Six 2006-01-01
583 SELECT * FROM tsp_l PARTITION (sp03);
584 a b c
585 8 Eight 2008-01-01
586 SELECT * FROM tsp_l PARTITION (sp04);
587 a b c
588 4 Four 2004-01-01
589 SELECT * FROM tsp_l PARTITION (sp02);
590 a b c
591 2 Two 2002-01-01
592 ALTER TABLE tsp_l CHECK PARTITION sp00 ;
593 Table Op Msg_type Msg_text
594 test.tsp_l check error Found a misplaced row in part 0 should be in part 1:
595 a:1
596 test.tsp_l check error Subpartition sp00 returned error
597 test.tsp_l check error Table upgrade required. Please do "REPAIR TABLE `tsp_l`" or dump/reload to fix it!
598 ALTER TABLE tsp_l REPAIR PARTITION sp00 ;
599 Table Op Msg_type Msg_text
600 test.tsp_l repair warning Moved 3 misplaced rows
601 test.tsp_l repair status OK
602 SELECT * FROM tsp_l PARTITION (sp00);
603 a b c
604 5 Five 2005-01-01
605 SELECT * FROM tsp_l PARTITION (sp01);
606 a b c
607 1 One 2001-01-01
608 6 Six 2006-01-01
609 SELECT * FROM tsp_l PARTITION (sp02);
610 a b c
611 2 Two 2002-01-01
612 SELECT * FROM tsp_l PARTITION (sp03);
613 a b c
614 3 Three 2003-01-01
615 8 Eight 2008-01-01
616 SELECT * FROM tsp_l PARTITION (sp04);
617 a b c
618 4 Four 2004-01-01
619 9 Nine 2009-01-01
620 DROP TABLE IF EXISTS t_11;
621 DROP TABLE IF EXISTS t_10;
622 DROP TABLE IF EXISTS t_100;
623 DROP TABLE IF EXISTS t_1000;
624 DROP TABLE IF EXISTS tp_r;
625 DROP TABLE IF EXISTS tp_rvar;
626 DROP TABLE IF EXISTS tp_rintvar;
627 DROP TABLE IF EXISTS tp_rdate;
628 DROP TABLE IF EXISTS tp_rintdate;
629 DROP TABLE IF EXISTS tsp_r;
630 DROP TABLE IF EXISTS tsp_rvar;
631 DROP TABLE IF EXISTS tsp_rvar1;
632 DROP TABLE IF EXISTS tp_l;
633 DROP TABLE IF EXISTS tsp_l;
634 DROP TABLE IF EXISTS tsp_lvar;
635 DROP TABLE IF EXISTS tsp_ldate;
636 DROP TABLE IF EXISTS tp_k;
637 DROP TABLE IF EXISTS tp_kvar;
638 DROP TABLE IF EXISTS tp_kdate;
639 DROP TABLE IF EXISTS tp_h;
640 DROP TABLE IF EXISTS tp_hvar;
641 DROP TABLE IF EXISTS tp_hdate;
642 DROP TABLE IF EXISTS tsp_00;
643 DROP TABLE IF EXISTS tsp_01;
644 DROP TABLE IF EXISTS tsp_02;
645 DROP TABLE IF EXISTS tsp_03;
646 DROP TABLE IF EXISTS tsp_04;
647 DROP TABLE IF EXISTS t_empty;
648 DROP TABLE IF EXISTS t_null;