1
# inc/partition_auto_increment.inc
4
# used variables: $engine
8
DROP TABLE IF EXISTS t1;
11
-- echo # test without partitioning for reference
12
eval CREATE TABLE t1 (
13
c1 INT NOT NULL AUTO_INCREMENT,
17
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
19
INSERT INTO t1 VALUES (2);
20
INSERT INTO t1 VALUES (4);
21
INSERT INTO t1 VALUES (NULL);
22
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
24
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
26
INSERT INTO t1 VALUES (0);
27
-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
28
INSERT INTO t1 VALUES (5), (16);
31
echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
32
echo # mysql_errno: $mysql_errno;
34
INSERT INTO t1 VALUES (17);
35
INSERT INTO t1 VALUES (19), (NULL);
36
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
37
INSERT INTO t1 VALUES (NULL), (10), (NULL);
40
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
42
INSERT INTO t1 VALUES (NULL);
44
INSERT INTO t1 VALUES (NULL);
47
# InnoDB Does not handle this correctly, see bug#14793, bug#21641
48
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
49
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
51
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
52
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
54
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
55
INSERT INTO t1 VALUES (NULL);
56
INSERT INTO t1 VALUES (NULL);
58
SELECT * FROM t1 ORDER BY c1;
60
eval CREATE TABLE t1 (
61
c1 INT NOT NULL AUTO_INCREMENT,
67
INSERT INTO t1 VALUES (4);
70
INSERT INTO t1 VALUES (NULL);
77
INSERT INTO t1 VALUES (NULL);
79
SELECT * FROM t1 ORDER BY c1;
84
INSERT INTO t1 VALUES (NULL);
86
SELECT * FROM t1 ORDER BY c1;
87
INSERT INTO t1 VALUES (100);
88
INSERT INTO t1 VALUES (NULL);
91
DELETE FROM t1 WHERE c1 >= 100;
93
# InnoDB does reset auto_increment on OPTIMIZE, Bug#18274
94
# Archive does reset auto_increment on OPTIMIZE, Bug#40216
99
-- echo # Simple test with NULL
100
eval CREATE TABLE t1 (
101
c1 INT NOT NULL AUTO_INCREMENT,
104
PARTITION BY HASH(c1)
106
INSERT INTO t1 VALUES (NULL);
107
SHOW CREATE TABLE t1;
111
-- echo # Test with sql_mode and first insert as 0
112
eval CREATE TABLE t1 (
114
c2 INT NOT NULL AUTO_INCREMENT,
117
PARTITION BY HASH(c2)
119
INSERT INTO t1 VALUES (1, NULL);
120
-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
121
INSERT INTO t1 VALUES (1, 1), (99, 99);
124
echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
125
echo # mysql_errno: $mysql_errno;
127
INSERT INTO t1 VALUES (1, NULL);
128
let $old_sql_mode = `select @@session.sql_mode`;
129
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
130
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
131
INSERT INTO t1 VALUES (1, 0);
134
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
136
SELECT * FROM t1 ORDER BY c1, c2;
138
eval CREATE TABLE t1 (
140
c2 INT NOT NULL AUTO_INCREMENT,
143
PARTITION BY HASH(c2)
145
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
146
INSERT INTO t1 VALUES (1, 0);
149
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
151
INSERT INTO t1 VALUES (1, 1), (1, NULL);
152
INSERT INTO t1 VALUES (2, NULL), (4, 7);
153
INSERT INTO t1 VALUES (1, NULL);
154
SELECT * FROM t1 ORDER BY c1, c2;
155
eval SET @@session.sql_mode = '$old_sql_mode';
159
-- echo # Simple test with NULL, 0 and explicit values both incr. and desc.
160
eval CREATE TABLE t1 (
161
c1 INT NOT NULL AUTO_INCREMENT,
164
PARTITION BY HASH(c1)
166
INSERT INTO t1 VALUES (2), (4), (NULL);
167
INSERT INTO t1 VALUES (0);
168
-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
169
INSERT INTO t1 VALUES (5), (16);
172
echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
173
echo # mysql_errno: $mysql_errno;
175
INSERT INTO t1 VALUES (17), (19), (NULL);
176
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
177
INSERT INTO t1 VALUES (NULL), (10), (NULL);
180
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
182
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
183
INSERT INTO t1 VALUES (NULL), (9);
186
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
188
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
189
INSERT INTO t1 VALUES (59), (55);
192
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
194
INSERT INTO t1 VALUES (NULL), (90);
195
INSERT INTO t1 VALUES (NULL);
198
UPDATE t1 SET c1 = 150 WHERE c1 = 17;
199
UPDATE t1 SET c1 = 151 WHERE c1 = 19;
201
UPDATE t1 SET c1 = 140 WHERE c1 = 150;
202
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
204
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
205
INSERT INTO t1 VALUES (NULL);
206
INSERT INTO t1 VALUES (NULL);
208
SELECT * FROM t1 ORDER BY c1;
211
-- echo # Test with auto_increment_increment and auto_increment_offset.
212
eval CREATE TABLE t1 (
213
c1 INT NOT NULL AUTO_INCREMENT,
216
PARTITION BY HASH(c1)
218
let $old_increment = `SELECT @@session.auto_increment_increment`;
219
let $old_offset = `SELECT @@session.auto_increment_offset`;
220
SET @@session.auto_increment_increment = 10;
221
SET @@session.auto_increment_offset = 5;
222
INSERT INTO t1 VALUES (1);
223
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
224
SET @@session.auto_increment_increment = 5;
225
SET @@session.auto_increment_offset = 3;
226
INSERT INTO t1 VALUES (NULL);
227
let $new_val = `SELECT LAST_INSERT_ID()`;
228
eval INSERT INTO t1 VALUES ($new_val + 1);
229
INSERT INTO t1 VALUES (NULL);
230
let $new_val = `SELECT LAST_INSERT_ID()`;
231
eval INSERT INTO t1 VALUES ($new_val + 2);
232
INSERT INTO t1 VALUES (NULL);
233
let $new_val = `SELECT LAST_INSERT_ID()`;
234
eval INSERT INTO t1 VALUES ($new_val + 3);
235
INSERT INTO t1 VALUES (NULL);
236
let $new_val = `SELECT LAST_INSERT_ID()`;
237
eval INSERT INTO t1 VALUES ($new_val + 4);
238
INSERT INTO t1 VALUES (NULL);
239
let $new_val = `SELECT LAST_INSERT_ID()`;
240
eval INSERT INTO t1 VALUES ($new_val + 5);
241
INSERT INTO t1 VALUES (NULL);
242
let $new_val = `SELECT LAST_INSERT_ID()`;
243
eval INSERT INTO t1 VALUES ($new_val + 6);
244
INSERT INTO t1 VALUES (NULL);
245
eval SET @@session.auto_increment_increment = $old_increment;
246
eval SET @@session.auto_increment_offset = $old_offset;
247
SELECT * FROM t1 ORDER BY c1;
251
-- echo # Test reported auto_increment value
252
eval CREATE TABLE t1 (
253
c1 INT NOT NULL AUTO_INCREMENT,
256
PARTITION BY HASH (c1)
258
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
260
INSERT INTO t1 VALUES (2);
261
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
263
INSERT INTO t1 VALUES (4);
264
INSERT INTO t1 VALUES (NULL);
265
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
267
INSERT INTO t1 VALUES (NULL);
268
INSERT INTO t1 VALUES (17);
269
INSERT INTO t1 VALUES (19);
270
INSERT INTO t1 VALUES (NULL);
271
INSERT INTO t1 VALUES (NULL);
272
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
274
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
276
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
277
INSERT INTO t1 VALUES (10);
280
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
282
SELECT * FROM t1 ORDER BY c1;
283
INSERT INTO t1 VALUES (NULL);
284
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
286
INSERT INTO t1 VALUES (NULL);
287
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
288
INSERT INTO t1 VALUES (15);
291
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
293
INSERT INTO t1 VALUES (NULL);
294
SELECT * FROM t1 ORDER BY c1;
295
INSERT INTO t1 VALUES (NULL);
300
INSERT INTO t1 VALUES (NULL);
301
SHOW CREATE TABLE t1;
302
SELECT * FROM t1 ORDER BY c1;
307
INSERT INTO t1 VALUES (NULL);
308
SHOW CREATE TABLE t1;
309
SELECT * FROM t1 ORDER BY c1;
310
INSERT INTO t1 VALUES (100);
311
INSERT INTO t1 VALUES (NULL);
314
DELETE FROM t1 WHERE c1 >= 100;
316
# InnoDB does reset auto_increment on OPTIMIZE, Bug#18274
318
SHOW CREATE TABLE t1;
321
-- echo # Test with two threads
323
-- echo # con default
324
eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
326
PARTITION BY HASH(c1)
328
INSERT INTO t1 (c1) VALUES (2);
329
INSERT INTO t1 (c1) VALUES (4);
330
connect(con1, localhost, root,,);
333
INSERT INTO t1 (c1) VALUES (NULL);
334
INSERT INTO t1 (c1) VALUES (10);
336
-- echo # con default
337
INSERT INTO t1 (c1) VALUES (NULL);
338
INSERT INTO t1 (c1) VALUES (NULL);
339
INSERT INTO t1 (c1) VALUES (19);
340
INSERT INTO t1 (c1) VALUES (21);
343
INSERT INTO t1 (c1) VALUES (NULL);
345
-- echo # con default
346
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
347
INSERT INTO t1 (c1) VALUES (16);
350
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
354
INSERT INTO t1 (c1) VALUES (NULL);
357
-- echo # con default
358
INSERT INTO t1 (c1) VALUES (NULL);
359
SELECT * FROM t1 ORDER BY c1;
362
-- echo # Test with two threads + start transaction NO PARTITIONING
363
connect(con1, localhost, root,,);
365
-- echo # con default
366
eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
369
INSERT INTO t1 (c1) VALUES (2);
370
INSERT INTO t1 (c1) VALUES (4);
374
INSERT INTO t1 (c1) VALUES (NULL);
375
INSERT INTO t1 (c1) VALUES (10);
377
-- echo # con default
378
INSERT INTO t1 (c1) VALUES (NULL);
379
INSERT INTO t1 (c1) VALUES (NULL);
380
INSERT INTO t1 (c1) VALUES (19);
381
INSERT INTO t1 (c1) VALUES (21);
384
INSERT INTO t1 (c1) VALUES (NULL);
386
-- echo # con default
387
-- error 0, ER_DUP_KEY
388
INSERT INTO t1 (c1) VALUES (16);
391
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
395
INSERT INTO t1 (c1) VALUES (NULL);
396
SELECT * FROM t1 ORDER BY c1;
398
SELECT * FROM t1 ORDER BY c1;
401
-- echo # con default
402
INSERT INTO t1 (c1) VALUES (NULL);
403
SELECT * FROM t1 ORDER BY c1;
405
SELECT * FROM t1 ORDER BY c1;
408
-- echo # Test with two threads + start transaction
409
connect(con1, localhost, root,,);
411
-- echo # con default
412
eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
414
PARTITION BY HASH(c1)
417
INSERT INTO t1 (c1) VALUES (2);
418
INSERT INTO t1 (c1) VALUES (4);
422
INSERT INTO t1 (c1) VALUES (NULL), (10);
424
-- echo # con default
425
INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
426
INSERT INTO t1 (c1) VALUES (21);
429
INSERT INTO t1 (c1) VALUES (NULL);
431
-- echo # con default
432
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
433
INSERT INTO t1 (c1) VALUES (16);
436
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
440
INSERT INTO t1 (c1) VALUES (NULL);
441
SELECT * FROM t1 ORDER BY c1;
443
SELECT * FROM t1 ORDER BY c1;
446
-- echo # con default
447
INSERT INTO t1 (c1) VALUES (NULL);
448
SELECT * FROM t1 ORDER BY c1;
450
SELECT * FROM t1 ORDER BY c1;
455
-- echo # Test with another column after
456
eval CREATE TABLE t1 (
457
c1 INT NOT NULL AUTO_INCREMENT,
461
PARTITION BY HASH(c2)
463
INSERT INTO t1 VALUES (1, 0);
464
INSERT INTO t1 VALUES (1, 1);
465
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
466
INSERT INTO t1 VALUES (NULL, 3);
467
INSERT INTO t1 VALUES (2, 0), (NULL, 2);
468
INSERT INTO t1 VALUES (2, 2);
469
INSERT INTO t1 VALUES (2, 22);
470
INSERT INTO t1 VALUES (NULL, 2);
471
SELECT * FROM t1 ORDER BY c1,c2;
475
-- echo # Test with another column before
476
eval CREATE TABLE t1 (
478
c2 INT NOT NULL AUTO_INCREMENT,
481
PARTITION BY HASH(c2)
483
INSERT INTO t1 VALUES (1, 0);
484
-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
485
INSERT INTO t1 VALUES (1, 1);
488
echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
489
echo # mysql_errno: $mysql_errno;
491
INSERT INTO t1 VALUES (1, NULL);
492
INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
493
INSERT INTO t1 VALUES (2, NULL);
494
-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
495
INSERT INTO t1 VALUES (2, 2);
498
echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
499
echo # mysql_errno: $mysql_errno;
501
INSERT INTO t1 VALUES (2, 22);
502
INSERT INTO t1 VALUES (2, NULL);
503
SELECT * FROM t1 ORDER BY c1,c2;
506
-- echo # Test with auto_increment on secondary column in multi-column-index
507
-- disable_abort_on_error
508
eval CREATE TABLE t1 (
510
c2 INT NOT NULL AUTO_INCREMENT,
513
PARTITION BY HASH(c2)
515
-- enable_abort_on_error
517
eval SET @my_errno= $mysql_errno ;
518
let $run = `SELECT @my_errno = 0`;
519
# ER_WRONG_AUTO_KEY is 1075
520
let $ER_WRONG_AUTO_KEY= 1075;
521
if (`SELECT @my_errno NOT IN (0,$ER_WRONG_AUTO_KEY)`)
523
-- echo # Unknown error code, exits
529
INSERT INTO t1 VALUES (1, 0);
530
-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
531
INSERT INTO t1 VALUES (1, 1);
534
echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
535
echo # mysql_errno: $mysql_errno;
537
INSERT INTO t1 VALUES (1, NULL);
538
INSERT INTO t1 VALUES (2, NULL);
539
INSERT INTO t1 VALUES (3, NULL);
540
INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
541
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
542
INSERT INTO t1 VALUES (2, 2);
545
echo # ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY;
546
echo # mysql_errno: $mysql_errno;
548
INSERT INTO t1 VALUES (2, 22), (2, NULL);
549
SELECT * FROM t1 ORDER BY c1,c2;
553
-- echo # Test AUTO_INCREMENT in CREATE
554
eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
557
PARTITION BY HASH(c1)
559
SHOW CREATE TABLE t1;
560
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
561
INSERT INTO t1 (c1) VALUES (4);
564
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
566
SHOW CREATE TABLE t1;
567
INSERT INTO t1 (c1) VALUES (0);
568
SHOW CREATE TABLE t1;
569
INSERT INTO t1 (c1) VALUES (NULL);
570
SHOW CREATE TABLE t1;
571
SELECT * FROM t1 ORDER BY c1;
573
-- echo # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
574
let $old_sql_mode = `select @@session.sql_mode`;
575
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
576
INSERT INTO t1 (c1) VALUES (300);
577
SHOW CREATE TABLE t1;
578
-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
579
INSERT INTO t1 (c1) VALUES (0);
582
echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
584
SHOW CREATE TABLE t1;
585
INSERT INTO t1 (c1) VALUES (NULL);
586
SHOW CREATE TABLE t1;
587
SELECT * FROM t1 ORDER BY c1;
588
eval SET @@session.sql_mode = '$old_sql_mode';
591
-- echo # Test SET INSERT_ID
592
eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
594
PARTITION BY HASH(c1)
596
SHOW CREATE TABLE t1;
597
INSERT INTO t1 (c1) VALUES (NULL);
598
SHOW CREATE TABLE t1;
601
SHOW CREATE TABLE t1;
602
INSERT INTO t1 (c1) VALUES (NULL);
603
SHOW CREATE TABLE t1;
604
SELECT * FROM t1 ORDER BY c1;
607
-- echo # Testing with FLUSH TABLE
608
eval CREATE TABLE t1 (
609
c1 INT NOT NULL AUTO_INCREMENT,
612
PARTITION BY HASH(c1)
614
SHOW CREATE TABLE t1;
616
SHOW CREATE TABLE t1;
617
INSERT INTO t1 VALUES (4);
619
SHOW CREATE TABLE t1;
620
INSERT INTO t1 VALUES (NULL);
622
SHOW CREATE TABLE t1;
623
SELECT * FROM t1 ORDER BY c1;
626
if (!$skip_negative_auto_inc)
628
--echo #############################################################################
629
--echo # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
630
--echo # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
631
--echo ##############################################################################
633
--echo # Inserting negative autoincrement values into a partition table (partitions >= 4)
635
eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
636
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
638
INSERT INTO t(c2) VALUES (10);
639
INSERT INTO t(c2) VALUES (20);
640
INSERT INTO t VALUES (-1,-10);
641
INSERT INTO t(c2) VALUES (30);
642
INSERT INTO t(c2) VALUES (40);
644
SELECT * FROM t ORDER BY c1 ASC;
648
--echo # Reading from a partition table (partitions >= 2 ) after inserting a negative
649
--echo # value into the auto increment column
652
eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
653
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2;
655
INSERT INTO t VALUES (-2,-20);
656
INSERT INTO t(c2) VALUES (30);
658
SELECT * FROM t ORDER BY c1 ASC;
662
--echo # Inserting negative auto increment value into a partition table (partitions >= 2)
663
--echo # auto increment value > 2.
665
eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
666
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2;
668
INSERT INTO t VALUES (-4,-20);
669
INSERT INTO t(c2) VALUES (30);
670
INSERT INTO t(c2) VALUES (40);
672
SELECT * FROM t ORDER BY c1 ASC;
676
--echo # Inserting -1 into autoincrement column of a partition table (partition >= 4)
678
eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
679
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
681
INSERT INTO t(c2) VALUES (10);
682
INSERT INTO t(c2) VALUES (20);
683
INSERT INTO t VALUES (-1,-10);
685
SELECT * FROM t ORDER BY c1 ASC;
687
INSERT INTO t(c2) VALUES (30);
689
SELECT * FROM t ORDER BY c1 ASC;
693
--echo # Deleting from an auto increment table after inserting negative values
695
eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
696
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
698
INSERT INTO t(c2) VALUES (10);
699
INSERT INTO t(c2) VALUES (20);
700
INSERT INTO t VALUES (-1,-10);
701
INSERT INTO t(c2) VALUES (30);
702
INSERT INTO t VALUES (-3,-20);
703
INSERT INTO t(c2) VALUES (40);
705
SELECT * FROM t ORDER BY c1 ASC;
709
DELETE FROM t WHERE c1 > 1;
712
SELECT * FROM t ORDER BY c1 ASC;
716
--echo # Inserting a positive value that exceeds maximum allowed value for an
717
--echo # Auto Increment column (positive maximum)
719
eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
720
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
722
INSERT INTO t(c2) VALUES (10);
723
INSERT INTO t(c2) VALUES (20);
724
INSERT INTO t VALUES (126,30);
725
INSERT INTO t VALUES (127,40);
728
INSERT INTO t VALUES (128,50);
730
INSERT INTO t VALUES (129,60);
732
SELECT * FROM t ORDER BY c1 ASC;
736
--echo # Inserting a negative value that goes below minimum allowed value for an
737
--echo # Auto Increment column (negative minimum)
739
eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
740
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
742
INSERT INTO t(c2) VALUES (10);
743
INSERT INTO t(c2) VALUES (20);
744
INSERT INTO t VALUES (-127,30);
745
INSERT INTO t VALUES (-128,40);
748
INSERT INTO t VALUES (-129,50);
750
INSERT INTO t VALUES (-130,60);
752
SELECT * FROM t ORDER BY c1 ASC;
756
--echo # Updating the partition table with a negative Auto Increment value
758
eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
759
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
761
INSERT INTO t(c2) VALUES (10);
762
INSERT INTO t(c2) VALUES (20);
763
INSERT INTO t VALUES (-1,-10);
764
INSERT INTO t(c2) VALUES (30);
766
SELECT * FROM t ORDER BY c1 ASC;
770
UPDATE t SET c1 = -6 WHERE c1 = 2;
773
SELECT * FROM t ORDER BY c1 ASC;
775
INSERT INTO t(c2) VALUES (40);
776
INSERT INTO t(c2) VALUES (50);
780
UPDATE t SET c1 = -6 WHERE c1 = 2;
783
SELECT * FROM t ORDER BY c1 ASC;
787
--echo # Updating the partition table with a value that crosses the upper limits
788
--echo # on both the positive and the negative side.
790
eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
791
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
793
INSERT INTO t(c2) VALUES (10);
794
INSERT INTO t(c2) VALUES (20);
795
INSERT INTO t VALUES (126,30);
796
INSERT INTO t VALUES (127,40);
798
SELECT * FROM t ORDER BY c1 ASC;
802
UPDATE t SET c1 = 130 where c1 = 127;
805
SELECT * FROM t ORDER BY c1 ASC;
809
UPDATE t SET c1 = -140 where c1 = 126;
812
SELECT * FROM t ORDER BY c1 ASC;
816
--echo ##############################################################################