Debian Codegraph @

mysql-test/suite/innodb/r/autoinc_persist,desc.rdiff

raw
1 --- autoinc_persist.result
2 +++ autoinc_persist.result,desc
3 @@ -13,224 +13,224 @@
4 #
5 # Pre-create several tables
6 SET SQL_MODE='STRICT_ALL_TABLES';
7 -CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
8 +CREATE TABLE t1(a TINYINT AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
9 INSERT INTO t1 VALUES(0), (0), (0), (0), (-1), (-10), (0),
10 (20), (30), (31);
11 SELECT * FROM t1;
12 a
13 --10
14 --1
15 -1
16 -2
17 -3
18 -4
19 -5
20 -20
21 -30
22 31
23 -CREATE TABLE t2(a TINYINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
24 +30
25 +20
26 +5
27 +4
28 +3
29 +2
30 +1
31 +-1
32 +-10
33 +CREATE TABLE t2(a TINYINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
34 INSERT INTO t2 VALUES(-5);
35 ERROR 22003: Out of range value for column 'a' at row 1
36 INSERT INTO t2 VALUES(0), (0), (0), (0), (8), (10), (0),
37 (20), (30), (31);
38 SELECT * FROM t2;
39 a
40 -1
41 -2
42 -3
43 -4
44 -8
45 -10
46 -11
47 -20
48 -30
49 31
50 -CREATE TABLE t3(a SMALLINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
51 +30
52 +20
53 +11
54 +10
55 +8
56 +4
57 +3
58 +2
59 +1
60 +CREATE TABLE t3(a SMALLINT AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
61 INSERT INTO t3 VALUES(0), (0), (0), (0), (-1), (-10), (0),
62 (20), (30), (31), (1024), (4096);
63 SELECT * FROM t3;
64 a
65 --10
66 --1
67 -1
68 -2
69 -3
70 -4
71 -5
72 -20
73 -30
74 -31
75 -1024
76 4096
77 -CREATE TABLE t4(a SMALLINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
78 +1024
79 +31
80 +30
81 +20
82 +5
83 +4
84 +3
85 +2
86 +1
87 +-1
88 +-10
89 +CREATE TABLE t4(a SMALLINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
90 INSERT INTO t4 VALUES(-5);
91 ERROR 22003: Out of range value for column 'a' at row 1
92 INSERT INTO t4 VALUES(0), (0), (0), (0), (8), (10), (0),
93 (20), (30), (31), (1024), (4096);
94 SELECT * FROM t4;
95 a
96 -1
97 -2
98 -3
99 -4
100 -8
101 -10
102 -11
103 -20
104 -30
105 -31
106 -1024
107 4096
108 -CREATE TABLE t5(a MEDIUMINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
109 +1024
110 +31
111 +30
112 +20
113 +11
114 +10
115 +8
116 +4
117 +3
118 +2
119 +1
120 +CREATE TABLE t5(a MEDIUMINT AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
121 INSERT INTO t5 VALUES(0), (0), (0), (0), (-1), (-10), (0),
122 (20), (30), (31), (1000000), (1000005);
123 SELECT * FROM t5;
124 a
125 --10
126 --1
127 -1
128 -2
129 -3
130 -4
131 -5
132 -20
133 -30
134 -31
135 -1000000
136 1000005
137 -CREATE TABLE t6(a MEDIUMINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
138 +1000000
139 +31
140 +30
141 +20
142 +5
143 +4
144 +3
145 +2
146 +1
147 +-1
148 +-10
149 +CREATE TABLE t6(a MEDIUMINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
150 INSERT INTO t6 VALUES(-5);
151 ERROR 22003: Out of range value for column 'a' at row 1
152 INSERT INTO t6 VALUES(0), (0), (0), (0), (8), (10), (0),
153 (20), (30), (31), (1000000), (1000005);
154 SELECT * FROM t6;
155 a
156 -1
157 -2
158 -3
159 -4
160 -8
161 -10
162 -11
163 -20
164 -30
165 -31
166 -1000000
167 1000005
168 -CREATE TABLE t7(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB;
169 +1000000
170 +31
171 +30
172 +20
173 +11
174 +10
175 +8
176 +4
177 +3
178 +2
179 +1
180 +CREATE TABLE t7(a INT AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
181 INSERT INTO t7 VALUES(0), (0), (0), (0), (-1), (-10), (0),
182 (20), (30), (31), (100000000), (100000008);
183 SELECT * FROM t7;
184 a
185 --10
186 --1
187 -1
188 -2
189 -3
190 -4
191 -5
192 -20
193 -30
194 -31
195 -100000000
196 100000008
197 -CREATE TABLE t8(a INT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
198 +100000000
199 +31
200 +30
201 +20
202 +5
203 +4
204 +3
205 +2
206 +1
207 +-1
208 +-10
209 +CREATE TABLE t8(a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
210 INSERT INTO t8 VALUES(-5);
211 ERROR 22003: Out of range value for column 'a' at row 1
212 INSERT INTO t8 VALUES(0), (0), (0), (0), (8), (10), (0),
213 (20), (30), (31), (100000000), (100000008);
214 SELECT * FROM t8;
215 a
216 -1
217 -2
218 -3
219 -4
220 -8
221 -10
222 -11
223 -20
224 -30
225 -31
226 -100000000
227 100000008
228 -CREATE TABLE t9(a BIGINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
229 +100000000
230 +31
231 +30
232 +20
233 +11
234 +10
235 +8
236 +4
237 +3
238 +2
239 +1
240 +CREATE TABLE t9(a BIGINT AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
241 INSERT INTO t9 VALUES(0), (0), (0), (0), (-1), (-10), (0),
242 (20), (30), (31), (100000000000), (100000000006);
243 SELECT * FROM t9;
244 a
245 --10
246 --1
247 -1
248 -2
249 -3
250 -4
251 -5
252 -20
253 -30
254 -31
255 -100000000000
256 100000000006
257 -CREATE TABLE t10(a BIGINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
258 +100000000000
259 +31
260 +30
261 +20
262 +5
263 +4
264 +3
265 +2
266 +1
267 +-1
268 +-10
269 +CREATE TABLE t10(a BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
270 INSERT INTO t10 VALUES(-5);
271 ERROR 22003: Out of range value for column 'a' at row 1
272 INSERT INTO t10 VALUES(0), (0), (0), (0), (8), (10), (0),
273 (20), (30), (31), (100000000000), (100000000006);
274 SELECT * FROM t10;
275 a
276 -1
277 -2
278 -3
279 -4
280 -8
281 -10
282 -11
283 -20
284 -30
285 -31
286 -100000000000
287 100000000006
288 -CREATE TABLE t11(a FLOAT AUTO_INCREMENT KEY) ENGINE = InnoDB;
289 +100000000000
290 +31
291 +30
292 +20
293 +11
294 +10
295 +8
296 +4
297 +3
298 +2
299 +1
300 +CREATE TABLE t11(a FLOAT AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
301 INSERT INTO t11 VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
302 SELECT * FROM t11;
303 a
304 --10
305 --1
306 -1
307 -2
308 -3
309 -4
310 -5
311 -20
312 -30
313 31
314 -CREATE TABLE t11u(a FLOAT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
315 +30
316 +20
317 +5
318 +4
319 +3
320 +2
321 +1
322 +-1
323 +-10
324 +CREATE TABLE t11u(a FLOAT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
325 INSERT INTO t11u VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
326 ERROR 22003: Out of range value for column 'a' at row 5
327 INSERT INTO t11u VALUES(0), (0), (0), (0), (0), (20), (30), (31);
328 SELECT * FROM t11u;
329 a
330 -11
331 -12
332 -13
333 -14
334 -15
335 -20
336 -30
337 31
338 -CREATE TABLE t12(a DOUBLE AUTO_INCREMENT KEY) ENGINE = InnoDB;
339 +30
340 +20
341 +15
342 +14
343 +13
344 +12
345 +11
346 +CREATE TABLE t12(a DOUBLE AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB;
347 INSERT INTO t12 VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
348 SELECT * FROM t12;
349 a
350 --10
351 --1
352 -1
353 -2
354 -3
355 -4
356 -5
357 -20
358 -30
359 31
360 +30
361 +20
362 +5
363 +4
364 +3
365 +2
366 +1
367 +-1
368 +-10
369 CREATE TABLE t12u(a DOUBLE UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
370 INSERT INTO t12u VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
371 ERROR 22003: Out of range value for column 'a' at row 5
372 @@ -268,14 +268,14 @@
373 SELECT MAX(a) AS `Expect 100000000000` FROM t9;
374 Expect 100000000000
375 100000000000
376 -CREATE TABLE t13(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB,
377 +CREATE TABLE t13(a INT AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE = InnoDB,
378 AUTO_INCREMENT = 1234;
379 # restart
380 SHOW CREATE TABLE t13;
381 Table Create Table
382 t13 CREATE TABLE `t13` (
383 `a` int(11) NOT NULL AUTO_INCREMENT,
384 - PRIMARY KEY (`a`)
385 + PRIMARY KEY (`a` DESC)
386 ) ENGINE=InnoDB AUTO_INCREMENT=1234 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
387 INSERT INTO t13 VALUES(0);
388 SELECT a AS `Expect 1234` FROM t13;
389 @@ -490,28 +490,28 @@
390 INSERT INTO t1 VALUES(0), (0);
391 SELECT * FROM t1;
392 a
393 -1
394 2
395 +1
396 INSERT INTO t3 VALUES(0), (0);
397 SELECT * FROM t3;
398 a
399 -1
400 2
401 +1
402 INSERT INTO t5 VALUES(0), (0);
403 SELECT * FROM t5;
404 a
405 -1
406 2
407 +1
408 INSERT INTO t7 VALUES(0), (0);
409 SELECT * FROM t7;
410 a
411 -1
412 2
413 +1
414 INSERT INTO t9 VALUES(0), (0);
415 SELECT * FROM t9;
416 a
417 -1
418 2
419 +1
420 # Ensure that all changes before the server is killed are persisted.
421 set global innodb_flush_log_at_trx_commit=1;
422 TRUNCATE TABLE t1;
423 @@ -524,63 +524,63 @@
424 INSERT INTO t19 VALUES(0), (0);
425 SELECT * FROM t19;
426 a
427 -1
428 2
429 +1
430 # restart
431 INSERT INTO t1 VALUES(0), (0);
432 SELECT * FROM t1;
433 a
434 -1
435 2
436 +1
437 INSERT INTO t3 VALUES(0), (0);
438 SELECT * FROM t3;
439 a
440 -1
441 2
442 +1
443 INSERT INTO t5 VALUES(0), (0);
444 SELECT * FROM t5;
445 a
446 -1
447 2
448 +1
449 INSERT INTO t7 VALUES(0), (0);
450 SELECT * FROM t7;
451 a
452 -1
453 2
454 +1
455 INSERT INTO t19 VALUES(0), (0);
456 SELECT * FROM t19;
457 a
458 -1
459 -2
460 -3
461 4
462 +3
463 +2
464 +1
465 DELETE FROM t19 WHERE a = 4;
466 RENAME TABLE t19 to t9;
467 INSERT INTO t9 VALUES(0), (0);
468 SELECT * FROM t9;
469 a
470 -1
471 -2
472 -3
473 -5
474 6
475 +5
476 +3
477 +2
478 +1
479 TRUNCATE TABLE t9;
480 INSERT INTO t9 VALUES(0), (0);
481 SELECT * FROM t9;
482 a
483 -1
484 2
485 +1
486 # Scenario 8: Test ALTER TABLE operations
487 INSERT INTO t3 VALUES(0), (0), (100), (200), (1000);
488 SELECT * FROM t3;
489 a
490 -1
491 -2
492 -3
493 -4
494 -100
495 -200
496 1000
497 +200
498 +100
499 +4
500 +3
501 +2
502 +1
503 DELETE FROM t3 WHERE a > 300;
504 SELECT MAX(a) AS `Expect 200` FROM t3;
505 Expect 200
506 @@ -592,7 +592,7 @@
507 Table Create Table
508 t3 CREATE TABLE `t3` (
509 `a` smallint(6) NOT NULL AUTO_INCREMENT,
510 - PRIMARY KEY (`a`)
511 + PRIMARY KEY (`a` DESC)
512 ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
513 INSERT INTO t3 VALUES(0);
514 SELECT MAX(a) AS `Expect 201` FROM t3;
515 @@ -605,7 +605,7 @@
516 Table Create Table
517 t3 CREATE TABLE `t3` (
518 `a` smallint(6) NOT NULL AUTO_INCREMENT,
519 - PRIMARY KEY (`a`)
520 + PRIMARY KEY (`a` DESC)
521 ) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
522 INSERT INTO t3 VALUES(0);
523 SELECT MAX(a) AS `Expect 500` FROM t3;
524 @@ -617,13 +617,13 @@
525 Table Create Table
526 t3 CREATE TABLE `t3` (
527 `a` smallint(6) NOT NULL AUTO_INCREMENT,
528 - PRIMARY KEY (`a`)
529 + PRIMARY KEY (`a` DESC)
530 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
531 INSERT INTO t3 VALUES(0), (0);
532 SELECT * FROM t3;
533 a
534 -100
535 101
536 +100
537 INSERT INTO t3 VALUES(150), (180);
538 UPDATE t3 SET a = 200 WHERE a = 150;
539 INSERT INTO t3 VALUES(220);
540 @@ -633,7 +633,7 @@
541 Table Create Table
542 t3 CREATE TABLE `t3` (
543 `a` smallint(6) NOT NULL AUTO_INCREMENT,
544 - PRIMARY KEY (`a`)
545 + PRIMARY KEY (`a` DESC)
546 ) ENGINE=InnoDB AUTO_INCREMENT=221 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
547 INSERT INTO t3 VALUES(0);
548 SELECT MAX(a) AS `Expect 221` FROM t3;
549 @@ -645,7 +645,7 @@
550 Table Create Table
551 t3 CREATE TABLE `t3` (
552 `a` smallint(6) NOT NULL AUTO_INCREMENT,
553 - PRIMARY KEY (`a`)
554 + PRIMARY KEY (`a` DESC)
555 ) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
556 # MDEV-6076: Test adding an AUTO_INCREMENT COLUMN
557 CREATE TABLE mdev6076a (b INT) ENGINE=InnoDB;
558 @@ -695,18 +695,18 @@
559 INSERT INTO t_inplace SELECT * FROM t3;
560 SELECT * FROM t_inplace;
561 a
562 -100
563 -101
564 -120
565 -121
566 -122
567 -200
568 210
569 +200
570 +122
571 +121
572 +120
573 +101
574 +100
575 SHOW CREATE TABLE t_inplace;
576 Table Create Table
577 t_inplace CREATE TABLE `t_inplace` (
578 `a` smallint(6) NOT NULL AUTO_INCREMENT,
579 - PRIMARY KEY (`a`)
580 + PRIMARY KEY (`a` DESC)
581 ) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
582 # This will keep the autoinc counter
583 ALTER TABLE t_inplace AUTO_INCREMENT = 250, ALGORITHM = INPLACE;
584 @@ -715,7 +715,7 @@
585 Table Create Table
586 t_inplace CREATE TABLE `t_inplace` (
587 `a` smallint(6) NOT NULL AUTO_INCREMENT,
588 - PRIMARY KEY (`a`)
589 + PRIMARY KEY (`a` DESC)
590 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
591 # This should keep the autoinc counter as well
592 ALTER TABLE t_inplace ADD COLUMN b INT, ALGORITHM = INPLACE;
593 @@ -725,16 +725,16 @@
594 t_inplace CREATE TABLE `t_inplace` (
595 `a` smallint(6) NOT NULL AUTO_INCREMENT,
596 `b` int(11) DEFAULT NULL,
597 - PRIMARY KEY (`a`)
598 + PRIMARY KEY (`a` DESC)
599 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
600 DELETE FROM t_inplace WHERE a > 150;
601 SELECT * FROM t_inplace;
602 a b
603 -100 NULL
604 -101 NULL
605 -120 NULL
606 -121 NULL
607 122 NULL
608 +121 NULL
609 +120 NULL
610 +101 NULL
611 +100 NULL
612 # This should reset the autoinc counter to the one specified
613 # Since it's smaller than current one but bigger than existing
614 # biggest counter in the table
615 @@ -745,7 +745,7 @@
616 t_inplace CREATE TABLE `t_inplace` (
617 `a` smallint(6) NOT NULL AUTO_INCREMENT,
618 `b` int(11) DEFAULT NULL,
619 - PRIMARY KEY (`a`)
620 + PRIMARY KEY (`a` DESC)
621 ) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
622 # This should reset the autoinc counter to the next value of
623 # current max counter in the table, since the specified value
624 @@ -756,7 +756,7 @@
625 Table Create Table
626 t_inplace CREATE TABLE `t_inplace` (
627 `a` smallint(6) NOT NULL AUTO_INCREMENT,
628 - PRIMARY KEY (`a`)
629 + PRIMARY KEY (`a` DESC)
630 ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
631 INSERT INTO t_inplace VALUES(0), (0);
632 SELECT MAX(a) AS `Expect 124` FROM t_inplace;
633 @@ -783,18 +783,18 @@
634 INSERT INTO t_copy SELECT * FROM t3;
635 SELECT * FROM t_copy;
636 a
637 -100
638 -101
639 -120
640 -121
641 -122
642 -200
643 210
644 +200
645 +122
646 +121
647 +120
648 +101
649 +100
650 SHOW CREATE TABLE t_copy;
651 Table Create Table
652 t_copy CREATE TABLE `t_copy` (
653 `a` smallint(6) NOT NULL AUTO_INCREMENT,
654 - PRIMARY KEY (`a`)
655 + PRIMARY KEY (`a` DESC)
656 ) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
657 # This will keep the autoinc counter
658 ALTER TABLE t_copy AUTO_INCREMENT = 250, ALGORITHM = COPY;
659 @@ -803,7 +803,7 @@
660 Table Create Table
661 t_copy CREATE TABLE `t_copy` (
662 `a` smallint(6) NOT NULL AUTO_INCREMENT,
663 - PRIMARY KEY (`a`)
664 + PRIMARY KEY (`a` DESC)
665 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
666 # This should keep the autoinc counter as well
667 ALTER TABLE t_copy ADD COLUMN b INT, ALGORITHM = COPY;
668 @@ -813,16 +813,16 @@
669 t_copy CREATE TABLE `t_copy` (
670 `a` smallint(6) NOT NULL AUTO_INCREMENT,
671 `b` int(11) DEFAULT NULL,
672 - PRIMARY KEY (`a`)
673 + PRIMARY KEY (`a` DESC)
674 ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
675 DELETE FROM t_copy WHERE a > 150;
676 SELECT * FROM t_copy;
677 a b
678 -100 NULL
679 -101 NULL
680 -120 NULL
681 -121 NULL
682 122 NULL
683 +121 NULL
684 +120 NULL
685 +101 NULL
686 +100 NULL
687 # This should reset the autoinc counter to the one specified
688 # Since it's smaller than current one but bigger than existing
689 # biggest counter in the table
690 @@ -833,7 +833,7 @@
691 t_copy CREATE TABLE `t_copy` (
692 `a` smallint(6) NOT NULL AUTO_INCREMENT,
693 `b` int(11) DEFAULT NULL,
694 - PRIMARY KEY (`a`)
695 + PRIMARY KEY (`a` DESC)
696 ) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
697 # This should reset the autoinc counter to the next value of
698 # current max counter in the table, since the specified value
699 @@ -844,7 +844,7 @@
700 Table Create Table
701 t_copy CREATE TABLE `t_copy` (
702 `a` smallint(6) NOT NULL AUTO_INCREMENT,
703 - PRIMARY KEY (`a`)
704 + PRIMARY KEY (`a` DESC)
705 ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
706 INSERT INTO t_copy VALUES(0), (0);
707 SELECT MAX(a) AS `Expect 124` FROM t_copy;
708 @@ -868,7 +868,7 @@
709 126
710 DROP TABLE t_copy, it_copy;
711 # Scenario 9: Test the sql_mode = NO_AUTO_VALUE_ON_ZERO
712 -CREATE TABLE t30 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, key(b)) ENGINE = InnoDB;
713 +CREATE TABLE t30 (a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(a DESC), b INT, key(b)) ENGINE = InnoDB;
714 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
715 INSERT INTO t30 VALUES(NULL, 1), (200, 2), (0, 3);
716 INSERT INTO t30(b) VALUES(4), (5), (6), (7);
717 @@ -895,20 +895,20 @@
718 set global innodb_flush_log_at_trx_commit=1;
719 CREATE TABLE t31 (a INT) ENGINE = InnoDB;
720 INSERT INTO t31 VALUES(1), (2);
721 -ALTER TABLE t31 ADD b INT AUTO_INCREMENT PRIMARY KEY;
722 +ALTER TABLE t31 ADD b INT AUTO_INCREMENT, ADD PRIMARY KEY(b DESC);
723 INSERT INTO t31 VALUES(3, 0), (4, NULL), (5, NULL);
724 INSERT INTO t31 VALUES(6, 0);
725 ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
726 SELECT * FROM t31;
727 a b
728 -3 0
729 -1 1
730 -2 2
731 -4 3
732 5 4
733 +4 3
734 +2 2
735 +1 1
736 +3 0
737 SET SQL_MODE = 0;
738 # Scenario 10: Rollback would not rollback the counter
739 -CREATE TABLE t32 (a BIGINT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
740 +CREATE TABLE t32 (a BIGINT AUTO_INCREMENT, PRIMARY KEY(a DESC)) ENGINE=InnoDB;
741 INSERT INTO t32 VALUES(0), (0);
742 # Ensure that all changes before the server is killed are persisted.
743 set global innodb_flush_log_at_trx_commit=1;
744 @@ -923,7 +923,7 @@
745 # increasing the counter
746 CREATE TABLE t33 (
747 a BIGINT NOT NULL PRIMARY KEY,
748 -b BIGINT NOT NULL AUTO_INCREMENT, KEY(b)) ENGINE = InnoDB;
749 +b BIGINT NOT NULL AUTO_INCREMENT, INDEX(b DESC)) ENGINE = InnoDB;
750 INSERT INTO t33 VALUES(1, NULL);
751 INSERT INTO t33 VALUES(2, NULL);
752 INSERT INTO t33 VALUES(2, NULL);
753 @@ -946,13 +946,13 @@
754 INSERT INTO t31(a) VALUES(6), (0);
755 SELECT * FROM t31;
756 a b
757 -3 0
758 -1 1
759 -2 2
760 -4 3
761 -5 4
762 -6 5
763 0 6
764 +6 5
765 +5 4
766 +4 3
767 +2 2
768 +1 1
769 +3 0
770 DROP TABLE t31;
771 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
772 DELETE FROM t30 WHERE a = 0;
773 @@ -991,7 +991,7 @@
774 DROP TABLE t33;
775 CREATE TABLE t33 (
776 a BIGINT NOT NULL PRIMARY KEY,
777 -b BIGINT NOT NULL AUTO_INCREMENT, KEY(b)) ENGINE = InnoDB;
778 +b BIGINT NOT NULL AUTO_INCREMENT, INDEX(b DESC)) ENGINE = InnoDB;
779 ALTER TABLE t33 DISCARD TABLESPACE;
780 restore: t33 .ibd and .cfg files
781 ALTER TABLE t33 IMPORT TABLESPACE;
782 @@ -1001,8 +1001,8 @@
783 4
784 SELECT * FROM t33;
785 a b
786 -10 1
787 -2 2
788 3 4
789 +2 2
790 +10 1
791 DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t11u, t12u,
792 t30, t32, t33;