path: root/mysql-test/main/auto_increment.test

#
# Test of auto_increment;  The test for BDB tables is in bdb.test
#
--disable_warnings
drop table if exists t1;
drop table if exists t2;
--enable_warnings
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;
delete from t1 where a=6;
#show table status like "t1";
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;
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;
select _rowid,t1._rowid,skey,sval from t1;
drop table t1;

#
# Test auto_increment on sub key
#
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;
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;
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;
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;
drop table t1,t2;

#
# Test of auto_increment columns when they are set to 0
#

create table t1 (a int not null primary key auto_increment);
insert into t1 values (0);
update t1 set a=0;
select * from t1;
check table t1;
drop table t1;

#
# Test negative values (Bug #1366)
#

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();
insert into t1 values (NULL);
select * from t1;
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();
insert into t1 values (NULL);
select * from t1;
drop table t1;
#
# last_insert_id() madness
#
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();
explain extended select last_insert_id();
--error ER_DUP_ENTRY
insert into t1 set i = 254;
select last_insert_id();
--error HA_ERR_AUTOINC_ERANGE
insert into t1 set i = null;
select last_insert_id();
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();
--error HA_ERR_AUTOINC_ERANGE
insert into t1 set i = null;
select last_insert_id();
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();
insert into t1 values (NULL, 15);
select last_insert_id();
--error ER_DUP_ENTRY
insert into t1 values (NULL, 10);
select last_insert_id();

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;
alter table t1 modify b mediumint;
select * from t1 order by b;
create table t2 (a int);
insert t2 values (1),(2);
alter table t2 add b int auto_increment primary key;
select * from t2;
drop table t2;
delete from t1 where a=0;
update t1 set a=0 where b=5;
select * from t1 order by b;
delete from t1 where a=0;
update t1 set a=NULL where b=6;
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;
delete from t1 where a=0;
update t1 set a=0 where b=12;
select * from t1 order by b;
delete from t1 where a=0;
update t1 set a=NULL where b=13;
update t1 set a=500 where b=14;
select * from t1 order by b;
drop table t1;

#
# Test of behavior of ALTER TABLE when coulmn containing NULL or zeroes is
# converted to AUTO_INCREMENT column
#
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;
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;
drop table t1;

# We still should be able to preserve zero in NO_AUTO_VALUE_ON_ZERO mode
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;
drop table t1;

# It also sensible to preserve zeroes if we are converting auto_increment
# column to auto_increment column (or not touching it at all, which is more
# common case probably)
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;
set sql_mode= '';
alter table t1 modify b varchar(255);
insert into t1 values (0,4);
select * from t1;
drop table t1;

#
# BUG #10045: Problem with composite AUTO_INCREMENT + BLOB key

CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
INSERT INTO t1 (b) VALUES ('aaaa');
CHECK TABLE t1;
INSERT INTO t1 (b) VALUES ('');
CHECK TABLE t1;
INSERT INTO t1 (b) VALUES ('bbbb');
CHECK TABLE t1;
DROP TABLE IF EXISTS t1;

# BUG #19025:

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;

SHOW CREATE TABLE `t1`;

DROP TABLE `t1`;

#
# Bug #6880: LAST_INSERT_ID() within a statement
#

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;
drop table t1, t2;

--echo End of 4.1 tests

#
# Bug #11080 & #11005  Multi-row REPLACE fails on a duplicate key error
# 

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;
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;
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');
--error ER_DUP_ENTRY
insert into t1 (val) values ('1'),('2');
select * from t1;
drop table t1;

#
# Test that update changes internal auto-increment value
#

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;
drop table t1;

#
# Test key duplications with auto-increment in ALTER TABLE
# bug #14573
#
CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
INSERT INTO t1 VALUES(0, 0);
INSERT INTO t1 VALUES(1, 1);
--error ER_DUP_ENTRY
ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
DROP TABLE t1;

# Test of REPLACE when it does INSERT+DELETE and not UPDATE:
# see if it sets LAST_INSERT_ID() ok
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);
# this will delete two rows
replace into t1 values(null,1,0,null);
select last_insert_id();
drop table t1;

# Test of REPLACE when it does a INSERT+DELETE for all the conflicting rows
# (i.e.) when there are three rows conflicting in unique key columns with
# a row that is being inserted, all the three rows will be deleted and then
# the new rows will be inserted.
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();
drop table t1;

--echo #
--echo # Bug#46616: Assertion `!table->auto_increment_field_not_null' on view   
--echo # manipulations
--echo #
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (1);

CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
--error ER_DUP_ENTRY
INSERT INTO t2 SELECT a FROM t1;

UPDATE t2 SET a = 2;

SELECT a FROM t2;

DROP TABLE t1, t2;

--echo #
--echo # Bug#39828 autoinc wraps around when offset and increment > 1
--echo #

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;
--error HA_ERR_AUTOINC_ERANGE
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
SELECT * FROM t1;

SET @@SESSION.AUTO_INCREMENT_INCREMENT=default;
SET @@SESSION.AUTO_INCREMENT_OFFSET=default;

DROP TABLE t1;

--echo End of 5.1 tests

--echo #
--echo # Bug#50619 assert in handler::update_auto_increment
--echo #

CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk));
# This triggered the assert
INSERT INTO t1 VALUES (NULL), (-1), (NULL);
SELECT * FROM t1;
DROP TABLE t1;

# Check that that true overflow still gives error
CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk));
--error ER_AUTOINC_READ_FAILED
INSERT INTO t1 VALUES (NULL), (18446744073709551615-1), (NULL);
SELECT * FROM t1;
DROP TABLE t1;

# MDEV-331 last_insert_id() returns a signed number
# Check that last_insert_id() generates a signed value
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();
select * from t1;
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();
select * from t1;
drop table t1;

--echo #
--echo # End of 5.3 tests
--echo #

--echo #
--echo # MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA
--echo #

CREATE TABLE t1 (
  id TINYINT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
INSERT INTO t1 (name) VALUES ('dog');
UPDATE t1 SET id=-1 WHERE id=1;
INSERT INTO t1 (name) VALUES ('cat');
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # End of 5.5 tests
--echo #

--echo #
--echo # MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number
--echo #

SET @engine='MyISAM';
--source include/autoinc_mdev15353.inc

--echo #
--echo # End of 10.2 tests
--echo #