Debian Codegraph @

mysql-test/suite/parts/r/partition_auto_increment_memory.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='Memory';
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=MEMORY 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 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='Memory';
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=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 note The storage engine for the table doesn't support optimize
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=MEMORY 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='Memory';
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='Memory'
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=MEMORY 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='Memory'
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='Memory'
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='Memory'
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='Memory'
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='Memory'
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=MEMORY 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=MEMORY 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 The storage engine for the table doesn't support optimize
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=MEMORY 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 = 'Memory'
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 = 'Memory';
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 = 'Memory'
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 = 'Memory'
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 = 'Memory'
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 = 'Memory'
716 PARTITION BY HASH(c2)
717 PARTITIONS 2;
718 ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
719 # Test AUTO_INCREMENT in CREATE
720 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
721 ENGINE = 'Memory'
722 AUTO_INCREMENT = 15
723 PARTITION BY HASH(c1)
724 PARTITIONS 2;
725 SHOW CREATE TABLE t1;
726 Table Create Table
727 t1 CREATE TABLE `t1` (
728 `c1` int(11) NOT NULL AUTO_INCREMENT,
729 PRIMARY KEY (`c1`)
730 ) ENGINE=MEMORY AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
731 PARTITION BY HASH (`c1`)
732 PARTITIONS 2
733 INSERT INTO t1 (c1) VALUES (4);
734 SHOW CREATE TABLE t1;
735 Table Create Table
736 t1 CREATE TABLE `t1` (
737 `c1` int(11) NOT NULL AUTO_INCREMENT,
738 PRIMARY KEY (`c1`)
739 ) ENGINE=MEMORY AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
740 PARTITION BY HASH (`c1`)
741 PARTITIONS 2
742 INSERT INTO t1 (c1) VALUES (0);
743 SHOW CREATE TABLE t1;
744 Table Create Table
745 t1 CREATE TABLE `t1` (
746 `c1` int(11) NOT NULL AUTO_INCREMENT,
747 PRIMARY KEY (`c1`)
748 ) ENGINE=MEMORY AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
749 PARTITION BY HASH (`c1`)
750 PARTITIONS 2
751 INSERT INTO t1 (c1) VALUES (NULL);
752 SHOW CREATE TABLE t1;
753 Table Create Table
754 t1 CREATE TABLE `t1` (
755 `c1` int(11) NOT NULL AUTO_INCREMENT,
756 PRIMARY KEY (`c1`)
757 ) ENGINE=MEMORY AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
758 PARTITION BY HASH (`c1`)
759 PARTITIONS 2
760 SELECT * FROM t1 ORDER BY c1;
761 c1
762 4
763 15
764 16
765 # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
766 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
767 INSERT INTO t1 (c1) VALUES (300);
768 SHOW CREATE TABLE t1;
769 Table Create Table
770 t1 CREATE TABLE `t1` (
771 `c1` int(11) NOT NULL AUTO_INCREMENT,
772 PRIMARY KEY (`c1`)
773 ) ENGINE=MEMORY AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
774 PARTITION BY HASH (`c1`)
775 PARTITIONS 2
776 INSERT INTO t1 (c1) VALUES (0);
777 SHOW CREATE TABLE t1;
778 Table Create Table
779 t1 CREATE TABLE `t1` (
780 `c1` int(11) NOT NULL AUTO_INCREMENT,
781 PRIMARY KEY (`c1`)
782 ) ENGINE=MEMORY AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
783 PARTITION BY HASH (`c1`)
784 PARTITIONS 2
785 INSERT INTO t1 (c1) VALUES (NULL);
786 SHOW CREATE TABLE t1;
787 Table Create Table
788 t1 CREATE TABLE `t1` (
789 `c1` int(11) NOT NULL AUTO_INCREMENT,
790 PRIMARY KEY (`c1`)
791 ) ENGINE=MEMORY AUTO_INCREMENT=302 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
792 PARTITION BY HASH (`c1`)
793 PARTITIONS 2
794 SELECT * FROM t1 ORDER BY c1;
795 c1
796 0
797 4
798 15
799 16
800 300
801 301
802 SET @@session.sql_mode = '';
803 DROP TABLE t1;
804 # Test SET INSERT_ID
805 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
806 ENGINE = 'Memory'
807 PARTITION BY HASH(c1)
808 PARTITIONS 2;
809 SHOW CREATE TABLE t1;
810 Table Create Table
811 t1 CREATE TABLE `t1` (
812 `c1` int(11) NOT NULL AUTO_INCREMENT,
813 PRIMARY KEY (`c1`)
814 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
815 PARTITION BY HASH (`c1`)
816 PARTITIONS 2
817 INSERT INTO t1 (c1) VALUES (NULL);
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=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
824 PARTITION BY HASH (`c1`)
825 PARTITIONS 2
826 SELECT * FROM t1;
827 c1
828 1
829 SET INSERT_ID = 23;
830 SHOW CREATE TABLE t1;
831 Table Create Table
832 t1 CREATE TABLE `t1` (
833 `c1` int(11) NOT NULL AUTO_INCREMENT,
834 PRIMARY KEY (`c1`)
835 ) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
836 PARTITION BY HASH (`c1`)
837 PARTITIONS 2
838 INSERT INTO t1 (c1) VALUES (NULL);
839 SHOW CREATE TABLE t1;
840 Table Create Table
841 t1 CREATE TABLE `t1` (
842 `c1` int(11) NOT NULL AUTO_INCREMENT,
843 PRIMARY KEY (`c1`)
844 ) ENGINE=MEMORY AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
845 PARTITION BY HASH (`c1`)
846 PARTITIONS 2
847 SET INSERT_ID = 22;
848 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
849 INSERT INTO t1 VALUES (NULL);
850 SELECT * FROM t1 ORDER BY c1;
851 c1
852 1
853 22
854 23
855 24
856 DROP TABLE t1;
857 # Testing with FLUSH TABLE
858 CREATE TABLE t1 (
859 c1 INT NOT NULL AUTO_INCREMENT,
860 PRIMARY KEY (c1))
861 ENGINE='Memory'
862 PARTITION BY HASH(c1)
863 PARTITIONS 2;
864 SHOW CREATE TABLE t1;
865 Table Create Table
866 t1 CREATE TABLE `t1` (
867 `c1` int(11) NOT NULL AUTO_INCREMENT,
868 PRIMARY KEY (`c1`)
869 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
870 PARTITION BY HASH (`c1`)
871 PARTITIONS 2
872 FLUSH TABLE;
873 SHOW CREATE TABLE t1;
874 Table Create Table
875 t1 CREATE TABLE `t1` (
876 `c1` int(11) NOT NULL AUTO_INCREMENT,
877 PRIMARY KEY (`c1`)
878 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
879 PARTITION BY HASH (`c1`)
880 PARTITIONS 2
881 INSERT INTO t1 VALUES (4);
882 FLUSH TABLE;
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=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
889 PARTITION BY HASH (`c1`)
890 PARTITIONS 2
891 INSERT INTO t1 VALUES (NULL);
892 FLUSH TABLE;
893 SHOW CREATE TABLE t1;
894 Table Create Table
895 t1 CREATE TABLE `t1` (
896 `c1` int(11) NOT NULL AUTO_INCREMENT,
897 PRIMARY KEY (`c1`)
898 ) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
899 PARTITION BY HASH (`c1`)
900 PARTITIONS 2
901 SELECT * FROM t1 ORDER BY c1;
902 c1
903 4
904 5
905 DROP TABLE t1;
906 #############################################################################
907 # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
908 # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
909 ##############################################################################
910 # Inserting negative autoincrement values into a partition table (partitions >= 4)
911 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
912 c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4;
913 INSERT INTO t(c2) VALUES (10);
914 INSERT INTO t(c2) VALUES (20);
915 INSERT INTO t VALUES (-1,-10);
916 INSERT INTO t(c2) VALUES (30);
917 INSERT INTO t(c2) VALUES (40);
918 SELECT * FROM t ORDER BY c1 ASC;
919 c1 c2
920 -1 -10
921 1 10
922 2 20
923 3 30
924 4 40
925 DROP TABLE t;
926 # Reading from a partition table (partitions >= 2 ) after inserting a negative
927 # value into the auto increment column
928 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
929 c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 2;
930 INSERT INTO t VALUES (-2,-20);
931 INSERT INTO t(c2) VALUES (30);
932 SELECT * FROM t ORDER BY c1 ASC;
933 c1 c2
934 -2 -20
935 1 30
936 DROP TABLE t;
937 # Inserting negative auto increment value into a partition table (partitions >= 2)
938 # auto increment value > 2.
939 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
940 c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 2;
941 INSERT INTO t VALUES (-4,-20);
942 INSERT INTO t(c2) VALUES (30);
943 INSERT INTO t(c2) VALUES (40);
944 SELECT * FROM t ORDER BY c1 ASC;
945 c1 c2
946 -4 -20
947 1 30
948 2 40
949 DROP TABLE t;
950 # Inserting -1 into autoincrement column of a partition table (partition >= 4)
951 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
952 c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4;
953 INSERT INTO t(c2) VALUES (10);
954 INSERT INTO t(c2) VALUES (20);
955 INSERT INTO t VALUES (-1,-10);
956 SELECT * FROM t ORDER BY c1 ASC;
957 c1 c2
958 -1 -10
959 1 10
960 2 20
961 INSERT INTO t(c2) VALUES (30);
962 SELECT * FROM t ORDER BY c1 ASC;
963 c1 c2
964 -1 -10
965 1 10
966 2 20
967 3 30
968 DROP TABLE t;
969 # Deleting from an auto increment table after inserting negative values
970 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
971 c2 INT) ENGINE='Memory' 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 INSERT INTO t(c2) VALUES (30);
976 INSERT INTO t VALUES (-3,-20);
977 INSERT INTO t(c2) VALUES (40);
978 SELECT * FROM t ORDER BY c1 ASC;
979 c1 c2
980 -3 -20
981 -1 -10
982 1 10
983 2 20
984 3 30
985 4 40
986 DELETE FROM t WHERE c1 > 1;
987 SELECT * FROM t ORDER BY c1 ASC;
988 c1 c2
989 -3 -20
990 -1 -10
991 1 10
992 DROP TABLE t;
993 # Inserting a positive value that exceeds maximum allowed value for an
994 # Auto Increment column (positive maximum)
995 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
996 c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4;
997 INSERT INTO t(c2) VALUES (10);
998 INSERT INTO t(c2) VALUES (20);
999 INSERT INTO t VALUES (126,30);
1000 INSERT INTO t VALUES (127,40);
1001 INSERT INTO t VALUES (128,50);
1002 ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
1003 INSERT INTO t VALUES (129,60);
1004 ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
1005 SELECT * FROM t ORDER BY c1 ASC;
1006 c1 c2
1007 1 10
1008 2 20
1009 126 30
1010 127 40
1011 DROP TABLE t;
1012 # Inserting a negative value that goes below minimum allowed value for an
1013 # Auto Increment column (negative minimum)
1014 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1015 c2 INT) ENGINE='Memory' 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 (-127,30);
1019 INSERT INTO t VALUES (-128,40);
1020 INSERT INTO t VALUES (-129,50);
1021 ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
1022 INSERT INTO t VALUES (-130,60);
1023 ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
1024 SELECT * FROM t ORDER BY c1 ASC;
1025 c1 c2
1026 -128 40
1027 -127 30
1028 1 10
1029 2 20
1030 DROP TABLE t;
1031 # Updating the partition table with a negative Auto Increment value
1032 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1033 c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4;
1034 INSERT INTO t(c2) VALUES (10);
1035 INSERT INTO t(c2) VALUES (20);
1036 INSERT INTO t VALUES (-1,-10);
1037 INSERT INTO t(c2) VALUES (30);
1038 SELECT * FROM t ORDER BY c1 ASC;
1039 c1 c2
1040 -1 -10
1041 1 10
1042 2 20
1043 3 30
1044 UPDATE t SET c1 = -6 WHERE c1 = 2;
1045 SELECT * FROM t ORDER BY c1 ASC;
1046 c1 c2
1047 -6 20
1048 -1 -10
1049 1 10
1050 3 30
1051 INSERT INTO t(c2) VALUES (40);
1052 INSERT INTO t(c2) VALUES (50);
1053 UPDATE t SET c1 = -6 WHERE c1 = 2;
1054 SELECT * FROM t ORDER BY c1 ASC;
1055 c1 c2
1056 -6 20
1057 -1 -10
1058 1 10
1059 3 30
1060 4 40
1061 5 50
1062 DROP TABLE t;
1063 # Updating the partition table with a value that crosses the upper limits
1064 # on both the positive and the negative side.
1065 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1066 c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4;
1067 INSERT INTO t(c2) VALUES (10);
1068 INSERT INTO t(c2) VALUES (20);
1069 INSERT INTO t VALUES (126,30);
1070 INSERT INTO t VALUES (127,40);
1071 SELECT * FROM t ORDER BY c1 ASC;
1072 c1 c2
1073 1 10
1074 2 20
1075 126 30
1076 127 40
1077 UPDATE t SET c1 = 130 where c1 = 127;
1078 Warnings:
1079 Warning 1264 Out of range value for column 'c1' at row 1
1080 SELECT * FROM t ORDER BY c1 ASC;
1081 c1 c2
1082 1 10
1083 2 20
1084 126 30
1085 127 40
1086 UPDATE t SET c1 = -140 where c1 = 126;
1087 Warnings:
1088 Warning 1264 Out of range value for column 'c1' at row 1
1089 SELECT * FROM t ORDER BY c1 ASC;
1090 c1 c2
1091 -128 30
1092 1 10
1093 2 20
1094 127 40
1095 DROP TABLE t;
1096 CREATE TABLE t1
1097 (a INT NULL AUTO_INCREMENT,
1098 UNIQUE KEY (a))
1099 ENGINE='Memory'
1100 PARTITION BY KEY(a) PARTITIONS 2;
1101 SET LAST_INSERT_ID = 999;
1102 SET INSERT_ID = 0;
1103 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
1104 SELECT LAST_INSERT_ID();
1105 LAST_INSERT_ID()
1106 999
1107 SELECT * FROM t1;
1108 a
1109 1
1110 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
1111 SELECT LAST_INSERT_ID();
1112 LAST_INSERT_ID()
1113 999
1114 SELECT * FROM t1;
1115 a
1116 0
1117 UPDATE t1 SET a = 1 WHERE a IS NULL;
1118 SELECT LAST_INSERT_ID();
1119 LAST_INSERT_ID()
1120 999
1121 SELECT * FROM t1;
1122 a
1123 0
1124 UPDATE t1 SET a = NULL WHERE a = 1;
1125 SELECT LAST_INSERT_ID();
1126 LAST_INSERT_ID()
1127 999
1128 SELECT * FROM t1;
1129 a
1130 0
1131 DROP TABLE t1;
1132 ##############################################################################
1133 #
1134 # MDEV-19622 Assertion failures in
1135 # ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table
1136 #
1137 CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE='Memory' PARTITION BY HASH(a);
1138 INSERT INTO t1 VALUES (1,1),(2,2);
1139 UPDATE t1 SET pk = 0;
1140 DROP TABLE t1;
1141 #
1142 # MDEV-29636 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
1143 # failed in ha_partition::set_auto_increment_if_higher upon REPLACE
1144 # with partition pruning
1145 #
1146 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='Memory'
1147 PARTITION BY RANGE (a) (
1148 PARTITION p0 VALUES LESS THAN (10),
1149 PARTITION pn VALUES LESS THAN MAXVALUE
1150 );
1151 REPLACE INTO t1 PARTITION (p0) SELECT 1;
1152 DROP TABLE t1;
1153 #
1154 # MDEV-21027 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
1155 # ha_partition::set_auto_increment_if_higher
1156 #
1157 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='Memory' PARTITION BY HASH (a) PARTITIONS 3;
1158 REPLACE INTO t1 PARTITION (p0) VALUES (3);
1159 DROP TABLE t1;
1160 #
1161 # MDEV-21310 AUTO_INCREMENT column throws range error on INSERT in partitioned table |
1162 # Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' failed.
1163 #
1164 CREATE TABLE t1 (c INT AUTO_INCREMENT KEY) ENGINE='Memory' PARTITION BY LIST (c) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
1165 ALTER TABLE t1 TRUNCATE PARTITION p1;
1166 INSERT INTO t1 PARTITION (p1) (c) SELECT 1;
1167 DROP TABLE t1;