Debian Codegraph @

mysql-test/suite/parts/inc/part_exch_valid_range.inc

raw
1 use test;
2
3 call mtr.add_suppression("row in wrong partition.*from REBUILD/REORGANIZED");
4 #--disable_result_log
5 #--disable_query_log
6 --source suite/parts/inc/part_exch_ext_tabs.inc
7 #--enable_result_log
8 #--enable_query_log
9
10 --echo #############################################################################
11 --echo ### Range partitioned tables with/out validation
12
13 ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
14 --sorted_result
15 SELECT * FROM tp_r PARTITION (p0);
16 --sorted_result
17 SELECT * FROM tp_r PARTITION (p1);
18 --sorted_result
19 SELECT * FROM tp_r PARTITION (p2);
20 --sorted_result
21 SELECT * FROM t_10;
22
23 --error ER_ROW_DOES_NOT_MATCH_PARTITION
24 ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION;
25 ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION;
26 --sorted_result
27 SELECT * FROM tp_r PARTITION (p0);
28 --sorted_result
29 SELECT * FROM tp_r PARTITION (p1);
30 --sorted_result
31 SELECT * FROM tp_r PARTITION (p2);
32 --sorted_result
33 SELECT * FROM t_100;
34
35 #--error ER_ROW_IN_WRONG_PARTITION
36 #ALTER TABLE tp_r REBUILD PARTITION p0;
37 #ALTER TABLE tp_r REORGANIZE PARTITION p2 INTO
38 # (PARTITION p2 VALUES LESS THAN (1500));
39 ALTER TABLE tp_r EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
40 #--error ER_ROW_IN_WRONG_PARTITION
41 #ALTER TABLE tp_r REORGANIZE PARTITION p2 INTO
42 # (PARTITION p2 VALUES LESS THAN (1500));
43 ALTER TABLE tp_r EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
44 --sorted_result
45 SELECT * FROM tp_r PARTITION (p2);
46 ALTER TABLE tp_r ANALYZE PARTITION p0;
47 ALTER TABLE tp_r OPTIMIZE PARTITION p0;
48
49
50 # Values t_10 (not partitioned)
51 DELETE FROM t_10;
52 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');
53
54 # Values t_100 (not partitioned)
55 DELETE FROM t_100;
56 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');
57 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');
58
59 CREATE TABLE t_11 LIKE tp_r;
60 ALTER TABLE t_11 REMOVE PARTITIONING;
61 INSERT INTO t_11 SELECT * FROM t_10;
62 INSERT INTO t_11 SELECT * FROM t_100;
63 ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
64 --sorted_result
65 SELECT * FROM tp_r PARTITION (p0);
66 DELETE FROM tp_r PARTITION (p1);
67 --sorted_result
68 SELECT * FROM tp_r PARTITION (p1);
69 --sorted_result
70 SELECT * FROM t_11;
71 ALTER TABLE tp_r CHECK PARTITION p0 ;
72 ALTER TABLE tp_r REPAIR PARTITION p0 ;
73 --sorted_result
74 SELECT * FROM tp_r PARTITION (p0);
75 --sorted_result
76 SELECT * FROM tp_r PARTITION (p1);
77 --sorted_result
78 SELECT * FROM tp_r PARTITION (p2);
79 DROP TABLE IF EXISTS t_11;
80
81 # Values t_10 (not partitioned)
82 DELETE FROM t_10;
83 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');
84
85 # Values t_100 (not partitioned)
86 DELETE FROM t_100;
87 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');
88 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');
89
90 # Values tps (subpartitions)
91 DELETE FROM tsp_r;
92 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');
93 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');
94 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');
95 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');
96 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');
97 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');
98
99 --echo ### Range sub/partitioned tables with/out validation
100
101 --error ER_ROW_DOES_NOT_MATCH_PARTITION
102 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
103 --sorted_result
104 --error ER_ROW_DOES_NOT_MATCH_PARTITION
105 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_100 WITH VALIDATION;
106 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_100 WITHOUT VALIDATION;
107 --sorted_result
108 SELECT * FROM t_100;
109 --sorted_result
110 SELECT * FROM tsp_r PARTITION (sp00);
111 # REBUILD is a noop on subpartitions.
112 #ALTER TABLE tsp_r REBUILD PARTITION sp00;
113 # Bug19075764 ALTER... REBUILD WITH MISPLACED ROWS IS CRASHING IN MYISAM.
114 #ALTER TABLE tsp_r REORGANIZE PARTITION p2 INTO
115 # (PARTITION p2 VALUES LESS THAN (1500)
116 # (SUBPARTITION sp20,
117 # SUBPARTITION sp21,
118 # SUBPARTITION sp22,
119 # SUBPARTITION sp23,
120 # SUBPARTITION sp24));
121 --sorted_result
122 SELECT * FROM tsp_r PARTITION (sp22);
123 ALTER TABLE tsp_r EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
124 --sorted_result
125 SELECT * FROM tsp_r PARTITION (sp22);
126 #--error ER_ROW_IN_WRONG_PARTITION
127 #ALTER TABLE tsp_r REORGANIZE PARTITION p2 INTO
128 # (PARTITION p2 VALUES LESS THAN (1500)
129 # (SUBPARTITION sp20,
130 # SUBPARTITION sp21,
131 # SUBPARTITION sp22,
132 # SUBPARTITION sp23,
133 # SUBPARTITION sp24));
134 --sorted_result
135 SELECT * FROM tsp_r PARTITION (sp22);
136 ALTER TABLE tsp_r EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
137 --sorted_result
138 SELECT * FROM tsp_r PARTITION (sp22);
139 ALTER TABLE tsp_r ANALYZE PARTITION p0;
140 ALTER TABLE tsp_r OPTIMIZE PARTITION p0;
141 --error ER_ROW_DOES_NOT_MATCH_PARTITION
142 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
143 CREATE TABLE t_11 LIKE t_10;
144 INSERT INTO t_11 SELECT * FROM t_10;
145 INSERT INTO t_11 SELECT * FROM t_100;
146 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION;
147 --sorted_result
148 SELECT * FROM tsp_r PARTITION (sp00);
149 --sorted_result
150 SELECT * FROM tsp_r PARTITION (sp01);
151 --sorted_result
152 SELECT * FROM tsp_r PARTITION (sp03);
153 --sorted_result
154 SELECT * FROM tsp_r PARTITION (sp04);
155 --sorted_result
156 SELECT * FROM tsp_r PARTITION (sp02);
157 ALTER TABLE tsp_r CHECK PARTITION sp00 ;
158 ALTER TABLE tsp_r REPAIR PARTITION sp00 ;
159 --sorted_result
160 SELECT * FROM tsp_r PARTITION (sp00);
161 --sorted_result
162 SELECT * FROM tsp_r PARTITION (sp01);
163 --sorted_result
164 SELECT * FROM tsp_r PARTITION (sp02);
165 --sorted_result
166 SELECT * FROM tsp_r PARTITION (sp03);
167 --sorted_result
168 SELECT * FROM tsp_r PARTITION (sp04);
169 DROP TABLE IF EXISTS t_11;
170
171 # Values t_10 (not partitioned)
172 DELETE FROM t_10;
173 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');
174
175 # Values t_100 (not partitioned)
176 DELETE FROM t_100;
177 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');
178 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');
179
180 CREATE TABLE t_11 LIKE tp_rintvar;
181 ALTER TABLE t_11 REMOVE PARTITIONING;
182 INSERT INTO t_11 SELECT * FROM t_10;
183 INSERT INTO t_11 SELECT * FROM t_100;
184 --error ER_ROW_DOES_NOT_MATCH_PARTITION
185 ALTER TABLE tp_rintvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION;
186 ALTER TABLE tp_rintvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
187 --sorted_result
188 SELECT * FROM tp_rintvar PARTITION (p0);
189 DELETE FROM tp_rintvar PARTITION (p1);
190 --sorted_result
191 SELECT * FROM tp_rintvar PARTITION (p1);
192 --sorted_result
193 SELECT * FROM t_11;
194 ALTER TABLE tp_rintvar CHECK PARTITION p0 ;
195 ALTER TABLE tp_rintvar REPAIR PARTITION p0 ;
196 --sorted_result
197 SELECT * FROM tp_rintvar PARTITION (p0);
198 --sorted_result
199 SELECT * FROM tp_rintvar PARTITION (p1);
200 --sorted_result
201 SELECT * FROM tp_rintvar PARTITION (p2);
202 DROP TABLE IF EXISTS t_11;
203
204 # Values t_10 (not partitioned)
205 DELETE FROM t_10;
206 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');
207
208 # Values t_100 (not partitioned)
209 DELETE FROM t_100;
210 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');
211 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');
212
213 CREATE TABLE t_11 LIKE tp_rvar;
214 ALTER TABLE t_11 REMOVE PARTITIONING;
215 INSERT INTO t_11 SELECT * FROM t_10;
216 INSERT INTO t_11 SELECT * FROM t_100;
217 --error ER_ROW_DOES_NOT_MATCH_PARTITION
218 ALTER TABLE tp_rvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION;
219 ALTER TABLE tp_rvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
220 --sorted_result
221 SELECT * FROM tp_rvar PARTITION (p0);
222 DELETE FROM tp_rvar PARTITION (p1);
223 --sorted_result
224 SELECT * FROM tp_rvar PARTITION (p1);
225 --sorted_result
226 SELECT * FROM t_11;
227 ALTER TABLE tp_rvar CHECK PARTITION p0 ;
228 ALTER TABLE tp_rvar REPAIR PARTITION p0 ;
229 --sorted_result
230 SELECT * FROM tp_rvar PARTITION (p0);
231 --sorted_result
232 SELECT * FROM tp_rvar PARTITION (p1);
233 --sorted_result
234 SELECT * FROM tp_rvar PARTITION (p2);
235 DROP TABLE IF EXISTS t_11;
236
237 # Values t_10 (not partitioned)
238 DELETE FROM t_10;
239 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');
240
241 # Values t_100 (not partitioned)
242 DELETE FROM t_100;
243 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');
244 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');
245
246 CREATE TABLE t_11 LIKE tp_rintdate;
247 ALTER TABLE t_11 REMOVE PARTITIONING;
248 INSERT INTO t_11 SELECT * FROM t_10;
249 INSERT INTO t_11 SELECT * FROM t_100;
250 --error ER_ROW_DOES_NOT_MATCH_PARTITION
251 ALTER TABLE tp_rintdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION;
252 ALTER TABLE tp_rintdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
253 --sorted_result
254 SELECT * FROM tp_rintdate PARTITION (p0);
255 DELETE FROM tp_rintdate PARTITION (p1);
256 --sorted_result
257 SELECT * FROM tp_rintdate PARTITION (p1);
258 --sorted_result
259 SELECT * FROM t_11;
260 ALTER TABLE tp_rintdate CHECK PARTITION p0 ;
261 ALTER TABLE tp_rintdate REPAIR PARTITION p0 ;
262 --sorted_result
263 SELECT * FROM tp_rintdate PARTITION (p0);
264 --sorted_result
265 SELECT * FROM tp_rintdate PARTITION (p1);
266 --sorted_result
267 SELECT * FROM tp_rintdate PARTITION (p2);
268 DROP TABLE IF EXISTS t_11;
269
270 # Values t_10 (not partitioned)
271 DELETE FROM t_10;
272 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');
273
274 # Values t_100 (not partitioned)
275 DELETE FROM t_100;
276 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');
277 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');
278
279 CREATE TABLE t_11 LIKE tp_rdate;
280 ALTER TABLE t_11 REMOVE PARTITIONING;
281 INSERT INTO t_11 SELECT * FROM t_10;
282 INSERT INTO t_11 SELECT * FROM t_100;
283 --error ER_ROW_DOES_NOT_MATCH_PARTITION
284 ALTER TABLE tp_rdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION;
285 ALTER TABLE tp_rdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
286 --sorted_result
287 SELECT * FROM tp_rdate PARTITION (p0);
288 DELETE FROM tp_rdate PARTITION (p1);
289 --sorted_result
290 SELECT * FROM tp_rdate PARTITION (p1);
291 --sorted_result
292 SELECT * FROM t_11;
293 ALTER TABLE tp_rdate CHECK PARTITION p0 ;
294 ALTER TABLE tp_rdate REPAIR PARTITION p0 ;
295 --sorted_result
296 SELECT * FROM tp_rdate PARTITION (p0);
297 --sorted_result
298 SELECT * FROM tp_rdate PARTITION (p1);
299 --sorted_result
300 SELECT * FROM tp_rdate PARTITION (p2);
301 DROP TABLE IF EXISTS t_11;
302
303 # Values t_10 (not partitioned)
304 DELETE FROM t_10;
305 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');
306
307 # Values t_100 (not partitioned)
308 DELETE FROM t_100;
309 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');
310 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');
311
312 CREATE TABLE t_11 LIKE tsp_rvar;
313 ALTER TABLE t_11 REMOVE PARTITIONING;
314 INSERT INTO t_11 SELECT * FROM t_10;
315 INSERT INTO t_11 SELECT * FROM t_100;
316 --error ER_ROW_DOES_NOT_MATCH_PARTITION
317 ALTER TABLE tsp_rvar EXCHANGE PARTITION sp00 WITH TABLE t_11 WITH VALIDATION;
318 ALTER TABLE tsp_rvar EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION;
319 --sorted_result
320 SELECT * FROM tsp_rvar PARTITION (sp00);
321 DELETE FROM tsp_rvar PARTITION (sp01);
322 --sorted_result
323 SELECT * FROM tsp_rvar PARTITION (sp01);
324 --sorted_result
325 SELECT * FROM t_11;
326 ALTER TABLE tsp_rvar CHECK PARTITION p0 ;
327 ALTER TABLE tsp_rvar REPAIR PARTITION p0 ;
328 --sorted_result
329 SELECT * FROM tsp_rvar PARTITION (sp00);
330 --sorted_result
331 SELECT * FROM tsp_rvar PARTITION (sp01);
332 --sorted_result
333 SELECT * FROM tsp_rvar PARTITION (sp02);
334 --sorted_result
335 SELECT * FROM tsp_rvar PARTITION (sp03);
336 --sorted_result
337 SELECT * FROM tsp_rvar PARTITION (sp04);
338 DROP TABLE IF EXISTS t_11;
339
340 # Values t_10 (not partitioned)
341 DELETE FROM t_10;
342 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');
343
344 # Values t_100 (not partitioned)
345 DELETE FROM t_100;
346 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');
347 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');
348
349 CREATE TABLE t_11 LIKE tsp_rvar1;
350 ALTER TABLE t_11 REMOVE PARTITIONING;
351 INSERT INTO t_11 SELECT * FROM t_10;
352 INSERT INTO t_11 SELECT * FROM t_100;
353 --error ER_ROW_DOES_NOT_MATCH_PARTITION
354 ALTER TABLE tsp_rvar1 EXCHANGE PARTITION p0sp0 WITH TABLE t_11 WITH VALIDATION;
355 ALTER TABLE tsp_rvar1 EXCHANGE PARTITION p0sp0 WITH TABLE t_11 WITHOUT VALIDATION;
356 --sorted_result
357 SELECT * FROM tsp_rvar1 PARTITION (p0sp0);
358 DELETE FROM tsp_rvar1 PARTITION (p0sp1);
359 --sorted_result
360 SELECT * FROM tsp_rvar1 PARTITION (p0sp1);
361 --sorted_result
362 SELECT * FROM t_11;
363 ALTER TABLE tsp_rvar1 CHECK PARTITION p0 ;
364 ALTER TABLE tsp_rvar1 REPAIR PARTITION p0 ;
365 --sorted_result
366 SELECT * FROM tsp_rvar1 PARTITION (p0sp0);
367 --sorted_result
368 SELECT * FROM tsp_rvar1 PARTITION (p0sp1);
369 --sorted_result
370 SELECT * FROM tsp_rvar1 PARTITION (p0sp2);
371 --sorted_result
372 SELECT * FROM tsp_rvar1 PARTITION (p0sp3);
373 --sorted_result
374 SELECT * FROM tsp_rvar1 PARTITION (p0sp4);
375 DROP TABLE IF EXISTS t_11;
376
377 --source suite/parts/inc/part_exch_drop_ext_tabs.inc
378