summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/auto_increment.result
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--mysql-test/main/auto_increment.result691
1 files changed, 691 insertions, 0 deletions
diff --git a/mysql-test/main/auto_increment.result b/mysql-test/main/auto_increment.result
new file mode 100644
index 00000000..a1c7d500
--- /dev/null
+++ b/mysql-test/main/auto_increment.result
@@ -0,0 +1,691 @@
+drop table if exists t1;
+drop table if exists t2;
+SET SQL_WARNINGS=1;
+create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
+insert into t1 values (1,1),(NULL,3),(NULL,4);
+delete from t1 where a=4;
+insert into t1 values (NULL,5),(NULL,6);
+select * from t1;
+a b
+1 1
+3 3
+5 5
+6 6
+delete from t1 where a=6;
+replace t1 values (3,1);
+ALTER TABLE t1 add c int;
+replace t1 values (3,3,3);
+insert into t1 values (NULL,7,7);
+update t1 set a=8,b=b+1,c=c+1 where a=7;
+insert into t1 values (NULL,9,9);
+select * from t1;
+a b c
+1 1 NULL
+3 3 3
+5 5 NULL
+8 8 8
+9 9 9
+drop table t1;
+create table t1 (
+skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
+sval char(20)
+);
+insert into t1 values (NULL, "hello");
+insert into t1 values (NULL, "hey");
+select * from t1;
+skey sval
+1 hello
+2 hey
+select _rowid,t1._rowid,skey,sval from t1;
+_rowid _rowid skey sval
+1 1 1 hello
+2 2 2 hey
+drop table t1;
+create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b));
+insert into t1 values ("a",1),("b",2),("a",2),("c",1);
+insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
+insert into t1 (a) values ("a"),("b"),("c"),("d");
+insert into t1 (a) values ('k'),('d');
+insert into t1 (a) values ("a");
+insert into t1 values ("d",last_insert_id());
+select * from t1;
+a b
+a 1
+a 2
+a 3
+a 4
+a 5
+b 2
+b 3
+b 4
+c 1
+c 2
+c 3
+d 1
+d 2
+d 5
+e 1
+k 1
+drop table t1;
+create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid));
+insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
+select * from t1;
+ordid ord
+1 sdj
+2 sdj
+drop table t1;
+create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid));
+insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
+select * from t1;
+ordid ord
+1 abc
+2 abc
+3 abc
+1 sdj
+2 sdj
+3 sdj
+1 zzz
+drop table t1;
+create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id));
+create table t2 (sid char(20), id int(2));
+insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
+insert into t1 select * from t2;
+select * from t1;
+sid id
+skr 1
+skr 2
+test 1
+drop table t1,t2;
+create table t1 (a int not null primary key auto_increment);
+insert into t1 values (0);
+update t1 set a=0;
+select * from t1;
+a
+0
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check warning Found row where the auto_increment column has the value 0
+test.t1 check status OK
+drop table t1;
+create table t1 (a int not null auto_increment primary key);
+insert into t1 values (NULL);
+insert into t1 values (-1);
+select last_insert_id();
+last_insert_id()
+1
+insert into t1 values (NULL);
+select * from t1;
+a
+-1
+1
+2
+drop table t1;
+create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
+insert into t1 values (NULL);
+insert into t1 values (-1);
+select last_insert_id();
+last_insert_id()
+1
+insert into t1 values (NULL);
+select * from t1;
+a
+1
+-1
+2
+drop table t1;
+create table t1 (i tinyint unsigned not null auto_increment primary key);
+insert into t1 set i = 254;
+insert into t1 set i = null;
+select last_insert_id();
+last_insert_id()
+255
+explain extended select last_insert_id();
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select last_insert_id() AS `last_insert_id()`
+insert into t1 set i = 254;
+ERROR 23000: Duplicate entry '254' for key 'PRIMARY'
+select last_insert_id();
+last_insert_id()
+255
+insert into t1 set i = null;
+ERROR 22003: Out of range value for column 'i' at row 1
+select last_insert_id();
+last_insert_id()
+255
+drop table t1;
+create table t1 (i tinyint unsigned not null auto_increment, key (i));
+insert into t1 set i = 254;
+insert into t1 set i = null;
+select last_insert_id();
+last_insert_id()
+255
+insert into t1 set i = null;
+ERROR 22003: Out of range value for column 'i' at row 1
+select last_insert_id();
+last_insert_id()
+255
+drop table t1;
+create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
+insert into t1 values (NULL, 10);
+select last_insert_id();
+last_insert_id()
+1
+insert into t1 values (NULL, 15);
+select last_insert_id();
+last_insert_id()
+2
+insert into t1 values (NULL, 10);
+ERROR 23000: Duplicate entry '10' for key 'b'
+select last_insert_id();
+last_insert_id()
+2
+drop table t1;
+create table t1(a int auto_increment,b int null,primary key(a));
+SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
+insert into t1(a,b)values(NULL,1);
+insert into t1(a,b)values(200,2);
+insert into t1(a,b)values(0,3);
+insert into t1(b)values(4);
+insert into t1(b)values(5);
+insert into t1(b)values(6);
+insert into t1(b)values(7);
+select * from t1 order by b;
+a b
+1 1
+200 2
+0 3
+201 4
+202 5
+203 6
+204 7
+alter table t1 modify b mediumint;
+select * from t1 order by b;
+a b
+1 1
+200 2
+0 3
+201 4
+202 5
+203 6
+204 7
+create table t2 (a int);
+insert t2 values (1),(2);
+alter table t2 add b int auto_increment primary key;
+select * from t2;
+a b
+1 1
+2 2
+drop table t2;
+delete from t1 where a=0;
+update t1 set a=0 where b=5;
+select * from t1 order by b;
+a b
+1 1
+200 2
+201 4
+0 5
+203 6
+204 7
+delete from t1 where a=0;
+update t1 set a=NULL where b=6;
+Warnings:
+Warning 1048 Column 'a' cannot be null
+update t1 set a=300 where b=7;
+SET SQL_MODE='';
+insert into t1(a,b)values(NULL,8);
+insert into t1(a,b)values(400,9);
+insert into t1(a,b)values(0,10);
+insert into t1(b)values(11);
+insert into t1(b)values(12);
+insert into t1(b)values(13);
+insert into t1(b)values(14);
+select * from t1 order by b;
+a b
+1 1
+200 2
+201 4
+0 6
+300 7
+301 8
+400 9
+401 10
+402 11
+403 12
+404 13
+405 14
+delete from t1 where a=0;
+update t1 set a=0 where b=12;
+select * from t1 order by b;
+a b
+1 1
+200 2
+201 4
+300 7
+301 8
+400 9
+401 10
+402 11
+0 12
+404 13
+405 14
+delete from t1 where a=0;
+update t1 set a=NULL where b=13;
+Warnings:
+Warning 1048 Column 'a' cannot be null
+update t1 set a=500 where b=14;
+select * from t1 order by b;
+a b
+1 1
+200 2
+201 4
+300 7
+301 8
+400 9
+401 10
+402 11
+0 13
+500 14
+drop table t1;
+create table t1 (a bigint);
+insert into t1 values (1), (2), (3), (NULL), (NULL);
+alter table t1 modify a bigint not null auto_increment primary key;
+select * from t1;
+a
+1
+2
+3
+4
+5
+drop table t1;
+create table t1 (a bigint);
+insert into t1 values (1), (2), (3), (0), (0);
+alter table t1 modify a bigint not null auto_increment primary key;
+select * from t1;
+a
+1
+2
+3
+4
+5
+drop table t1;
+create table t1 (a bigint);
+insert into t1 values (0), (1), (2), (3);
+set sql_mode=NO_AUTO_VALUE_ON_ZERO;
+alter table t1 modify a bigint not null auto_increment primary key;
+set sql_mode= '';
+select * from t1;
+a
+0
+1
+2
+3
+drop table t1;
+create table t1 (a int auto_increment primary key , b int null);
+set sql_mode=NO_AUTO_VALUE_ON_ZERO;
+insert into t1 values (0,1),(1,2),(2,3);
+select * from t1;
+a b
+0 1
+1 2
+2 3
+set sql_mode= '';
+alter table t1 modify b varchar(255);
+insert into t1 values (0,4);
+select * from t1;
+a b
+0 1
+1 2
+2 3
+3 4
+drop table t1;
+CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
+INSERT INTO t1 (b) VALUES ('aaaa');
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+INSERT INTO t1 (b) VALUES ('');
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+INSERT INTO t1 (b) VALUES ('bbbb');
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+DROP TABLE IF EXISTS t1;
+CREATE TABLE `t1` (
+t1_name VARCHAR(255) DEFAULT NULL,
+t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+KEY (t1_name),
+PRIMARY KEY (t1_id)
+) AUTO_INCREMENT = 1000;
+INSERT INTO t1 (t1_name) VALUES('MySQL');
+INSERT INTO t1 (t1_name) VALUES('MySQL');
+INSERT INTO t1 (t1_name) VALUES('MySQL');
+SELECT * from t1;
+t1_name t1_id
+MySQL 1000
+MySQL 1001
+MySQL 1002
+SHOW CREATE TABLE `t1`;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `t1_name` varchar(255) DEFAULT NULL,
+ `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`t1_id`),
+ KEY `t1_name` (`t1_name`)
+) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE `t1`;
+create table t1(a int not null auto_increment primary key);
+create table t2(a int not null auto_increment primary key, t1a int);
+insert into t1 values(NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
+insert into t1 values (NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID());
+insert into t1 values (NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
+select * from t2;
+a t1a
+1 1
+2 1
+3 2
+4 2
+5 2
+6 3
+7 3
+8 3
+9 3
+drop table t1, t2;
+End of 4.1 tests
+CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`));
+insert into t1 (b) values (1);
+replace into t1 (b) values (2), (1), (3);
+select * from t1;
+a b
+3 1
+2 2
+4 3
+truncate table t1;
+insert into t1 (b) values (1);
+replace into t1 (b) values (2);
+replace into t1 (b) values (1);
+replace into t1 (b) values (3);
+select * from t1;
+a b
+3 1
+2 2
+4 3
+drop table t1;
+create table t1 (rowid int not null auto_increment, val int not null,primary
+key (rowid), unique(val));
+replace into t1 (val) values ('1'),('2');
+replace into t1 (val) values ('1'),('2');
+insert into t1 (val) values ('1'),('2');
+ERROR 23000: Duplicate entry '1' for key 'val'
+select * from t1;
+rowid val
+3 1
+4 2
+drop table t1;
+create table t1 (a int not null auto_increment primary key, val int);
+insert into t1 (val) values (1);
+update t1 set a=2 where a=1;
+insert into t1 (val) values (1);
+select * from t1;
+a val
+2 1
+3 1
+drop table t1;
+CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
+INSERT INTO t1 VALUES(0, 0);
+INSERT INTO t1 VALUES(1, 1);
+ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
+ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
+DROP TABLE t1;
+create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
+insert into t1 values(null,1,1,now());
+insert into t1 values(null,0,0,null);
+replace into t1 values(null,1,0,null);
+select last_insert_id();
+last_insert_id()
+3
+drop table t1;
+create 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));
+insert into t1 values(null,1,1,1,now());
+insert into t1 values(null,0,0,0,null);
+replace into t1 values(null,1,0,2,null);
+select last_insert_id();
+last_insert_id()
+3
+drop table t1;
+#
+# Bug#46616: Assertion `!table->auto_increment_field_not_null' on view
+# manipulations
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (1);
+CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
+INSERT INTO t2 SELECT a FROM t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+UPDATE t2 SET a = 2;
+SELECT a FROM t2;
+a
+2
+DROP TABLE t1, t2;
+#
+# Bug#39828 autoinc wraps around when offset and increment > 1
+#
+CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engine=MyISAM;
+INSERT INTO t1 VALUES(1);
+INSERT INTO t1 VALUES (18446744073709551601);
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=10;
+SELECT @@SESSION.AUTO_INCREMENT_OFFSET;
+@@SESSION.AUTO_INCREMENT_OFFSET
+1
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+ERROR 22003: Out of range value for column 'c1' at row 2
+SELECT * FROM t1;
+c1
+1
+18446744073709551601
+18446744073709551611
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=default;
+SET @@SESSION.AUTO_INCREMENT_OFFSET=default;
+DROP TABLE t1;
+End of 5.1 tests
+#
+# Bug#50619 assert in handler::update_auto_increment
+#
+CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk));
+INSERT INTO t1 VALUES (NULL), (-1), (NULL);
+SELECT * FROM t1;
+pk
+-1
+1
+2
+DROP TABLE t1;
+CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk));
+INSERT INTO t1 VALUES (NULL), (18446744073709551615-1), (NULL);
+ERROR HY000: Failed to read auto-increment value from storage engine
+SELECT * FROM t1;
+pk
+1
+18446744073709551614
+DROP TABLE t1;
+CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk));
+insert into t1 values((1<<63)+1);
+insert into t1 values(null);
+select last_insert_id();
+last_insert_id()
+9223372036854775810
+select * from t1;
+pk
+9223372036854775809
+9223372036854775810
+drop table t1;
+CREATE TABLE t1 (pk BIGINT AUTO_INCREMENT, PRIMARY KEY (pk));
+insert into t1 values(-5);
+insert into t1 values(null);
+select last_insert_id();
+last_insert_id()
+1
+select * from t1;
+pk
+-5
+1
+drop table t1;
+#
+# End of 5.3 tests
+#
+#
+# MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA
+#
+CREATE TABLE t1 (
+id TINYINT NOT NULL AUTO_INCREMENT,
+name CHAR(30) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=MyISAM;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 (name) VALUES ('dog');
+UPDATE t1 SET id=-1 WHERE id=1;
+INSERT INTO t1 (name) VALUES ('cat');
+SELECT * FROM t1;
+id name
+-1 dog
+2 cat
+DROP TABLE t1;
+#
+# End of 5.5 tests
+#
+#
+# MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number
+#
+SET @engine='MyISAM';
+CREATE PROCEDURE autoinc_mdev15353_one(engine VARCHAR(64), t VARCHAR(64))
+BEGIN
+DECLARE query TEXT DEFAULT 'CREATE TABLE t1 ('
+ ' id TTT NOT NULL AUTO_INCREMENT,'
+ ' name CHAR(30) NOT NULL,'
+ ' PRIMARY KEY (id)) ENGINE=EEE';
+EXECUTE IMMEDIATE REPLACE(REPLACE(query,'TTT', t), 'EEE', engine);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (name) VALUES ('dog');
+SELECT * FROM t1;
+UPDATE t1 SET id=-1 WHERE id=1;
+SELECT * FROM t1;
+INSERT INTO t1 (name) VALUES ('cat');
+SELECT * FROM t1;
+DROP TABLE t1;
+END;
+$$
+CALL autoinc_mdev15353_one(@engine, 'tinyint');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'smallint');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` smallint(6) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'mediumint');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` mediumint(9) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'int');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'bigint');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` bigint(20) NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'float');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` float NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+CALL autoinc_mdev15353_one(@engine, 'double');
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` double NOT NULL AUTO_INCREMENT,
+ `name` char(30) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id name
+1 dog
+id name
+-1 dog
+id name
+-1 dog
+2 cat
+DROP PROCEDURE autoinc_mdev15353_one;
+#
+# End of 10.2 tests
+#
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-23 07:07:57 +0000