Debian Codegraph @

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

raw
1 create table t1 (a tinyint unsigned not null, primary key(a)) engine='InnoDB'
2 partition by key (a) (
3 partition pa1 max_rows=20 min_rows=2,
4 partition pa2 max_rows=30 min_rows=3,
5 partition pa3 max_rows=30 min_rows=4,
6 partition pa4 max_rows=40 min_rows=2);
7 show create table t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `a` tinyint unsigned NOT NULL,
11 PRIMARY KEY (`a`)
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
13 /*!50100 PARTITION BY KEY (a)
14 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
15 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
16 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
17 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
18 insert into t1 values (255), (254), (253), (252), (1), (2), (128);
19 select * from t1;
20 a
21 1
22 128
23 2
24 252
25 253
26 254
27 255
28 select * from t1 where a=253;
29 a
30 253
31 delete from t1 where a=253;
32 select * from t1;
33 a
34 1
35 128
36 2
37 252
38 254
39 255
40 drop table t1;
41 create table t2 (a tinyint unsigned not null, primary key(a)) engine='InnoDB'
42 partition by key (a) partitions 8;
43 show create table t2;
44 Table Create Table
45 t2 CREATE TABLE `t2` (
46 `a` tinyint unsigned NOT NULL,
47 PRIMARY KEY (`a`)
48 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
49 /*!50100 PARTITION BY KEY (a)
50 PARTITIONS 8 */
51 insert into t2 values (255), (254), (253), (252);
52 select * from t2;
53 a
54 252
55 253
56 254
57 255
58 select * from t2 where a=253;
59 a
60 253
61 delete from t2 where a=253;
62 select * from t2;
63 a
64 252
65 254
66 255
67 delete from t2;
68 255 inserts;
69 select count(*) from t2;
70 count(*)
71 255
72 drop table t2;
73 create table t3 (a tinyint not null, primary key(a)) engine='InnoDB'
74 partition by key (a) partitions 7;
75 show create table t3;
76 Table Create Table
77 t3 CREATE TABLE `t3` (
78 `a` tinyint NOT NULL,
79 PRIMARY KEY (`a`)
80 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
81 /*!50100 PARTITION BY KEY (a)
82 PARTITIONS 7 */
83 insert into t3 values (127), (126), (125), (124), (-128), (-127), (1), (-1), (0);
84 select * from t3;
85 a
86 -1
87 -127
88 -128
89 0
90 1
91 124
92 125
93 126
94 127
95 select * from t3 where a=125;
96 a
97 125
98 delete from t3 where a=125;
99 select * from t3;
100 a
101 -1
102 -127
103 -128
104 0
105 1
106 124
107 126
108 127
109 drop table t3;
110 create table t1 (a smallint unsigned not null, primary key(a)) engine='InnoDB'
111 partition by key (a) (
112 partition pa1 max_rows=20 min_rows=2,
113 partition pa2 max_rows=30 min_rows=3,
114 partition pa3 max_rows=30 min_rows=4,
115 partition pa4 max_rows=40 min_rows=2);
116 show create table t1;
117 Table Create Table
118 t1 CREATE TABLE `t1` (
119 `a` smallint unsigned NOT NULL,
120 PRIMARY KEY (`a`)
121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
122 /*!50100 PARTITION BY KEY (a)
123 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
124 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
125 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
126 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
127 insert into t1 values (65535), (65534), (65533), (65532), (1), (2), (256);
128 select * from t1;
129 a
130 1
131 2
132 256
133 65532
134 65533
135 65534
136 65535
137 select * from t1 where a=65533;
138 a
139 65533
140 delete from t1 where a=65533;
141 select * from t1;
142 a
143 1
144 2
145 256
146 65532
147 65534
148 65535
149 drop table t1;
150 create table t2 (a smallint unsigned not null, primary key(a)) engine='InnoDB'
151 partition by key (a) partitions 8;
152 show create table t2;
153 Table Create Table
154 t2 CREATE TABLE `t2` (
155 `a` smallint unsigned NOT NULL,
156 PRIMARY KEY (`a`)
157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
158 /*!50100 PARTITION BY KEY (a)
159 PARTITIONS 8 */
160 insert into t2 values (65535), (65534), (65533), (65532);
161 select * from t2;
162 a
163 65532
164 65533
165 65534
166 65535
167 select * from t2 where a=65533;
168 a
169 65533
170 delete from t2 where a=65533;
171 select * from t2;
172 a
173 65532
174 65534
175 65535
176 delete from t2;
177 1024 inserts;
178 select count(*) from t2;
179 count(*)
180 1024
181 drop table t2;
182 create table t3 (a smallint not null, primary key(a)) engine='InnoDB'
183 partition by key (a) partitions 7;
184 show create table t3;
185 Table Create Table
186 t3 CREATE TABLE `t3` (
187 `a` smallint NOT NULL,
188 PRIMARY KEY (`a`)
189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
190 /*!50100 PARTITION BY KEY (a)
191 PARTITIONS 7 */
192 insert into t3 values (32767), (32766), (32765), (32764), (-32768), (-32767), (1), (-1), (0);
193 select * from t3;
194 a
195 -1
196 -32767
197 -32768
198 0
199 1
200 32764
201 32765
202 32766
203 32767
204 select * from t3 where a=32765;
205 a
206 32765
207 delete from t3 where a=32765;
208 select * from t3;
209 a
210 -1
211 -32767
212 -32768
213 0
214 1
215 32764
216 32766
217 32767
218 drop table t3;
219 create table t1 (a int unsigned not null, primary key(a)) engine='InnoDB'
220 partition by key (a) (
221 partition pa1 max_rows=20 min_rows=2,
222 partition pa2 max_rows=30 min_rows=3,
223 partition pa3 max_rows=30 min_rows=4,
224 partition pa4 max_rows=40 min_rows=2);
225 show create table t1;
226 Table Create Table
227 t1 CREATE TABLE `t1` (
228 `a` int unsigned NOT NULL,
229 PRIMARY KEY (`a`)
230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
231 /*!50100 PARTITION BY KEY (a)
232 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
233 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
234 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
235 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
236 insert into t1 values (4294967295), (4294967294), (4294967293), (4294967292), (1), (2), (65535);
237 select * from t1;
238 a
239 1
240 2
241 4294967292
242 4294967293
243 4294967294
244 4294967295
245 65535
246 select * from t1 where a=4294967293;
247 a
248 4294967293
249 delete from t1 where a=4294967293;
250 select * from t1;
251 a
252 1
253 2
254 4294967292
255 4294967294
256 4294967295
257 65535
258 drop table t1;
259 create table t2 (a int unsigned not null, primary key(a)) engine='InnoDB'
260 partition by key (a) partitions 8;
261 show create table t2;
262 Table Create Table
263 t2 CREATE TABLE `t2` (
264 `a` int unsigned NOT NULL,
265 PRIMARY KEY (`a`)
266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
267 /*!50100 PARTITION BY KEY (a)
268 PARTITIONS 8 */
269 insert into t2 values (4294967295), (4294967294), (4294967293), (4294967292);
270 select * from t2;
271 a
272 4294967292
273 4294967293
274 4294967294
275 4294967295
276 select * from t2 where a=4294967293;
277 a
278 4294967293
279 delete from t2 where a=4294967293;
280 select * from t2;
281 a
282 4294967292
283 4294967294
284 4294967295
285 delete from t2;
286 1024 inserts;
287 select count(*) from t2;
288 count(*)
289 1024
290 drop table t2;
291 create table t3 (a int not null, primary key(a)) engine='InnoDB'
292 partition by key (a) partitions 7;
293 show create table t3;
294 Table Create Table
295 t3 CREATE TABLE `t3` (
296 `a` int NOT NULL,
297 PRIMARY KEY (`a`)
298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
299 /*!50100 PARTITION BY KEY (a)
300 PARTITIONS 7 */
301 insert into t3 values (2147483647), (2147483646), (2147483645), (2147483644), (-2147483648), (-2147483647), (1), (-1), (0);
302 select * from t3;
303 a
304 -1
305 -2147483647
306 -2147483648
307 0
308 1
309 2147483644
310 2147483645
311 2147483646
312 2147483647
313 select * from t3 where a=2147483645;
314 a
315 2147483645
316 delete from t3 where a=2147483645;
317 select * from t3;
318 a
319 -1
320 -2147483647
321 -2147483648
322 0
323 1
324 2147483644
325 2147483646
326 2147483647
327 drop table t3;
328 create table t1 (a mediumint unsigned not null, primary key(a)) engine='InnoDB'
329 partition by key (a) (
330 partition pa1 max_rows=20 min_rows=2,
331 partition pa2 max_rows=30 min_rows=3,
332 partition pa3 max_rows=30 min_rows=4,
333 partition pa4 max_rows=40 min_rows=2);
334 show create table t1;
335 Table Create Table
336 t1 CREATE TABLE `t1` (
337 `a` mediumint unsigned NOT NULL,
338 PRIMARY KEY (`a`)
339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
340 /*!50100 PARTITION BY KEY (a)
341 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
342 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
343 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
344 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
345 insert into t1 values (16777215), (16777214), (16777213), (16777212), (1), (2), (65535);
346 select * from t1;
347 a
348 1
349 16777212
350 16777213
351 16777214
352 16777215
353 2
354 65535
355 select * from t1 where a=16777213;
356 a
357 16777213
358 delete from t1 where a=16777213;
359 select * from t1;
360 a
361 1
362 16777212
363 16777214
364 16777215
365 2
366 65535
367 drop table t1;
368 create table t2 (a mediumint unsigned not null, primary key(a)) engine='InnoDB'
369 partition by key (a) partitions 8;
370 show create table t2;
371 Table Create Table
372 t2 CREATE TABLE `t2` (
373 `a` mediumint unsigned NOT NULL,
374 PRIMARY KEY (`a`)
375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
376 /*!50100 PARTITION BY KEY (a)
377 PARTITIONS 8 */
378 insert into t2 values (16777215), (16777214), (16777213), (16777212);
379 select * from t2;
380 a
381 16777212
382 16777213
383 16777214
384 16777215
385 select * from t2 where a=16777213;
386 a
387 16777213
388 delete from t2 where a=16777213;
389 select * from t2;
390 a
391 16777212
392 16777214
393 16777215
394 delete from t2;
395 1024 inserts;
396 select count(*) from t2;
397 count(*)
398 1024
399 drop table t2;
400 create table t3 (a mediumint not null, primary key(a)) engine='InnoDB'
401 partition by key (a) partitions 7;
402 show create table t3;
403 Table Create Table
404 t3 CREATE TABLE `t3` (
405 `a` mediumint NOT NULL,
406 PRIMARY KEY (`a`)
407 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
408 /*!50100 PARTITION BY KEY (a)
409 PARTITIONS 7 */
410 insert into t3 values (8388607), (8388606), (8388605), (8388604), (-8388608), (-8388607), (1), (-1), (0);
411 select * from t3;
412 a
413 -1
414 -8388607
415 -8388608
416 0
417 1
418 8388604
419 8388605
420 8388606
421 8388607
422 select * from t3 where a=8388605;
423 a
424 8388605
425 delete from t3 where a=8388605;
426 select * from t3;
427 a
428 -1
429 -8388607
430 -8388608
431 0
432 1
433 8388604
434 8388606
435 8388607
436 drop table t3;
437 create table t1 (a bigint unsigned not null, primary key(a)) engine='InnoDB'
438 partition by key (a) (
439 partition pa1 max_rows=20 min_rows=2,
440 partition pa2 max_rows=30 min_rows=3,
441 partition pa3 max_rows=30 min_rows=4,
442 partition pa4 max_rows=40 min_rows=2);
443 show create table t1;
444 Table Create Table
445 t1 CREATE TABLE `t1` (
446 `a` bigint unsigned NOT NULL,
447 PRIMARY KEY (`a`)
448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
449 /*!50100 PARTITION BY KEY (a)
450 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
451 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
452 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
453 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
454 insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612), (1), (2), (65535);
455 select * from t1;
456 a
457 1
458 18446744073709551612
459 18446744073709551613
460 18446744073709551614
461 18446744073709551615
462 2
463 65535
464 select * from t1 where a=-2;
465 a
466 delete from t1 where a=-2;
467 select * from t1;
468 a
469 1
470 18446744073709551612
471 18446744073709551613
472 18446744073709551614
473 18446744073709551615
474 2
475 65535
476 select * from t1 where a=18446744073709551615;
477 a
478 18446744073709551615
479 delete from t1 where a=18446744073709551615;
480 select * from t1;
481 a
482 1
483 18446744073709551612
484 18446744073709551613
485 18446744073709551614
486 2
487 65535
488 drop table t1;
489 create table t2 (a bigint unsigned not null, primary key(a)) engine='InnoDB'
490 partition by key (a) partitions 8;
491 show create table t2;
492 Table Create Table
493 t2 CREATE TABLE `t2` (
494 `a` bigint unsigned NOT NULL,
495 PRIMARY KEY (`a`)
496 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
497 /*!50100 PARTITION BY KEY (a)
498 PARTITIONS 8 */
499 insert into t2 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);
500 select * from t2;
501 a
502 18446744073709551612
503 18446744073709551613
504 18446744073709551614
505 18446744073709551615
506 select * from t2 where a=18446744073709551615;
507 a
508 18446744073709551615
509 delete from t2 where a=18446744073709551615;
510 select * from t2;
511 a
512 18446744073709551612
513 18446744073709551613
514 18446744073709551614
515 delete from t2;
516 1024 inserts;
517 select count(*) from t2;
518 count(*)
519 1024
520 drop table t2;
521 create table t3 (a bigint not null, primary key(a)) engine='InnoDB'
522 partition by key (a) partitions 7;
523 show create table t3;
524 Table Create Table
525 t3 CREATE TABLE `t3` (
526 `a` bigint NOT NULL,
527 PRIMARY KEY (`a`)
528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
529 /*!50100 PARTITION BY KEY (a)
530 PARTITIONS 7 */
531 insert into t3 values (9223372036854775807), (9223372036854775806), (9223372036854775805), (9223372036854775804), (-9223372036854775808), (-9223372036854775807), (1), (-1), (0);
532 select * from t3;
533 a
534 -1
535 -9223372036854775807
536 -9223372036854775808
537 0
538 1
539 9223372036854775804
540 9223372036854775805
541 9223372036854775806
542 9223372036854775807
543 select * from t3 where a=9223372036854775806;
544 a
545 9223372036854775806
546 delete from t3 where a=9223372036854775806;
547 select * from t3;
548 a
549 -1
550 -9223372036854775807
551 -9223372036854775808
552 0
553 1
554 9223372036854775804
555 9223372036854775805
556 9223372036854775807
557 drop table t3;