Debian Codegraph @

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

raw
1 DROP TABLE IF EXISTS t1;
2 # test without partitioning for reference
3 CREATE TABLE t1 (
4 c1 INT NOT NULL AUTO_INCREMENT,
5 PRIMARY KEY (c1))
6 ENGINE='InnoDB';
7 SHOW CREATE TABLE t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `c1` int(11) NOT NULL AUTO_INCREMENT,
11 PRIMARY KEY (`c1`)
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
13 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
14 AND TABLE_NAME='t1';
15 AUTO_INCREMENT
16 1
17 INSERT INTO t1 VALUES (2);
18 INSERT INTO t1 VALUES (4);
19 INSERT INTO t1 VALUES (NULL);
20 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
21 AND TABLE_NAME='t1';
22 AUTO_INCREMENT
23 6
24 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
25 AND TABLE_NAME='t1';
26 AUTO_INCREMENT
27 6
28 INSERT INTO t1 VALUES (0);
29 INSERT INTO t1 VALUES (5), (16);
30 INSERT INTO t1 VALUES (17);
31 INSERT INTO t1 VALUES (19), (NULL);
32 INSERT INTO t1 VALUES (NULL), (10), (NULL);
33 INSERT INTO t1 VALUES (NULL);
34 SET INSERT_ID = 30;
35 INSERT INTO t1 VALUES (NULL);
36 SET INSERT_ID = 29;
37 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
38 INSERT INTO t1 VALUES (NULL);
39 UPDATE t1 SET c1 = 50 WHERE c1 = 17;
40 UPDATE t1 SET c1 = 51 WHERE c1 = 19;
41 FLUSH TABLES;
42 UPDATE t1 SET c1 = 40 WHERE c1 = 50;
43 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
44 AND TABLE_NAME='t1';
45 AUTO_INCREMENT
46 52
47 UPDATE t1 SET c1 = NULL WHERE c1 = 4;
48 Warnings:
49 Warning 1048 Column 'c1' cannot be null
50 INSERT INTO t1 VALUES (NULL);
51 INSERT INTO t1 VALUES (NULL);
52 SELECT * FROM t1 ORDER BY c1;
53 c1
54 0
55 2
56 5
57 6
58 10
59 20
60 22
61 23
62 25
63 30
64 31
65 40
66 51
67 52
68 53
69 DROP TABLE t1;
70 CREATE TABLE t1 (
71 c1 INT NOT NULL AUTO_INCREMENT,
72 PRIMARY KEY (c1))
73 ENGINE='InnoDB';
74 SHOW CREATE TABLE t1;
75 Table Create Table
76 t1 CREATE TABLE `t1` (
77 `c1` int(11) NOT NULL AUTO_INCREMENT,
78 PRIMARY KEY (`c1`)
79 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
80 FLUSH TABLE;
81 SHOW CREATE TABLE t1;
82 Table Create Table
83 t1 CREATE TABLE `t1` (
84 `c1` int(11) NOT NULL AUTO_INCREMENT,
85 PRIMARY KEY (`c1`)
86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
87 INSERT INTO t1 VALUES (4);
88 FLUSH TABLE;
89 SHOW CREATE TABLE t1;
90 Table Create Table
91 t1 CREATE TABLE `t1` (
92 `c1` int(11) NOT NULL AUTO_INCREMENT,
93 PRIMARY KEY (`c1`)
94 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
95 INSERT INTO t1 VALUES (NULL);
96 FLUSH TABLE;
97 SHOW CREATE TABLE t1;
98 Table Create Table
99 t1 CREATE TABLE `t1` (
100 `c1` int(11) NOT NULL AUTO_INCREMENT,
101 PRIMARY KEY (`c1`)
102 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
103 DELETE FROM t1;
104 INSERT INTO t1 VALUES (NULL);
105 SHOW CREATE TABLE t1;
106 Table Create Table
107 t1 CREATE TABLE `t1` (
108 `c1` int(11) NOT NULL AUTO_INCREMENT,
109 PRIMARY KEY (`c1`)
110 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
111 SELECT * FROM t1 ORDER BY c1;
112 c1
113 6
114 TRUNCATE TABLE t1;
115 INSERT INTO t1 VALUES (NULL);
116 SHOW CREATE TABLE t1;
117 Table Create Table
118 t1 CREATE TABLE `t1` (
119 `c1` int(11) NOT NULL AUTO_INCREMENT,
120 PRIMARY KEY (`c1`)
121 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
122 SELECT * FROM t1 ORDER BY c1;
123 c1
124 1
125 INSERT INTO t1 VALUES (100);
126 INSERT INTO t1 VALUES (NULL);
127 DELETE FROM t1 WHERE c1 >= 100;
128 OPTIMIZE TABLE t1;
129 Table Op Msg_type Msg_text
130 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
131 test.t1 optimize status OK
132 SHOW CREATE TABLE t1;
133 Table Create Table
134 t1 CREATE TABLE `t1` (
135 `c1` int(11) NOT NULL AUTO_INCREMENT,
136 PRIMARY KEY (`c1`)
137 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
138 DROP TABLE t1;
139 CREATE TABLE t1
140 (a INT NULL AUTO_INCREMENT,
141 UNIQUE KEY (a))
142 ENGINE='InnoDB';
143 SET LAST_INSERT_ID = 999;
144 SET INSERT_ID = 0;
145 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
146 SELECT LAST_INSERT_ID();
147 LAST_INSERT_ID()
148 999
149 SELECT * FROM t1;
150 a
151 1
152 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
153 SELECT LAST_INSERT_ID();
154 LAST_INSERT_ID()
155 999
156 SELECT * FROM t1;
157 a
158 0
159 UPDATE t1 SET a = 1 WHERE a IS NULL;
160 SELECT LAST_INSERT_ID();
161 LAST_INSERT_ID()
162 999
163 SELECT * FROM t1;
164 a
165 0
166 UPDATE t1 SET a = NULL WHERE a = 1;
167 SELECT LAST_INSERT_ID();
168 LAST_INSERT_ID()
169 999
170 SELECT * FROM t1;
171 a
172 0
173 DROP TABLE t1;
174 SET INSERT_ID = 1;
175 # Simple test with NULL
176 CREATE TABLE t1 (
177 c1 INT NOT NULL AUTO_INCREMENT,
178 PRIMARY KEY (c1))
179 ENGINE='InnoDB'
180 PARTITION BY HASH(c1)
181 PARTITIONS 2;
182 INSERT INTO t1 VALUES (NULL);
183 SHOW CREATE TABLE t1;
184 Table Create Table
185 t1 CREATE TABLE `t1` (
186 `c1` int(11) NOT NULL AUTO_INCREMENT,
187 PRIMARY KEY (`c1`)
188 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
189 PARTITION BY HASH (`c1`)
190 PARTITIONS 2
191 SELECT * FROM t1;
192 c1
193 1
194 DROP TABLE t1;
195 # Test with sql_mode and first insert as 0
196 CREATE TABLE t1 (
197 c1 INT,
198 c2 INT NOT NULL AUTO_INCREMENT,
199 PRIMARY KEY (c2))
200 ENGINE='InnoDB'
201 PARTITION BY HASH(c2)
202 PARTITIONS 2;
203 INSERT INTO t1 VALUES (1, NULL);
204 INSERT INTO t1 VALUES (1, 1), (99, 99);
205 INSERT INTO t1 VALUES (1, NULL);
206 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
207 INSERT INTO t1 VALUES (1, 0);
208 SELECT * FROM t1 ORDER BY c1, c2;
209 c1 c2
210 1 0
211 1 1
212 1 2
213 DROP TABLE t1;
214 CREATE TABLE t1 (
215 c1 INT,
216 c2 INT NOT NULL AUTO_INCREMENT,
217 PRIMARY KEY (c2))
218 ENGINE='InnoDB'
219 PARTITION BY HASH(c2)
220 PARTITIONS 2;
221 INSERT INTO t1 VALUES (1, 0);
222 INSERT INTO t1 VALUES (1, 1), (1, NULL);
223 INSERT INTO t1 VALUES (2, NULL), (4, 7);
224 INSERT INTO t1 VALUES (1, NULL);
225 SELECT * FROM t1 ORDER BY c1, c2;
226 c1 c2
227 1 0
228 1 1
229 1 2
230 1 8
231 2 3
232 4 7
233 SET @@session.sql_mode = '';
234 DROP TABLE t1;
235 # Simple test with NULL, 0 and explicit values both incr. and desc.
236 CREATE TABLE t1 (
237 c1 INT NOT NULL AUTO_INCREMENT,
238 PRIMARY KEY (c1))
239 ENGINE='InnoDB'
240 PARTITION BY HASH(c1)
241 PARTITIONS 2;
242 INSERT INTO t1 VALUES (2), (4), (NULL);
243 INSERT INTO t1 VALUES (0);
244 INSERT INTO t1 VALUES (5), (16);
245 INSERT INTO t1 VALUES (17), (19), (NULL);
246 INSERT INTO t1 VALUES (NULL), (10), (NULL);
247 INSERT INTO t1 VALUES (NULL), (9);
248 INSERT INTO t1 VALUES (59), (55);
249 INSERT INTO t1 VALUES (NULL), (90);
250 INSERT INTO t1 VALUES (NULL);
251 UPDATE t1 SET c1 = 150 WHERE c1 = 17;
252 UPDATE t1 SET c1 = 151 WHERE c1 = 19;
253 FLUSH TABLES;
254 UPDATE t1 SET c1 = 140 WHERE c1 = 150;
255 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
256 AND TABLE_NAME='t1';
257 AUTO_INCREMENT
258 152
259 UPDATE t1 SET c1 = NULL WHERE c1 = 4;
260 Warnings:
261 Warning 1048 Column 'c1' cannot be null
262 INSERT INTO t1 VALUES (NULL);
263 INSERT INTO t1 VALUES (NULL);
264 SELECT * FROM t1 ORDER BY c1;
265 c1
266 0
267 2
268 5
269 6
270 9
271 10
272 20
273 21
274 22
275 23
276 55
277 59
278 60
279 90
280 91
281 140
282 151
283 152
284 153
285 DROP TABLE t1;
286 # Test with auto_increment_increment and auto_increment_offset.
287 CREATE TABLE t1 (
288 c1 INT NOT NULL AUTO_INCREMENT,
289 PRIMARY KEY (c1))
290 ENGINE='InnoDB'
291 PARTITION BY HASH(c1)
292 PARTITIONS 2;
293 SET @@session.auto_increment_increment = 10;
294 SET @@session.auto_increment_offset = 5;
295 INSERT INTO t1 VALUES (1);
296 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
297 SET @@session.auto_increment_increment = 5;
298 SET @@session.auto_increment_offset = 3;
299 INSERT INTO t1 VALUES (NULL);
300 INSERT INTO t1 VALUES (33 + 1);
301 INSERT INTO t1 VALUES (NULL);
302 INSERT INTO t1 VALUES (38 + 2);
303 INSERT INTO t1 VALUES (NULL);
304 INSERT INTO t1 VALUES (43 + 3);
305 INSERT INTO t1 VALUES (NULL);
306 INSERT INTO t1 VALUES (48 + 4);
307 INSERT INTO t1 VALUES (NULL);
308 INSERT INTO t1 VALUES (53 + 5);
309 INSERT INTO t1 VALUES (NULL);
310 INSERT INTO t1 VALUES (63 + 6);
311 INSERT INTO t1 VALUES (NULL);
312 SET @@session.auto_increment_increment = 1;
313 SET @@session.auto_increment_offset = 1;
314 SELECT * FROM t1 ORDER BY c1;
315 c1
316 1
317 5
318 15
319 25
320 33
321 34
322 38
323 40
324 43
325 46
326 48
327 52
328 53
329 58
330 63
331 69
332 73
333 DROP TABLE t1;
334 # Test reported auto_increment value
335 CREATE TABLE t1 (
336 c1 INT NOT NULL AUTO_INCREMENT,
337 PRIMARY KEY (c1))
338 ENGINE='InnoDB'
339 PARTITION BY HASH (c1)
340 PARTITIONS 2;
341 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
342 AND TABLE_NAME='t1';
343 AUTO_INCREMENT
344 1
345 INSERT INTO t1 VALUES (2);
346 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
347 AND TABLE_NAME='t1';
348 AUTO_INCREMENT
349 3
350 INSERT INTO t1 VALUES (4);
351 INSERT INTO t1 VALUES (NULL);
352 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
353 AND TABLE_NAME='t1';
354 AUTO_INCREMENT
355 6
356 INSERT INTO t1 VALUES (NULL);
357 INSERT INTO t1 VALUES (17);
358 INSERT INTO t1 VALUES (19);
359 INSERT INTO t1 VALUES (NULL);
360 INSERT INTO t1 VALUES (NULL);
361 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
362 AND TABLE_NAME='t1';
363 AUTO_INCREMENT
364 22
365 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
366 AND TABLE_NAME='t1';
367 AUTO_INCREMENT
368 22
369 INSERT INTO t1 VALUES (10);
370 SELECT * FROM t1 ORDER BY c1;
371 c1
372 2
373 4
374 5
375 6
376 10
377 17
378 19
379 20
380 21
381 INSERT INTO t1 VALUES (NULL);
382 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
383 AND TABLE_NAME='t1';
384 AUTO_INCREMENT
385 23
386 INSERT INTO t1 VALUES (NULL);
387 INSERT INTO t1 VALUES (15);
388 INSERT INTO t1 VALUES (NULL);
389 SELECT * FROM t1 ORDER BY c1;
390 c1
391 2
392 4
393 5
394 6
395 10
396 15
397 17
398 19
399 20
400 21
401 22
402 23
403 24
404 INSERT INTO t1 VALUES (NULL);
405 DELETE FROM t1;
406 INSERT INTO t1 VALUES (NULL);
407 SHOW CREATE TABLE t1;
408 Table Create Table
409 t1 CREATE TABLE `t1` (
410 `c1` int(11) NOT NULL AUTO_INCREMENT,
411 PRIMARY KEY (`c1`)
412 ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
413 PARTITION BY HASH (`c1`)
414 PARTITIONS 2
415 SELECT * FROM t1 ORDER BY c1;
416 c1
417 26
418 TRUNCATE TABLE t1;
419 INSERT INTO t1 VALUES (NULL);
420 SHOW CREATE TABLE t1;
421 Table Create Table
422 t1 CREATE TABLE `t1` (
423 `c1` int(11) NOT NULL AUTO_INCREMENT,
424 PRIMARY KEY (`c1`)
425 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
426 PARTITION BY HASH (`c1`)
427 PARTITIONS 2
428 SELECT * FROM t1 ORDER BY c1;
429 c1
430 1
431 INSERT INTO t1 VALUES (100);
432 INSERT INTO t1 VALUES (NULL);
433 DELETE FROM t1 WHERE c1 >= 100;
434 OPTIMIZE TABLE t1;
435 Table Op Msg_type Msg_text
436 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
437 test.t1 optimize status OK
438 SHOW CREATE TABLE t1;
439 Table Create Table
440 t1 CREATE TABLE `t1` (
441 `c1` int(11) NOT NULL AUTO_INCREMENT,
442 PRIMARY KEY (`c1`)
443 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
444 PARTITION BY HASH (`c1`)
445 PARTITIONS 2
446 DROP TABLE t1;
447 # Test with two threads
448 connection default;
449 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
450 ENGINE = 'InnoDB'
451 PARTITION BY HASH(c1)
452 PARTITIONS 2;
453 INSERT INTO t1 (c1) VALUES (2);
454 INSERT INTO t1 (c1) VALUES (4);
455 connect con1, localhost, root,,;
456 connection con1;
457 INSERT INTO t1 (c1) VALUES (NULL);
458 INSERT INTO t1 (c1) VALUES (10);
459 connection default;
460 INSERT INTO t1 (c1) VALUES (NULL);
461 INSERT INTO t1 (c1) VALUES (NULL);
462 INSERT INTO t1 (c1) VALUES (19);
463 INSERT INTO t1 (c1) VALUES (21);
464 connection con1;
465 INSERT INTO t1 (c1) VALUES (NULL);
466 connection default;
467 INSERT INTO t1 (c1) VALUES (16);
468 connection con1;
469 INSERT INTO t1 (c1) VALUES (NULL);
470 disconnect con1;
471 connection default;
472 INSERT INTO t1 (c1) VALUES (NULL);
473 SELECT * FROM t1 ORDER BY c1;
474 c1
475 2
476 4
477 5
478 10
479 11
480 12
481 16
482 19
483 21
484 22
485 23
486 24
487 DROP TABLE t1;
488 # Test with two threads + start transaction NO PARTITIONING
489 connect con1, localhost, root,,;
490 connection default;
491 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
492 ENGINE = 'InnoDB';
493 INSERT INTO t1(c1) VALUES(100);
494 START TRANSACTION;
495 INSERT INTO t1 (c1) VALUES (2);
496 INSERT INTO t1 (c1) VALUES (4);
497 connection con1;
498 START TRANSACTION;
499 INSERT INTO t1 (c1) VALUES (NULL);
500 INSERT INTO t1 (c1) VALUES (10);
501 connection default;
502 INSERT INTO t1 (c1) VALUES (NULL);
503 INSERT INTO t1 (c1) VALUES (NULL);
504 INSERT INTO t1 (c1) VALUES (19);
505 INSERT INTO t1 (c1) VALUES (21);
506 connection con1;
507 INSERT INTO t1 (c1) VALUES (NULL);
508 connection default;
509 INSERT INTO t1 (c1) VALUES (16);
510 connection con1;
511 INSERT INTO t1 (c1) VALUES (NULL);
512 SELECT * FROM t1 ORDER BY c1;
513 c1
514 10
515 100
516 101
517 104
518 105
519 COMMIT;
520 SELECT * FROM t1 ORDER BY c1;
521 c1
522 10
523 100
524 101
525 104
526 105
527 disconnect con1;
528 connection default;
529 INSERT INTO t1 (c1) VALUES (NULL);
530 SELECT * FROM t1 ORDER BY c1;
531 c1
532 2
533 4
534 10
535 16
536 19
537 21
538 100
539 101
540 102
541 103
542 104
543 105
544 106
545 COMMIT;
546 SELECT * FROM t1 ORDER BY c1;
547 c1
548 2
549 4
550 10
551 16
552 19
553 21
554 100
555 101
556 102
557 103
558 104
559 105
560 106
561 DROP TABLE t1;
562 # Test with two threads + start transaction
563 connect con1, localhost, root,,;
564 connection default;
565 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
566 ENGINE = 'InnoDB'
567 PARTITION BY HASH(c1)
568 PARTITIONS 2;
569 INSERT INTO t1 (c1) VALUES (100);
570 INSERT INTO t1 (c1) VALUES (101);
571 START TRANSACTION;
572 INSERT INTO t1 (c1) VALUES (2);
573 INSERT INTO t1 (c1) VALUES (4);
574 connection con1;
575 START TRANSACTION;
576 INSERT INTO t1 (c1) VALUES (NULL), (10);
577 connection default;
578 INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
579 INSERT INTO t1 (c1) VALUES (21);
580 connection con1;
581 INSERT INTO t1 (c1) VALUES (NULL);
582 connection default;
583 INSERT INTO t1 (c1) VALUES (16);
584 connection con1;
585 INSERT INTO t1 (c1) VALUES (NULL);
586 SELECT * FROM t1 ORDER BY c1;
587 c1
588 10
589 100
590 101
591 102
592 105
593 106
594 COMMIT;
595 SELECT * FROM t1 ORDER BY c1;
596 c1
597 10
598 100
599 101
600 102
601 105
602 106
603 disconnect con1;
604 connection default;
605 INSERT INTO t1 (c1) VALUES (NULL);
606 SELECT * FROM t1 ORDER BY c1;
607 c1
608 2
609 4
610 10
611 16
612 19
613 21
614 100
615 101
616 102
617 103
618 104
619 105
620 106
621 107
622 COMMIT;
623 SELECT * FROM t1 ORDER BY c1;
624 c1
625 2
626 4
627 10
628 16
629 19
630 21
631 100
632 101
633 102
634 103
635 104
636 105
637 106
638 107
639 DROP TABLE t1;
640 # Test with another column after
641 CREATE TABLE t1 (
642 c1 INT NOT NULL AUTO_INCREMENT,
643 c2 INT,
644 PRIMARY KEY (c1,c2))
645 ENGINE = 'InnoDB'
646 PARTITION BY HASH(c2)
647 PARTITIONS 2;
648 INSERT INTO t1 VALUES (1, 0);
649 INSERT INTO t1 VALUES (1, 1);
650 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
651 INSERT INTO t1 VALUES (NULL, 3);
652 INSERT INTO t1 VALUES (2, 0), (NULL, 2);
653 INSERT INTO t1 VALUES (2, 2);
654 INSERT INTO t1 VALUES (2, 22);
655 INSERT INTO t1 VALUES (NULL, 2);
656 SELECT * FROM t1 ORDER BY c1,c2;
657 c1 c2
658 1 0
659 1 1
660 2 0
661 2 1
662 2 2
663 2 22
664 3 2
665 4 3
666 5 3
667 6 2
668 7 2
669 DROP TABLE t1;
670 # Test with another column before
671 CREATE TABLE t1 (
672 c1 INT,
673 c2 INT NOT NULL AUTO_INCREMENT,
674 PRIMARY KEY (c2))
675 ENGINE = 'InnoDB'
676 PARTITION BY HASH(c2)
677 PARTITIONS 2;
678 INSERT INTO t1 VALUES (1, 0);
679 INSERT INTO t1 VALUES (1, 1);
680 INSERT INTO t1 VALUES (1, NULL);
681 INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
682 INSERT INTO t1 VALUES (2, NULL);
683 INSERT INTO t1 VALUES (2, 2);
684 INSERT INTO t1 VALUES (2, 22);
685 INSERT INTO t1 VALUES (2, NULL);
686 SELECT * FROM t1 ORDER BY c1,c2;
687 c1 c2
688 1 1
689 1 2
690 2 3
691 2 13
692 2 14
693 2 22
694 2 23
695 3 11
696 3 12
697 DROP TABLE t1;
698 # Test with auto_increment on secondary column in multi-column-index
699 CREATE TABLE t1 (
700 c1 INT,
701 c2 INT NOT NULL AUTO_INCREMENT,
702 PRIMARY KEY (c1,c2))
703 ENGINE = 'InnoDB'
704 PARTITION BY HASH(c2)
705 PARTITIONS 2;
706 ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
707 # Test AUTO_INCREMENT in CREATE
708 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
709 ENGINE = 'InnoDB'
710 AUTO_INCREMENT = 15
711 PARTITION BY HASH(c1)
712 PARTITIONS 2;
713 SHOW CREATE TABLE t1;
714 Table Create Table
715 t1 CREATE TABLE `t1` (
716 `c1` int(11) NOT NULL AUTO_INCREMENT,
717 PRIMARY KEY (`c1`)
718 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
719 PARTITION BY HASH (`c1`)
720 PARTITIONS 2
721 INSERT INTO t1 (c1) VALUES (4);
722 SHOW CREATE TABLE t1;
723 Table Create Table
724 t1 CREATE TABLE `t1` (
725 `c1` int(11) NOT NULL AUTO_INCREMENT,
726 PRIMARY KEY (`c1`)
727 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
728 PARTITION BY HASH (`c1`)
729 PARTITIONS 2
730 INSERT INTO t1 (c1) VALUES (0);
731 SHOW CREATE TABLE t1;
732 Table Create Table
733 t1 CREATE TABLE `t1` (
734 `c1` int(11) NOT NULL AUTO_INCREMENT,
735 PRIMARY KEY (`c1`)
736 ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
737 PARTITION BY HASH (`c1`)
738 PARTITIONS 2
739 INSERT INTO t1 (c1) VALUES (NULL);
740 SHOW CREATE TABLE t1;
741 Table Create Table
742 t1 CREATE TABLE `t1` (
743 `c1` int(11) NOT NULL AUTO_INCREMENT,
744 PRIMARY KEY (`c1`)
745 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
746 PARTITION BY HASH (`c1`)
747 PARTITIONS 2
748 SELECT * FROM t1 ORDER BY c1;
749 c1
750 4
751 15
752 16
753 # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
754 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
755 INSERT INTO t1 (c1) VALUES (300);
756 SHOW CREATE TABLE t1;
757 Table Create Table
758 t1 CREATE TABLE `t1` (
759 `c1` int(11) NOT NULL AUTO_INCREMENT,
760 PRIMARY KEY (`c1`)
761 ) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
762 PARTITION BY HASH (`c1`)
763 PARTITIONS 2
764 INSERT INTO t1 (c1) VALUES (0);
765 SHOW CREATE TABLE t1;
766 Table Create Table
767 t1 CREATE TABLE `t1` (
768 `c1` int(11) NOT NULL AUTO_INCREMENT,
769 PRIMARY KEY (`c1`)
770 ) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
771 PARTITION BY HASH (`c1`)
772 PARTITIONS 2
773 INSERT INTO t1 (c1) VALUES (NULL);
774 SHOW CREATE TABLE t1;
775 Table Create Table
776 t1 CREATE TABLE `t1` (
777 `c1` int(11) NOT NULL AUTO_INCREMENT,
778 PRIMARY KEY (`c1`)
779 ) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
780 PARTITION BY HASH (`c1`)
781 PARTITIONS 2
782 SELECT * FROM t1 ORDER BY c1;
783 c1
784 0
785 4
786 15
787 16
788 300
789 301
790 SET @@session.sql_mode = '';
791 DROP TABLE t1;
792 # Test SET INSERT_ID
793 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
794 ENGINE = 'InnoDB'
795 PARTITION BY HASH(c1)
796 PARTITIONS 2;
797 SHOW CREATE TABLE t1;
798 Table Create Table
799 t1 CREATE TABLE `t1` (
800 `c1` int(11) NOT NULL AUTO_INCREMENT,
801 PRIMARY KEY (`c1`)
802 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
803 PARTITION BY HASH (`c1`)
804 PARTITIONS 2
805 INSERT INTO t1 (c1) VALUES (NULL);
806 SHOW CREATE TABLE t1;
807 Table Create Table
808 t1 CREATE TABLE `t1` (
809 `c1` int(11) NOT NULL AUTO_INCREMENT,
810 PRIMARY KEY (`c1`)
811 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
812 PARTITION BY HASH (`c1`)
813 PARTITIONS 2
814 SELECT * FROM t1;
815 c1
816 1
817 SET INSERT_ID = 23;
818 SHOW CREATE TABLE t1;
819 Table Create Table
820 t1 CREATE TABLE `t1` (
821 `c1` int(11) NOT NULL AUTO_INCREMENT,
822 PRIMARY KEY (`c1`)
823 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
824 PARTITION BY HASH (`c1`)
825 PARTITIONS 2
826 INSERT INTO t1 (c1) VALUES (NULL);
827 SHOW CREATE TABLE t1;
828 Table Create Table
829 t1 CREATE TABLE `t1` (
830 `c1` int(11) NOT NULL AUTO_INCREMENT,
831 PRIMARY KEY (`c1`)
832 ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
833 PARTITION BY HASH (`c1`)
834 PARTITIONS 2
835 SET INSERT_ID = 22;
836 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
837 INSERT INTO t1 VALUES (NULL);
838 SELECT * FROM t1 ORDER BY c1;
839 c1
840 1
841 23
842 24
843 DROP TABLE t1;
844 # Testing with FLUSH TABLE
845 CREATE TABLE t1 (
846 c1 INT NOT NULL AUTO_INCREMENT,
847 PRIMARY KEY (c1))
848 ENGINE='InnoDB'
849 PARTITION BY HASH(c1)
850 PARTITIONS 2;
851 SHOW CREATE TABLE t1;
852 Table Create Table
853 t1 CREATE TABLE `t1` (
854 `c1` int(11) NOT NULL AUTO_INCREMENT,
855 PRIMARY KEY (`c1`)
856 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
857 PARTITION BY HASH (`c1`)
858 PARTITIONS 2
859 FLUSH TABLE;
860 SHOW CREATE TABLE t1;
861 Table Create Table
862 t1 CREATE TABLE `t1` (
863 `c1` int(11) NOT NULL AUTO_INCREMENT,
864 PRIMARY KEY (`c1`)
865 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
866 PARTITION BY HASH (`c1`)
867 PARTITIONS 2
868 INSERT INTO t1 VALUES (4);
869 FLUSH TABLE;
870 SHOW CREATE TABLE t1;
871 Table Create Table
872 t1 CREATE TABLE `t1` (
873 `c1` int(11) NOT NULL AUTO_INCREMENT,
874 PRIMARY KEY (`c1`)
875 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
876 PARTITION BY HASH (`c1`)
877 PARTITIONS 2
878 INSERT INTO t1 VALUES (NULL);
879 FLUSH TABLE;
880 SHOW CREATE TABLE t1;
881 Table Create Table
882 t1 CREATE TABLE `t1` (
883 `c1` int(11) NOT NULL AUTO_INCREMENT,
884 PRIMARY KEY (`c1`)
885 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
886 PARTITION BY HASH (`c1`)
887 PARTITIONS 2
888 SELECT * FROM t1 ORDER BY c1;
889 c1
890 4
891 5
892 DROP TABLE t1;
893 #############################################################################
894 # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
895 # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
896 ##############################################################################
897 # Inserting negative autoincrement values into a partition table (partitions >= 4)
898 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
899 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
900 INSERT INTO t(c2) VALUES (10);
901 INSERT INTO t(c2) VALUES (20);
902 INSERT INTO t VALUES (-1,-10);
903 INSERT INTO t(c2) VALUES (30);
904 INSERT INTO t(c2) VALUES (40);
905 SELECT * FROM t ORDER BY c1 ASC;
906 c1 c2
907 -1 -10
908 1 10
909 2 20
910 3 30
911 4 40
912 DROP TABLE t;
913 # Reading from a partition table (partitions >= 2 ) after inserting a negative
914 # value into the auto increment column
915 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
916 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 2;
917 INSERT INTO t VALUES (-2,-20);
918 INSERT INTO t(c2) VALUES (30);
919 SELECT * FROM t ORDER BY c1 ASC;
920 c1 c2
921 -2 -20
922 1 30
923 DROP TABLE t;
924 # Inserting negative auto increment value into a partition table (partitions >= 2)
925 # auto increment value > 2.
926 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
927 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 2;
928 INSERT INTO t VALUES (-4,-20);
929 INSERT INTO t(c2) VALUES (30);
930 INSERT INTO t(c2) VALUES (40);
931 SELECT * FROM t ORDER BY c1 ASC;
932 c1 c2
933 -4 -20
934 1 30
935 2 40
936 DROP TABLE t;
937 # Inserting -1 into autoincrement column of a partition table (partition >= 4)
938 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
939 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
940 INSERT INTO t(c2) VALUES (10);
941 INSERT INTO t(c2) VALUES (20);
942 INSERT INTO t VALUES (-1,-10);
943 SELECT * FROM t ORDER BY c1 ASC;
944 c1 c2
945 -1 -10
946 1 10
947 2 20
948 INSERT INTO t(c2) VALUES (30);
949 SELECT * FROM t ORDER BY c1 ASC;
950 c1 c2
951 -1 -10
952 1 10
953 2 20
954 3 30
955 DROP TABLE t;
956 # Deleting from an auto increment table after inserting negative values
957 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
958 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
959 INSERT INTO t(c2) VALUES (10);
960 INSERT INTO t(c2) VALUES (20);
961 INSERT INTO t VALUES (-1,-10);
962 INSERT INTO t(c2) VALUES (30);
963 INSERT INTO t VALUES (-3,-20);
964 INSERT INTO t(c2) VALUES (40);
965 SELECT * FROM t ORDER BY c1 ASC;
966 c1 c2
967 -3 -20
968 -1 -10
969 1 10
970 2 20
971 3 30
972 4 40
973 DELETE FROM t WHERE c1 > 1;
974 SELECT * FROM t ORDER BY c1 ASC;
975 c1 c2
976 -3 -20
977 -1 -10
978 1 10
979 DROP TABLE t;
980 # Inserting a positive value that exceeds maximum allowed value for an
981 # Auto Increment column (positive maximum)
982 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
983 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
984 INSERT INTO t(c2) VALUES (10);
985 INSERT INTO t(c2) VALUES (20);
986 INSERT INTO t VALUES (126,30);
987 INSERT INTO t VALUES (127,40);
988 INSERT INTO t VALUES (128,50);
989 ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
990 INSERT INTO t VALUES (129,60);
991 ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
992 SELECT * FROM t ORDER BY c1 ASC;
993 c1 c2
994 1 10
995 2 20
996 126 30
997 127 40
998 DROP TABLE t;
999 # Inserting a negative value that goes below minimum allowed value for an
1000 # Auto Increment column (negative minimum)
1001 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1002 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
1003 INSERT INTO t(c2) VALUES (10);
1004 INSERT INTO t(c2) VALUES (20);
1005 INSERT INTO t VALUES (-127,30);
1006 INSERT INTO t VALUES (-128,40);
1007 INSERT INTO t VALUES (-129,50);
1008 ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
1009 INSERT INTO t VALUES (-130,60);
1010 ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
1011 SELECT * FROM t ORDER BY c1 ASC;
1012 c1 c2
1013 -128 40
1014 -127 30
1015 1 10
1016 2 20
1017 DROP TABLE t;
1018 # Updating the partition table with a negative Auto Increment value
1019 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1020 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
1021 INSERT INTO t(c2) VALUES (10);
1022 INSERT INTO t(c2) VALUES (20);
1023 INSERT INTO t VALUES (-1,-10);
1024 INSERT INTO t(c2) VALUES (30);
1025 SELECT * FROM t ORDER BY c1 ASC;
1026 c1 c2
1027 -1 -10
1028 1 10
1029 2 20
1030 3 30
1031 UPDATE t SET c1 = -6 WHERE c1 = 2;
1032 SELECT * FROM t ORDER BY c1 ASC;
1033 c1 c2
1034 -6 20
1035 -1 -10
1036 1 10
1037 3 30
1038 INSERT INTO t(c2) VALUES (40);
1039 INSERT INTO t(c2) VALUES (50);
1040 UPDATE t SET c1 = -6 WHERE c1 = 2;
1041 SELECT * FROM t ORDER BY c1 ASC;
1042 c1 c2
1043 -6 20
1044 -1 -10
1045 1 10
1046 3 30
1047 4 40
1048 5 50
1049 DROP TABLE t;
1050 # Updating the partition table with a value that crosses the upper limits
1051 # on both the positive and the negative side.
1052 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1053 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
1054 INSERT INTO t(c2) VALUES (10);
1055 INSERT INTO t(c2) VALUES (20);
1056 INSERT INTO t VALUES (126,30);
1057 INSERT INTO t VALUES (127,40);
1058 SELECT * FROM t ORDER BY c1 ASC;
1059 c1 c2
1060 1 10
1061 2 20
1062 126 30
1063 127 40
1064 UPDATE t SET c1 = 130 where c1 = 127;
1065 Warnings:
1066 Warning 1264 Out of range value for column 'c1' at row 1
1067 SELECT * FROM t ORDER BY c1 ASC;
1068 c1 c2
1069 1 10
1070 2 20
1071 126 30
1072 127 40
1073 UPDATE t SET c1 = -140 where c1 = 126;
1074 Warnings:
1075 Warning 1264 Out of range value for column 'c1' at row 1
1076 SELECT * FROM t ORDER BY c1 ASC;
1077 c1 c2
1078 -128 30
1079 1 10
1080 2 20
1081 127 40
1082 DROP TABLE t;
1083 CREATE TABLE t1
1084 (a INT NULL AUTO_INCREMENT,
1085 UNIQUE KEY (a))
1086 ENGINE='InnoDB'
1087 PARTITION BY KEY(a) PARTITIONS 2;
1088 SET LAST_INSERT_ID = 999;
1089 SET INSERT_ID = 0;
1090 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
1091 SELECT LAST_INSERT_ID();
1092 LAST_INSERT_ID()
1093 999
1094 SELECT * FROM t1;
1095 a
1096 1
1097 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
1098 SELECT LAST_INSERT_ID();
1099 LAST_INSERT_ID()
1100 999
1101 SELECT * FROM t1;
1102 a
1103 0
1104 UPDATE t1 SET a = 1 WHERE a IS NULL;
1105 SELECT LAST_INSERT_ID();
1106 LAST_INSERT_ID()
1107 999
1108 SELECT * FROM t1;
1109 a
1110 0
1111 UPDATE t1 SET a = NULL WHERE a = 1;
1112 SELECT LAST_INSERT_ID();
1113 LAST_INSERT_ID()
1114 999
1115 SELECT * FROM t1;
1116 a
1117 0
1118 DROP TABLE t1;
1119 ##############################################################################
1120 #
1121 # MDEV-19622 Assertion failures in
1122 # ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table
1123 #
1124 CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE='InnoDB' PARTITION BY HASH(a);
1125 INSERT INTO t1 VALUES (1,1),(2,2);
1126 UPDATE t1 SET pk = 0;
1127 DROP TABLE t1;
1128 #
1129 # MDEV-29636 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
1130 # failed in ha_partition::set_auto_increment_if_higher upon REPLACE
1131 # with partition pruning
1132 #
1133 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='InnoDB'
1134 PARTITION BY RANGE (a) (
1135 PARTITION p0 VALUES LESS THAN (10),
1136 PARTITION pn VALUES LESS THAN MAXVALUE
1137 );
1138 REPLACE INTO t1 PARTITION (p0) SELECT 1;
1139 DROP TABLE t1;
1140 #
1141 # MDEV-21027 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
1142 # ha_partition::set_auto_increment_if_higher
1143 #
1144 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='InnoDB' PARTITION BY HASH (a) PARTITIONS 3;
1145 REPLACE INTO t1 PARTITION (p0) VALUES (3);
1146 DROP TABLE t1;
1147 #
1148 # MDEV-21310 AUTO_INCREMENT column throws range error on INSERT in partitioned table |
1149 # Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' failed.
1150 #
1151 CREATE TABLE t1 (c INT AUTO_INCREMENT KEY) ENGINE='InnoDB' PARTITION BY LIST (c) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
1152 ALTER TABLE t1 TRUNCATE PARTITION p1;
1153 INSERT INTO t1 PARTITION (p1) (c) SELECT 1;
1154 DROP TABLE t1;