Debian Codegraph @

mysql-test/suite/parts/r/partition_auto_increment_maria.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='Aria';
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=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
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 21
61 22
62 23
63 29
64 30
65 31
66 40
67 51
68 52
69 53
70 DROP TABLE t1;
71 CREATE TABLE t1 (
72 c1 INT NOT NULL AUTO_INCREMENT,
73 PRIMARY KEY (c1))
74 ENGINE='Aria';
75 SHOW CREATE TABLE t1;
76 Table Create Table
77 t1 CREATE TABLE `t1` (
78 `c1` int(11) NOT NULL AUTO_INCREMENT,
79 PRIMARY KEY (`c1`)
80 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
81 FLUSH TABLE;
82 SHOW CREATE TABLE t1;
83 Table Create Table
84 t1 CREATE TABLE `t1` (
85 `c1` int(11) NOT NULL AUTO_INCREMENT,
86 PRIMARY KEY (`c1`)
87 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
88 INSERT INTO t1 VALUES (4);
89 FLUSH TABLE;
90 SHOW CREATE TABLE t1;
91 Table Create Table
92 t1 CREATE TABLE `t1` (
93 `c1` int(11) NOT NULL AUTO_INCREMENT,
94 PRIMARY KEY (`c1`)
95 ) ENGINE=Aria AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
96 INSERT INTO t1 VALUES (NULL);
97 FLUSH TABLE;
98 SHOW CREATE TABLE t1;
99 Table Create Table
100 t1 CREATE TABLE `t1` (
101 `c1` int(11) NOT NULL AUTO_INCREMENT,
102 PRIMARY KEY (`c1`)
103 ) ENGINE=Aria AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
104 DELETE FROM t1;
105 INSERT INTO t1 VALUES (NULL);
106 SHOW CREATE TABLE t1;
107 Table Create Table
108 t1 CREATE TABLE `t1` (
109 `c1` int(11) NOT NULL AUTO_INCREMENT,
110 PRIMARY KEY (`c1`)
111 ) ENGINE=Aria AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
112 SELECT * FROM t1 ORDER BY c1;
113 c1
114 6
115 TRUNCATE TABLE t1;
116 INSERT INTO t1 VALUES (NULL);
117 SHOW CREATE TABLE t1;
118 Table Create Table
119 t1 CREATE TABLE `t1` (
120 `c1` int(11) NOT NULL AUTO_INCREMENT,
121 PRIMARY KEY (`c1`)
122 ) ENGINE=Aria AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
123 SELECT * FROM t1 ORDER BY c1;
124 c1
125 1
126 INSERT INTO t1 VALUES (100);
127 INSERT INTO t1 VALUES (NULL);
128 DELETE FROM t1 WHERE c1 >= 100;
129 OPTIMIZE TABLE t1;
130 Table Op Msg_type Msg_text
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=Aria AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
138 DROP TABLE t1;
139 CREATE TABLE t1
140 (a INT NULL AUTO_INCREMENT,
141 UNIQUE KEY (a))
142 ENGINE='Aria';
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='Aria'
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=Aria 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='Aria'
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='Aria'
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='Aria'
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='Aria'
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='Aria'
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=Aria 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=Aria AUTO_INCREMENT=28 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 27
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 status OK
437 SHOW CREATE TABLE t1;
438 Table Create Table
439 t1 CREATE TABLE `t1` (
440 `c1` int(11) NOT NULL AUTO_INCREMENT,
441 PRIMARY KEY (`c1`)
442 ) ENGINE=Aria AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
443 PARTITION BY HASH (`c1`)
444 PARTITIONS 2
445 DROP TABLE t1;
446 # Test with two threads
447 connection default;
448 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
449 ENGINE = 'Aria'
450 PARTITION BY HASH(c1)
451 PARTITIONS 2;
452 INSERT INTO t1 (c1) VALUES (2);
453 INSERT INTO t1 (c1) VALUES (4);
454 connect con1, localhost, root,,;
455 connection con1;
456 INSERT INTO t1 (c1) VALUES (NULL);
457 INSERT INTO t1 (c1) VALUES (10);
458 connection default;
459 INSERT INTO t1 (c1) VALUES (NULL);
460 INSERT INTO t1 (c1) VALUES (NULL);
461 INSERT INTO t1 (c1) VALUES (19);
462 INSERT INTO t1 (c1) VALUES (21);
463 connection con1;
464 INSERT INTO t1 (c1) VALUES (NULL);
465 connection default;
466 INSERT INTO t1 (c1) VALUES (16);
467 connection con1;
468 INSERT INTO t1 (c1) VALUES (NULL);
469 disconnect con1;
470 connection default;
471 INSERT INTO t1 (c1) VALUES (NULL);
472 SELECT * FROM t1 ORDER BY c1;
473 c1
474 2
475 4
476 5
477 10
478 11
479 12
480 16
481 19
482 21
483 22
484 23
485 24
486 DROP TABLE t1;
487 # Test with two threads + start transaction NO PARTITIONING
488 connect con1, localhost, root,,;
489 connection default;
490 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
491 ENGINE = 'Aria';
492 START TRANSACTION;
493 INSERT INTO t1 (c1) VALUES (2);
494 INSERT INTO t1 (c1) VALUES (4);
495 connection con1;
496 START TRANSACTION;
497 INSERT INTO t1 (c1) VALUES (NULL);
498 INSERT INTO t1 (c1) VALUES (10);
499 connection default;
500 INSERT INTO t1 (c1) VALUES (NULL);
501 INSERT INTO t1 (c1) VALUES (NULL);
502 INSERT INTO t1 (c1) VALUES (19);
503 INSERT INTO t1 (c1) VALUES (21);
504 connection con1;
505 INSERT INTO t1 (c1) VALUES (NULL);
506 connection default;
507 INSERT INTO t1 (c1) VALUES (16);
508 connection con1;
509 INSERT INTO t1 (c1) VALUES (NULL);
510 SELECT * FROM t1 ORDER BY c1;
511 c1
512 2
513 4
514 5
515 10
516 11
517 12
518 16
519 19
520 21
521 22
522 23
523 COMMIT;
524 SELECT * FROM t1 ORDER BY c1;
525 c1
526 2
527 4
528 5
529 10
530 11
531 12
532 16
533 19
534 21
535 22
536 23
537 disconnect con1;
538 connection default;
539 INSERT INTO t1 (c1) VALUES (NULL);
540 SELECT * FROM t1 ORDER BY c1;
541 c1
542 2
543 4
544 5
545 10
546 11
547 12
548 16
549 19
550 21
551 22
552 23
553 24
554 COMMIT;
555 SELECT * FROM t1 ORDER BY c1;
556 c1
557 2
558 4
559 5
560 10
561 11
562 12
563 16
564 19
565 21
566 22
567 23
568 24
569 DROP TABLE t1;
570 # Test with two threads + start transaction
571 connect con1, localhost, root,,;
572 connection default;
573 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
574 ENGINE = 'Aria'
575 PARTITION BY HASH(c1)
576 PARTITIONS 2;
577 START TRANSACTION;
578 INSERT INTO t1 (c1) VALUES (2);
579 INSERT INTO t1 (c1) VALUES (4);
580 connection con1;
581 START TRANSACTION;
582 INSERT INTO t1 (c1) VALUES (NULL), (10);
583 connection default;
584 INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
585 INSERT INTO t1 (c1) VALUES (21);
586 connection con1;
587 INSERT INTO t1 (c1) VALUES (NULL);
588 connection default;
589 INSERT INTO t1 (c1) VALUES (16);
590 connection con1;
591 INSERT INTO t1 (c1) VALUES (NULL);
592 SELECT * FROM t1 ORDER BY c1;
593 c1
594 2
595 4
596 5
597 10
598 11
599 12
600 16
601 19
602 21
603 22
604 23
605 COMMIT;
606 SELECT * FROM t1 ORDER BY c1;
607 c1
608 2
609 4
610 5
611 10
612 11
613 12
614 16
615 19
616 21
617 22
618 23
619 disconnect con1;
620 connection default;
621 INSERT INTO t1 (c1) VALUES (NULL);
622 SELECT * FROM t1 ORDER BY c1;
623 c1
624 2
625 4
626 5
627 10
628 11
629 12
630 16
631 19
632 21
633 22
634 23
635 24
636 COMMIT;
637 SELECT * FROM t1 ORDER BY c1;
638 c1
639 2
640 4
641 5
642 10
643 11
644 12
645 16
646 19
647 21
648 22
649 23
650 24
651 DROP TABLE t1;
652 # Test with another column after
653 CREATE TABLE t1 (
654 c1 INT NOT NULL AUTO_INCREMENT,
655 c2 INT,
656 PRIMARY KEY (c1,c2))
657 ENGINE = 'Aria'
658 PARTITION BY HASH(c2)
659 PARTITIONS 2;
660 INSERT INTO t1 VALUES (1, 0);
661 INSERT INTO t1 VALUES (1, 1);
662 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
663 INSERT INTO t1 VALUES (NULL, 3);
664 INSERT INTO t1 VALUES (2, 0), (NULL, 2);
665 INSERT INTO t1 VALUES (2, 2);
666 INSERT INTO t1 VALUES (2, 22);
667 INSERT INTO t1 VALUES (NULL, 2);
668 SELECT * FROM t1 ORDER BY c1,c2;
669 c1 c2
670 1 0
671 1 1
672 2 0
673 2 1
674 2 2
675 2 22
676 3 2
677 4 3
678 5 3
679 6 2
680 7 2
681 DROP TABLE t1;
682 # Test with another column before
683 CREATE TABLE t1 (
684 c1 INT,
685 c2 INT NOT NULL AUTO_INCREMENT,
686 PRIMARY KEY (c2))
687 ENGINE = 'Aria'
688 PARTITION BY HASH(c2)
689 PARTITIONS 2;
690 INSERT INTO t1 VALUES (1, 0);
691 INSERT INTO t1 VALUES (1, 1);
692 INSERT INTO t1 VALUES (1, NULL);
693 INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
694 INSERT INTO t1 VALUES (2, NULL);
695 INSERT INTO t1 VALUES (2, 2);
696 INSERT INTO t1 VALUES (2, 22);
697 INSERT INTO t1 VALUES (2, NULL);
698 SELECT * FROM t1 ORDER BY c1,c2;
699 c1 c2
700 1 1
701 1 2
702 2 3
703 2 13
704 2 14
705 2 22
706 2 23
707 3 11
708 3 12
709 DROP TABLE t1;
710 # Test with auto_increment on secondary column in multi-column-index
711 CREATE TABLE t1 (
712 c1 INT,
713 c2 INT NOT NULL AUTO_INCREMENT,
714 PRIMARY KEY (c1,c2))
715 ENGINE = 'Aria'
716 PARTITION BY HASH(c2)
717 PARTITIONS 2;
718 INSERT INTO t1 VALUES (1, 0);
719 INSERT INTO t1 VALUES (1, 1);
720 INSERT INTO t1 VALUES (1, NULL);
721 INSERT INTO t1 VALUES (2, NULL);
722 INSERT INTO t1 VALUES (3, NULL);
723 INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
724 INSERT INTO t1 VALUES (2, 2);
725 INSERT INTO t1 VALUES (2, 22), (2, NULL);
726 SELECT * FROM t1 ORDER BY c1,c2;
727 c1 c2
728 1 1
729 1 2
730 2 1
731 2 2
732 2 3
733 2 22
734 2 23
735 3 1
736 3 2
737 DROP TABLE t1;
738 # Test AUTO_INCREMENT in CREATE
739 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
740 ENGINE = 'Aria'
741 AUTO_INCREMENT = 15
742 PARTITION BY HASH(c1)
743 PARTITIONS 2;
744 SHOW CREATE TABLE t1;
745 Table Create Table
746 t1 CREATE TABLE `t1` (
747 `c1` int(11) NOT NULL AUTO_INCREMENT,
748 PRIMARY KEY (`c1`)
749 ) ENGINE=Aria AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
750 PARTITION BY HASH (`c1`)
751 PARTITIONS 2
752 INSERT INTO t1 (c1) VALUES (4);
753 SHOW CREATE TABLE t1;
754 Table Create Table
755 t1 CREATE TABLE `t1` (
756 `c1` int(11) NOT NULL AUTO_INCREMENT,
757 PRIMARY KEY (`c1`)
758 ) ENGINE=Aria AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
759 PARTITION BY HASH (`c1`)
760 PARTITIONS 2
761 INSERT INTO t1 (c1) VALUES (0);
762 SHOW CREATE TABLE t1;
763 Table Create Table
764 t1 CREATE TABLE `t1` (
765 `c1` int(11) NOT NULL AUTO_INCREMENT,
766 PRIMARY KEY (`c1`)
767 ) ENGINE=Aria AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
768 PARTITION BY HASH (`c1`)
769 PARTITIONS 2
770 INSERT INTO t1 (c1) VALUES (NULL);
771 SHOW CREATE TABLE t1;
772 Table Create Table
773 t1 CREATE TABLE `t1` (
774 `c1` int(11) NOT NULL AUTO_INCREMENT,
775 PRIMARY KEY (`c1`)
776 ) ENGINE=Aria AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
777 PARTITION BY HASH (`c1`)
778 PARTITIONS 2
779 SELECT * FROM t1 ORDER BY c1;
780 c1
781 4
782 15
783 16
784 # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
785 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
786 INSERT INTO t1 (c1) VALUES (300);
787 SHOW CREATE TABLE t1;
788 Table Create Table
789 t1 CREATE TABLE `t1` (
790 `c1` int(11) NOT NULL AUTO_INCREMENT,
791 PRIMARY KEY (`c1`)
792 ) ENGINE=Aria AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
793 PARTITION BY HASH (`c1`)
794 PARTITIONS 2
795 INSERT INTO t1 (c1) VALUES (0);
796 SHOW CREATE TABLE t1;
797 Table Create Table
798 t1 CREATE TABLE `t1` (
799 `c1` int(11) NOT NULL AUTO_INCREMENT,
800 PRIMARY KEY (`c1`)
801 ) ENGINE=Aria AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
802 PARTITION BY HASH (`c1`)
803 PARTITIONS 2
804 INSERT INTO t1 (c1) VALUES (NULL);
805 SHOW CREATE TABLE t1;
806 Table Create Table
807 t1 CREATE TABLE `t1` (
808 `c1` int(11) NOT NULL AUTO_INCREMENT,
809 PRIMARY KEY (`c1`)
810 ) ENGINE=Aria AUTO_INCREMENT=302 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
811 PARTITION BY HASH (`c1`)
812 PARTITIONS 2
813 SELECT * FROM t1 ORDER BY c1;
814 c1
815 0
816 4
817 15
818 16
819 300
820 301
821 SET @@session.sql_mode = '';
822 DROP TABLE t1;
823 # Test SET INSERT_ID
824 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
825 ENGINE = 'Aria'
826 PARTITION BY HASH(c1)
827 PARTITIONS 2;
828 SHOW CREATE TABLE t1;
829 Table Create Table
830 t1 CREATE TABLE `t1` (
831 `c1` int(11) NOT NULL AUTO_INCREMENT,
832 PRIMARY KEY (`c1`)
833 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
834 PARTITION BY HASH (`c1`)
835 PARTITIONS 2
836 INSERT INTO t1 (c1) VALUES (NULL);
837 SHOW CREATE TABLE t1;
838 Table Create Table
839 t1 CREATE TABLE `t1` (
840 `c1` int(11) NOT NULL AUTO_INCREMENT,
841 PRIMARY KEY (`c1`)
842 ) ENGINE=Aria AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
843 PARTITION BY HASH (`c1`)
844 PARTITIONS 2
845 SELECT * FROM t1;
846 c1
847 1
848 SET INSERT_ID = 23;
849 SHOW CREATE TABLE t1;
850 Table Create Table
851 t1 CREATE TABLE `t1` (
852 `c1` int(11) NOT NULL AUTO_INCREMENT,
853 PRIMARY KEY (`c1`)
854 ) ENGINE=Aria AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
855 PARTITION BY HASH (`c1`)
856 PARTITIONS 2
857 INSERT INTO t1 (c1) VALUES (NULL);
858 SHOW CREATE TABLE t1;
859 Table Create Table
860 t1 CREATE TABLE `t1` (
861 `c1` int(11) NOT NULL AUTO_INCREMENT,
862 PRIMARY KEY (`c1`)
863 ) ENGINE=Aria AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
864 PARTITION BY HASH (`c1`)
865 PARTITIONS 2
866 SET INSERT_ID = 22;
867 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
868 INSERT INTO t1 VALUES (NULL);
869 SELECT * FROM t1 ORDER BY c1;
870 c1
871 1
872 22
873 23
874 24
875 DROP TABLE t1;
876 # Testing with FLUSH TABLE
877 CREATE TABLE t1 (
878 c1 INT NOT NULL AUTO_INCREMENT,
879 PRIMARY KEY (c1))
880 ENGINE='Aria'
881 PARTITION BY HASH(c1)
882 PARTITIONS 2;
883 SHOW CREATE TABLE t1;
884 Table Create Table
885 t1 CREATE TABLE `t1` (
886 `c1` int(11) NOT NULL AUTO_INCREMENT,
887 PRIMARY KEY (`c1`)
888 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
889 PARTITION BY HASH (`c1`)
890 PARTITIONS 2
891 FLUSH TABLE;
892 SHOW CREATE TABLE t1;
893 Table Create Table
894 t1 CREATE TABLE `t1` (
895 `c1` int(11) NOT NULL AUTO_INCREMENT,
896 PRIMARY KEY (`c1`)
897 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
898 PARTITION BY HASH (`c1`)
899 PARTITIONS 2
900 INSERT INTO t1 VALUES (4);
901 FLUSH TABLE;
902 SHOW CREATE TABLE t1;
903 Table Create Table
904 t1 CREATE TABLE `t1` (
905 `c1` int(11) NOT NULL AUTO_INCREMENT,
906 PRIMARY KEY (`c1`)
907 ) ENGINE=Aria AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
908 PARTITION BY HASH (`c1`)
909 PARTITIONS 2
910 INSERT INTO t1 VALUES (NULL);
911 FLUSH TABLE;
912 SHOW CREATE TABLE t1;
913 Table Create Table
914 t1 CREATE TABLE `t1` (
915 `c1` int(11) NOT NULL AUTO_INCREMENT,
916 PRIMARY KEY (`c1`)
917 ) ENGINE=Aria AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
918 PARTITION BY HASH (`c1`)
919 PARTITIONS 2
920 SELECT * FROM t1 ORDER BY c1;
921 c1
922 4
923 5
924 DROP TABLE t1;
925 #############################################################################
926 # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
927 # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
928 ##############################################################################
929 # Inserting negative autoincrement values into a partition table (partitions >= 4)
930 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
931 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 4;
932 INSERT INTO t(c2) VALUES (10);
933 INSERT INTO t(c2) VALUES (20);
934 INSERT INTO t VALUES (-1,-10);
935 INSERT INTO t(c2) VALUES (30);
936 INSERT INTO t(c2) VALUES (40);
937 SELECT * FROM t ORDER BY c1 ASC;
938 c1 c2
939 -1 -10
940 1 10
941 2 20
942 3 30
943 4 40
944 DROP TABLE t;
945 # Reading from a partition table (partitions >= 2 ) after inserting a negative
946 # value into the auto increment column
947 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
948 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 2;
949 INSERT INTO t VALUES (-2,-20);
950 INSERT INTO t(c2) VALUES (30);
951 SELECT * FROM t ORDER BY c1 ASC;
952 c1 c2
953 -2 -20
954 1 30
955 DROP TABLE t;
956 # Inserting negative auto increment value into a partition table (partitions >= 2)
957 # auto increment value > 2.
958 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
959 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 2;
960 INSERT INTO t VALUES (-4,-20);
961 INSERT INTO t(c2) VALUES (30);
962 INSERT INTO t(c2) VALUES (40);
963 SELECT * FROM t ORDER BY c1 ASC;
964 c1 c2
965 -4 -20
966 1 30
967 2 40
968 DROP TABLE t;
969 # Inserting -1 into autoincrement column of a partition table (partition >= 4)
970 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
971 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 4;
972 INSERT INTO t(c2) VALUES (10);
973 INSERT INTO t(c2) VALUES (20);
974 INSERT INTO t VALUES (-1,-10);
975 SELECT * FROM t ORDER BY c1 ASC;
976 c1 c2
977 -1 -10
978 1 10
979 2 20
980 INSERT INTO t(c2) VALUES (30);
981 SELECT * FROM t ORDER BY c1 ASC;
982 c1 c2
983 -1 -10
984 1 10
985 2 20
986 3 30
987 DROP TABLE t;
988 # Deleting from an auto increment table after inserting negative values
989 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
990 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 4;
991 INSERT INTO t(c2) VALUES (10);
992 INSERT INTO t(c2) VALUES (20);
993 INSERT INTO t VALUES (-1,-10);
994 INSERT INTO t(c2) VALUES (30);
995 INSERT INTO t VALUES (-3,-20);
996 INSERT INTO t(c2) VALUES (40);
997 SELECT * FROM t ORDER BY c1 ASC;
998 c1 c2
999 -3 -20
1000 -1 -10
1001 1 10
1002 2 20
1003 3 30
1004 4 40
1005 DELETE FROM t WHERE c1 > 1;
1006 SELECT * FROM t ORDER BY c1 ASC;
1007 c1 c2
1008 -3 -20
1009 -1 -10
1010 1 10
1011 DROP TABLE t;
1012 # Inserting a positive value that exceeds maximum allowed value for an
1013 # Auto Increment column (positive maximum)
1014 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1015 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 4;
1016 INSERT INTO t(c2) VALUES (10);
1017 INSERT INTO t(c2) VALUES (20);
1018 INSERT INTO t VALUES (126,30);
1019 INSERT INTO t VALUES (127,40);
1020 INSERT INTO t VALUES (128,50);
1021 ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
1022 INSERT INTO t VALUES (129,60);
1023 ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
1024 SELECT * FROM t ORDER BY c1 ASC;
1025 c1 c2
1026 1 10
1027 2 20
1028 126 30
1029 127 40
1030 DROP TABLE t;
1031 # Inserting a negative value that goes below minimum allowed value for an
1032 # Auto Increment column (negative minimum)
1033 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1034 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 4;
1035 INSERT INTO t(c2) VALUES (10);
1036 INSERT INTO t(c2) VALUES (20);
1037 INSERT INTO t VALUES (-127,30);
1038 INSERT INTO t VALUES (-128,40);
1039 INSERT INTO t VALUES (-129,50);
1040 ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
1041 INSERT INTO t VALUES (-130,60);
1042 ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
1043 SELECT * FROM t ORDER BY c1 ASC;
1044 c1 c2
1045 -128 40
1046 -127 30
1047 1 10
1048 2 20
1049 DROP TABLE t;
1050 # Updating the partition table with a negative Auto Increment value
1051 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1052 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 4;
1053 INSERT INTO t(c2) VALUES (10);
1054 INSERT INTO t(c2) VALUES (20);
1055 INSERT INTO t VALUES (-1,-10);
1056 INSERT INTO t(c2) VALUES (30);
1057 SELECT * FROM t ORDER BY c1 ASC;
1058 c1 c2
1059 -1 -10
1060 1 10
1061 2 20
1062 3 30
1063 UPDATE t SET c1 = -6 WHERE c1 = 2;
1064 SELECT * FROM t ORDER BY c1 ASC;
1065 c1 c2
1066 -6 20
1067 -1 -10
1068 1 10
1069 3 30
1070 INSERT INTO t(c2) VALUES (40);
1071 INSERT INTO t(c2) VALUES (50);
1072 UPDATE t SET c1 = -6 WHERE c1 = 2;
1073 SELECT * FROM t ORDER BY c1 ASC;
1074 c1 c2
1075 -6 20
1076 -1 -10
1077 1 10
1078 3 30
1079 4 40
1080 5 50
1081 DROP TABLE t;
1082 # Updating the partition table with a value that crosses the upper limits
1083 # on both the positive and the negative side.
1084 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1085 c2 INT) ENGINE='Aria' PARTITION BY HASH(c1) PARTITIONS 4;
1086 INSERT INTO t(c2) VALUES (10);
1087 INSERT INTO t(c2) VALUES (20);
1088 INSERT INTO t VALUES (126,30);
1089 INSERT INTO t VALUES (127,40);
1090 SELECT * FROM t ORDER BY c1 ASC;
1091 c1 c2
1092 1 10
1093 2 20
1094 126 30
1095 127 40
1096 UPDATE t SET c1 = 130 where c1 = 127;
1097 Warnings:
1098 Warning 1264 Out of range value for column 'c1' at row 1
1099 SELECT * FROM t ORDER BY c1 ASC;
1100 c1 c2
1101 1 10
1102 2 20
1103 126 30
1104 127 40
1105 UPDATE t SET c1 = -140 where c1 = 126;
1106 Warnings:
1107 Warning 1264 Out of range value for column 'c1' at row 1
1108 SELECT * FROM t ORDER BY c1 ASC;
1109 c1 c2
1110 -128 30
1111 1 10
1112 2 20
1113 127 40
1114 DROP TABLE t;
1115 CREATE TABLE t1
1116 (a INT NULL AUTO_INCREMENT,
1117 UNIQUE KEY (a))
1118 ENGINE='Aria'
1119 PARTITION BY KEY(a) PARTITIONS 2;
1120 SET LAST_INSERT_ID = 999;
1121 SET INSERT_ID = 0;
1122 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
1123 SELECT LAST_INSERT_ID();
1124 LAST_INSERT_ID()
1125 999
1126 SELECT * FROM t1;
1127 a
1128 1
1129 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
1130 SELECT LAST_INSERT_ID();
1131 LAST_INSERT_ID()
1132 999
1133 SELECT * FROM t1;
1134 a
1135 0
1136 UPDATE t1 SET a = 1 WHERE a IS NULL;
1137 SELECT LAST_INSERT_ID();
1138 LAST_INSERT_ID()
1139 999
1140 SELECT * FROM t1;
1141 a
1142 0
1143 UPDATE t1 SET a = NULL WHERE a = 1;
1144 SELECT LAST_INSERT_ID();
1145 LAST_INSERT_ID()
1146 999
1147 SELECT * FROM t1;
1148 a
1149 0
1150 DROP TABLE t1;
1151 ##############################################################################
1152 #
1153 # MDEV-19622 Assertion failures in
1154 # ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table
1155 #
1156 CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE='Aria' PARTITION BY HASH(a);
1157 INSERT INTO t1 VALUES (1,1),(2,2);
1158 UPDATE t1 SET pk = 0;
1159 DROP TABLE t1;
1160 #
1161 # MDEV-29636 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
1162 # failed in ha_partition::set_auto_increment_if_higher upon REPLACE
1163 # with partition pruning
1164 #
1165 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='Aria'
1166 PARTITION BY RANGE (a) (
1167 PARTITION p0 VALUES LESS THAN (10),
1168 PARTITION pn VALUES LESS THAN MAXVALUE
1169 );
1170 REPLACE INTO t1 PARTITION (p0) SELECT 1;
1171 DROP TABLE t1;
1172 #
1173 # MDEV-21027 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
1174 # ha_partition::set_auto_increment_if_higher
1175 #
1176 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='Aria' PARTITION BY HASH (a) PARTITIONS 3;
1177 REPLACE INTO t1 PARTITION (p0) VALUES (3);
1178 DROP TABLE t1;
1179 #
1180 # MDEV-21310 AUTO_INCREMENT column throws range error on INSERT in partitioned table |
1181 # Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' failed.
1182 #
1183 CREATE TABLE t1 (c INT AUTO_INCREMENT KEY) ENGINE='Aria' PARTITION BY LIST (c) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
1184 ALTER TABLE t1 TRUNCATE PARTITION p1;
1185 INSERT INTO t1 PARTITION (p1) (c) SELECT 1;
1186 DROP TABLE t1;