1drop table if exists t1; 2drop table if exists t2; 3SET SQL_WARNINGS=1; 4create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3; 5insert into t1 values (1,1),(NULL,3),(NULL,4); 6delete from t1 where a=4; 7insert into t1 values (NULL,5),(NULL,6); 8select * from t1; 9a b 101 1 113 3 125 5 136 6 14delete from t1 where a=6; 15replace t1 values (3,1); 16ALTER TABLE t1 add c int; 17replace t1 values (3,3,3); 18insert into t1 values (NULL,7,7); 19update t1 set a=8,b=b+1,c=c+1 where a=7; 20insert into t1 values (NULL,9,9); 21select * from t1; 22a b c 231 1 NULL 243 3 3 255 5 NULL 268 8 8 279 9 9 28drop table t1; 29create table t1 ( 30skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY, 31sval char(20) 32); 33insert into t1 values (NULL, "hello"); 34insert into t1 values (NULL, "hey"); 35select * from t1; 36skey sval 371 hello 382 hey 39select _rowid,t1._rowid,skey,sval from t1; 40_rowid _rowid skey sval 411 1 1 hello 422 2 2 hey 43drop table t1; 44create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)); 45insert into t1 values ("a",1),("b",2),("a",2),("c",1); 46insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL); 47insert into t1 (a) values ("a"),("b"),("c"),("d"); 48insert into t1 (a) values ('k'),('d'); 49insert into t1 (a) values ("a"); 50insert into t1 values ("d",last_insert_id()); 51select * from t1; 52a b 53a 1 54a 2 55a 3 56a 4 57a 5 58b 2 59b 3 60b 4 61c 1 62c 2 63c 3 64d 1 65d 2 66d 5 67e 1 68k 1 69drop table t1; 70create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid)); 71insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj'); 72select * from t1; 73ordid ord 741 sdj 752 sdj 76drop table t1; 77create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)); 78insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc'); 79select * from t1; 80ordid ord 811 abc 822 abc 833 abc 841 sdj 852 sdj 863 sdj 871 zzz 88drop table t1; 89create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id)); 90create table t2 (sid char(20), id int(2)); 91insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL); 92insert into t1 select * from t2; 93select * from t1; 94sid id 95skr 1 96skr 2 97test 1 98drop table t1,t2; 99create table t1 (a int not null primary key auto_increment); 100insert into t1 values (0); 101update t1 set a=0; 102select * from t1; 103a 1040 105check table t1; 106Table Op Msg_type Msg_text 107test.t1 check warning Found row where the auto_increment column has the value 0 108test.t1 check status OK 109drop table t1; 110create table t1 (a int not null auto_increment primary key); 111insert into t1 values (NULL); 112insert into t1 values (-1); 113select last_insert_id(); 114last_insert_id() 1151 116insert into t1 values (NULL); 117select * from t1; 118a 119-1 1201 1212 122drop table t1; 123create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */; 124insert into t1 values (NULL); 125insert into t1 values (-1); 126select last_insert_id(); 127last_insert_id() 1281 129insert into t1 values (NULL); 130select * from t1; 131a 1321 133-1 1342 135drop table t1; 136create table t1 (i tinyint unsigned not null auto_increment primary key); 137insert into t1 set i = 254; 138insert into t1 set i = null; 139select last_insert_id(); 140last_insert_id() 141255 142explain extended select last_insert_id(); 143id select_type table type possible_keys key key_len ref rows filtered Extra 1441 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 145Warnings: 146Note 1003 select last_insert_id() AS `last_insert_id()` 147insert into t1 set i = 254; 148ERROR 23000: Duplicate entry '254' for key 'PRIMARY' 149select last_insert_id(); 150last_insert_id() 151255 152insert into t1 set i = null; 153ERROR 22003: Out of range value for column 'i' at row 1 154select last_insert_id(); 155last_insert_id() 156255 157drop table t1; 158create table t1 (i tinyint unsigned not null auto_increment, key (i)); 159insert into t1 set i = 254; 160insert into t1 set i = null; 161select last_insert_id(); 162last_insert_id() 163255 164insert into t1 set i = null; 165ERROR 22003: Out of range value for column 'i' at row 1 166select last_insert_id(); 167last_insert_id() 168255 169drop table t1; 170create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b)); 171insert into t1 values (NULL, 10); 172select last_insert_id(); 173last_insert_id() 1741 175insert into t1 values (NULL, 15); 176select last_insert_id(); 177last_insert_id() 1782 179insert into t1 values (NULL, 10); 180ERROR 23000: Duplicate entry '10' for key 'b' 181select last_insert_id(); 182last_insert_id() 1832 184drop table t1; 185create table t1(a int auto_increment,b int null,primary key(a)); 186SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; 187insert into t1(a,b)values(NULL,1); 188insert into t1(a,b)values(200,2); 189insert into t1(a,b)values(0,3); 190insert into t1(b)values(4); 191insert into t1(b)values(5); 192insert into t1(b)values(6); 193insert into t1(b)values(7); 194select * from t1 order by b; 195a b 1961 1 197200 2 1980 3 199201 4 200202 5 201203 6 202204 7 203alter table t1 modify b mediumint; 204select * from t1 order by b; 205a b 2061 1 207200 2 2080 3 209201 4 210202 5 211203 6 212204 7 213create table t2 (a int); 214insert t2 values (1),(2); 215alter table t2 add b int auto_increment primary key; 216select * from t2; 217a b 2181 1 2192 2 220drop table t2; 221delete from t1 where a=0; 222update t1 set a=0 where b=5; 223select * from t1 order by b; 224a b 2251 1 226200 2 227201 4 2280 5 229203 6 230204 7 231delete from t1 where a=0; 232update t1 set a=NULL where b=6; 233Warnings: 234Warning 1048 Column 'a' cannot be null 235update t1 set a=300 where b=7; 236SET SQL_MODE=''; 237insert into t1(a,b)values(NULL,8); 238insert into t1(a,b)values(400,9); 239insert into t1(a,b)values(0,10); 240insert into t1(b)values(11); 241insert into t1(b)values(12); 242insert into t1(b)values(13); 243insert into t1(b)values(14); 244select * from t1 order by b; 245a b 2461 1 247200 2 248201 4 2490 6 250300 7 251301 8 252400 9 253401 10 254402 11 255403 12 256404 13 257405 14 258delete from t1 where a=0; 259update t1 set a=0 where b=12; 260select * from t1 order by b; 261a b 2621 1 263200 2 264201 4 265300 7 266301 8 267400 9 268401 10 269402 11 2700 12 271404 13 272405 14 273delete from t1 where a=0; 274update t1 set a=NULL where b=13; 275Warnings: 276Warning 1048 Column 'a' cannot be null 277update t1 set a=500 where b=14; 278select * from t1 order by b; 279a b 2801 1 281200 2 282201 4 283300 7 284301 8 285400 9 286401 10 287402 11 2880 13 289500 14 290drop table t1; 291create table t1 (a bigint); 292insert into t1 values (1), (2), (3), (NULL), (NULL); 293alter table t1 modify a bigint not null auto_increment primary key; 294select * from t1; 295a 2961 2972 2983 2994 3005 301drop table t1; 302create table t1 (a bigint); 303insert into t1 values (1), (2), (3), (0), (0); 304alter table t1 modify a bigint not null auto_increment primary key; 305select * from t1; 306a 3071 3082 3093 3104 3115 312drop table t1; 313create table t1 (a bigint); 314insert into t1 values (0), (1), (2), (3); 315set sql_mode=NO_AUTO_VALUE_ON_ZERO; 316alter table t1 modify a bigint not null auto_increment primary key; 317set sql_mode= ''; 318select * from t1; 319a 3200 3211 3222 3233 324drop table t1; 325create table t1 (a int auto_increment primary key , b int null); 326set sql_mode=NO_AUTO_VALUE_ON_ZERO; 327insert into t1 values (0,1),(1,2),(2,3); 328select * from t1; 329a b 3300 1 3311 2 3322 3 333set sql_mode= ''; 334alter table t1 modify b varchar(255); 335insert into t1 values (0,4); 336select * from t1; 337a b 3380 1 3391 2 3402 3 3413 4 342drop table t1; 343CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10))); 344INSERT INTO t1 (b) VALUES ('aaaa'); 345CHECK TABLE t1; 346Table Op Msg_type Msg_text 347test.t1 check status OK 348INSERT INTO t1 (b) VALUES (''); 349CHECK TABLE t1; 350Table Op Msg_type Msg_text 351test.t1 check status OK 352INSERT INTO t1 (b) VALUES ('bbbb'); 353CHECK TABLE t1; 354Table Op Msg_type Msg_text 355test.t1 check status OK 356DROP TABLE IF EXISTS t1; 357CREATE TABLE `t1` ( 358t1_name VARCHAR(255) DEFAULT NULL, 359t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 360KEY (t1_name), 361PRIMARY KEY (t1_id) 362) AUTO_INCREMENT = 1000; 363INSERT INTO t1 (t1_name) VALUES('MySQL'); 364INSERT INTO t1 (t1_name) VALUES('MySQL'); 365INSERT INTO t1 (t1_name) VALUES('MySQL'); 366SELECT * from t1; 367t1_name t1_id 368MySQL 1000 369MySQL 1001 370MySQL 1002 371SHOW CREATE TABLE `t1`; 372Table Create Table 373t1 CREATE TABLE `t1` ( 374 `t1_name` varchar(255) DEFAULT NULL, 375 `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 376 PRIMARY KEY (`t1_id`), 377 KEY `t1_name` (`t1_name`) 378) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1 379DROP TABLE `t1`; 380create table t1(a int not null auto_increment primary key); 381create table t2(a int not null auto_increment primary key, t1a int); 382insert into t1 values(NULL); 383insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); 384insert into t1 values (NULL); 385insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), 386(NULL, LAST_INSERT_ID()); 387insert into t1 values (NULL); 388insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), 389(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); 390select * from t2; 391a t1a 3921 1 3932 1 3943 2 3954 2 3965 2 3976 3 3987 3 3998 3 4009 3 401drop table t1, t2; 402End of 4.1 tests 403CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)); 404insert into t1 (b) values (1); 405replace into t1 (b) values (2), (1), (3); 406select * from t1; 407a b 4083 1 4092 2 4104 3 411truncate table t1; 412insert into t1 (b) values (1); 413replace into t1 (b) values (2); 414replace into t1 (b) values (1); 415replace into t1 (b) values (3); 416select * from t1; 417a b 4183 1 4192 2 4204 3 421drop table t1; 422create table t1 (rowid int not null auto_increment, val int not null,primary 423key (rowid), unique(val)); 424replace into t1 (val) values ('1'),('2'); 425replace into t1 (val) values ('1'),('2'); 426insert into t1 (val) values ('1'),('2'); 427ERROR 23000: Duplicate entry '1' for key 'val' 428select * from t1; 429rowid val 4303 1 4314 2 432drop table t1; 433create table t1 (a int not null auto_increment primary key, val int); 434insert into t1 (val) values (1); 435update t1 set a=2 where a=1; 436insert into t1 (val) values (1); 437select * from t1; 438a val 4392 1 4403 1 441drop table t1; 442CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10)); 443INSERT INTO t1 VALUES(0, 0); 444INSERT INTO t1 VALUES(1, 1); 445ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment; 446ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY' 447DROP TABLE t1; 448create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c)); 449insert into t1 values(null,1,1,now()); 450insert into t1 values(null,0,0,null); 451replace into t1 values(null,1,0,null); 452select last_insert_id(); 453last_insert_id() 4543 455drop table t1; 456create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e)); 457insert into t1 values(null,1,1,1,now()); 458insert into t1 values(null,0,0,0,null); 459replace into t1 values(null,1,0,2,null); 460select last_insert_id(); 461last_insert_id() 4623 463drop table t1; 464# 465# Bug#46616: Assertion `!table->auto_increment_field_not_null' on view 466# manipulations 467# 468CREATE TABLE t1 ( a INT ); 469INSERT INTO t1 VALUES (1), (1); 470CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY ); 471INSERT INTO t2 SELECT a FROM t1; 472ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 473UPDATE t2 SET a = 2; 474SELECT a FROM t2; 475a 4762 477DROP TABLE t1, t2; 478# 479# Bug#39828 autoinc wraps around when offset and increment > 1 480# 481CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engine=MyISAM; 482INSERT INTO t1 VALUES(1); 483INSERT INTO t1 VALUES (18446744073709551601); 484SET @@SESSION.AUTO_INCREMENT_INCREMENT=10; 485SELECT @@SESSION.AUTO_INCREMENT_OFFSET; 486@@SESSION.AUTO_INCREMENT_OFFSET 4871 488INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 489ERROR 22003: Out of range value for column 'c1' at row 2 490SELECT * FROM t1; 491c1 4921 49318446744073709551601 49418446744073709551611 495SET @@SESSION.AUTO_INCREMENT_INCREMENT=default; 496SET @@SESSION.AUTO_INCREMENT_OFFSET=default; 497DROP TABLE t1; 498End of 5.1 tests 499# 500# Bug#50619 assert in handler::update_auto_increment 501# 502CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk)); 503INSERT INTO t1 VALUES (NULL), (-1), (NULL); 504SELECT * FROM t1; 505pk 506-1 5071 5082 509DROP TABLE t1; 510CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk)); 511INSERT INTO t1 VALUES (NULL), (18446744073709551615-1), (NULL); 512ERROR HY000: Failed to read auto-increment value from storage engine 513SELECT * FROM t1; 514pk 5151 51618446744073709551614 517DROP TABLE t1; 518CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk)); 519insert into t1 values((1<<63)+1); 520insert into t1 values(null); 521select last_insert_id(); 522last_insert_id() 5239223372036854775810 524select * from t1; 525pk 5269223372036854775809 5279223372036854775810 528drop table t1; 529CREATE TABLE t1 (pk BIGINT AUTO_INCREMENT, PRIMARY KEY (pk)); 530insert into t1 values(-5); 531insert into t1 values(null); 532select last_insert_id(); 533last_insert_id() 5341 535select * from t1; 536pk 537-5 5381 539drop table t1; 540# 541# End of 5.3 tests 542# 543# 544# MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA 545# 546CREATE TABLE t1 ( 547id TINYINT NOT NULL AUTO_INCREMENT, 548name CHAR(30) NOT NULL, 549PRIMARY KEY (id) 550) ENGINE=MyISAM; 551SHOW CREATE TABLE t1; 552Table Create Table 553t1 CREATE TABLE `t1` ( 554 `id` tinyint(4) NOT NULL AUTO_INCREMENT, 555 `name` char(30) NOT NULL, 556 PRIMARY KEY (`id`) 557) ENGINE=MyISAM DEFAULT CHARSET=latin1 558INSERT INTO t1 (name) VALUES ('dog'); 559UPDATE t1 SET id=-1 WHERE id=1; 560INSERT INTO t1 (name) VALUES ('cat'); 561SELECT * FROM t1; 562id name 563-1 dog 5642 cat 565DROP TABLE t1; 566# 567# End of 5.5 tests 568# 569# 570# MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number 571# 572SET @engine='MyISAM'; 573CREATE PROCEDURE autoinc_mdev15353_one(engine VARCHAR(64), t VARCHAR(64)) 574BEGIN 575DECLARE query TEXT DEFAULT 'CREATE TABLE t1 (' 576 ' id TTT NOT NULL AUTO_INCREMENT,' 577 ' name CHAR(30) NOT NULL,' 578 ' PRIMARY KEY (id)) ENGINE=EEE'; 579EXECUTE IMMEDIATE REPLACE(REPLACE(query,'TTT', t), 'EEE', engine); 580SHOW CREATE TABLE t1; 581INSERT INTO t1 (name) VALUES ('dog'); 582SELECT * FROM t1; 583UPDATE t1 SET id=-1 WHERE id=1; 584SELECT * FROM t1; 585INSERT INTO t1 (name) VALUES ('cat'); 586SELECT * FROM t1; 587DROP TABLE t1; 588END; 589$$ 590CALL autoinc_mdev15353_one(@engine, 'tinyint'); 591Table Create Table 592t1 CREATE TABLE `t1` ( 593 `id` tinyint(4) NOT NULL AUTO_INCREMENT, 594 `name` char(30) NOT NULL, 595 PRIMARY KEY (`id`) 596) ENGINE=MyISAM DEFAULT CHARSET=latin1 597id name 5981 dog 599id name 600-1 dog 601id name 602-1 dog 6032 cat 604CALL autoinc_mdev15353_one(@engine, 'smallint'); 605Table Create Table 606t1 CREATE TABLE `t1` ( 607 `id` smallint(6) NOT NULL AUTO_INCREMENT, 608 `name` char(30) NOT NULL, 609 PRIMARY KEY (`id`) 610) ENGINE=MyISAM DEFAULT CHARSET=latin1 611id name 6121 dog 613id name 614-1 dog 615id name 616-1 dog 6172 cat 618CALL autoinc_mdev15353_one(@engine, 'mediumint'); 619Table Create Table 620t1 CREATE TABLE `t1` ( 621 `id` mediumint(9) NOT NULL AUTO_INCREMENT, 622 `name` char(30) NOT NULL, 623 PRIMARY KEY (`id`) 624) ENGINE=MyISAM DEFAULT CHARSET=latin1 625id name 6261 dog 627id name 628-1 dog 629id name 630-1 dog 6312 cat 632CALL autoinc_mdev15353_one(@engine, 'int'); 633Table Create Table 634t1 CREATE TABLE `t1` ( 635 `id` int(11) NOT NULL AUTO_INCREMENT, 636 `name` char(30) NOT NULL, 637 PRIMARY KEY (`id`) 638) ENGINE=MyISAM DEFAULT CHARSET=latin1 639id name 6401 dog 641id name 642-1 dog 643id name 644-1 dog 6452 cat 646CALL autoinc_mdev15353_one(@engine, 'bigint'); 647Table Create Table 648t1 CREATE TABLE `t1` ( 649 `id` bigint(20) NOT NULL AUTO_INCREMENT, 650 `name` char(30) NOT NULL, 651 PRIMARY KEY (`id`) 652) ENGINE=MyISAM DEFAULT CHARSET=latin1 653id name 6541 dog 655id name 656-1 dog 657id name 658-1 dog 6592 cat 660CALL autoinc_mdev15353_one(@engine, 'float'); 661Table Create Table 662t1 CREATE TABLE `t1` ( 663 `id` float NOT NULL AUTO_INCREMENT, 664 `name` char(30) NOT NULL, 665 PRIMARY KEY (`id`) 666) ENGINE=MyISAM DEFAULT CHARSET=latin1 667id name 6681 dog 669id name 670-1 dog 671id name 672-1 dog 6732 cat 674CALL autoinc_mdev15353_one(@engine, 'double'); 675Table Create Table 676t1 CREATE TABLE `t1` ( 677 `id` double NOT NULL AUTO_INCREMENT, 678 `name` char(30) NOT NULL, 679 PRIMARY KEY (`id`) 680) ENGINE=MyISAM DEFAULT CHARSET=latin1 681id name 6821 dog 683id name 684-1 dog 685id name 686-1 dog 6872 cat 688DROP PROCEDURE autoinc_mdev15353_one; 689# 690# End of 10.2 tests 691# 692