5 files changed, 259 insertions, 4 deletions
diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index 884e0fabb5e..15156b89d8e 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -104,7 +104,7 @@ explain extended select last_insert_id();
 --error ER_DUP_ENTRY
 insert into t1 set i = 254;
 select last_insert_id();
---error ER_DUP_ENTRY
+--error 167
 insert into t1 set i = null;
 select last_insert_id();
 drop table t1;
@@ -113,6 +113,7 @@ 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 167
 insert into t1 set i = null;
 select last_insert_id();
 drop table t1;
@@ -353,7 +354,7 @@ INSERT INTO t1 VALUES (18446744073709551601);
 SET @@SESSION.AUTO_INCREMENT_INCREMENT=10;
 
 SELECT @@SESSION.AUTO_INCREMENT_OFFSET;
---error ER_WARN_DATA_OUT_OF_RANGE
+--error 167
 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
 SELECT * FROM t1;
 
diff --git a/mysql-test/t/auto_increment_ranges.inc b/mysql-test/t/auto_increment_ranges.inc
new file mode 100644
index 00000000000..a94aa46d38e
--- /dev/null
+++ b/mysql-test/t/auto_increment_ranges.inc
@@ -0,0 +1,240 @@
+#
+# Test of auto_increment at end of range
+#
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+--echo #
+--echo # Testing ranges with smallint
+--echo #
+let $type=smallint;
+let $range_max=32767;
+
+eval create table t1 (a $type primary key auto_increment);
+eval insert into t1 values($range_max);
+--error 167
+insert into t1 values(NULL);
+truncate table t1;
+eval insert into t1 values($range_max-1);
+insert into t1 values(NULL);
+--error 167
+insert into t1 values(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max),(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max-1),(NULL),(NULL);
+truncate table t1;
+eval insert into t1 values($range_max+1);
+select * from t1;
+--error 167
+eval insert into t1 values(NULL);
+drop table t1;
+
+--echo #
+--echo # Testing ranges with unsigned smallint
+--echo #
+
+let $type=smallint unsigned;
+let $range_max=65535;
+
+eval create table t1 (a $type primary key auto_increment);
+eval insert into t1 values($range_max);
+--error 167
+insert into t1 values(NULL);
+truncate table t1;
+eval insert into t1 values($range_max-1);
+insert into t1 values(NULL);
+--error 167
+insert into t1 values(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max),(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max-1),(NULL),(NULL);
+truncate table t1;
+eval insert into t1 values($range_max+1);
+select * from t1;
+--error 167
+eval insert into t1 values(NULL);
+drop table t1;
+
+--echo #
+--echo # Testing ranges with integer
+--echo #
+
+let $type=int;
+let $range_max=2147483647;
+
+eval create table t1 (a $type primary key auto_increment);
+eval insert into t1 values($range_max);
+--error 167
+insert into t1 values(NULL);
+truncate table t1;
+eval insert into t1 values($range_max-1);
+insert into t1 values(NULL);
+--error 167
+insert into t1 values(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max),(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max-1),(NULL),(NULL);
+truncate table t1;
+eval insert into t1 values($range_max+1);
+select * from t1;
+--error 167
+eval insert into t1 values(NULL);
+drop table t1;
+
+--echo #
+--echo # Testing ranges with unsigned integer
+--echo #
+
+let $type=int unsigned;
+let $range_max=4294967295;
+
+eval create table t1 (a $type primary key auto_increment);
+eval insert into t1 values($range_max);
+--error 167
+insert into t1 values(NULL);
+truncate table t1;
+eval insert into t1 values($range_max-1);
+insert into t1 values(NULL);
+--error 167
+insert into t1 values(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max),(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max-1),(NULL),(NULL);
+truncate table t1;
+eval insert into t1 values($range_max+1);
+select * from t1;
+--error 167
+eval insert into t1 values(NULL);
+drop table t1;
+
+--echo #
+--echo # Testing ranges with bigint
+--echo #
+
+let $type=bigint;
+let $range_max=cast(9223372036854775807 as unsigned);
+
+eval create table t1 (a $type primary key auto_increment);
+eval insert into t1 values($range_max);
+--error 167
+insert into t1 values(NULL);
+truncate table t1;
+eval insert into t1 values($range_max-1);
+insert into t1 values(NULL);
+--error 167
+insert into t1 values(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max),(NULL);
+select * from t1;
+truncate table t1;
+--error 167
+eval insert into t1 values($range_max-1),(NULL),(NULL);
+truncate table t1;
+eval insert into t1 values($range_max+1);
+select * from t1;
+--error 167
+eval insert into t1 values(NULL);
+drop table t1;
+
+--echo #
+--echo # Testing ranges with unsigned bigint
+--echo #
+
+let $type=bigint unsigned;
+let $range_max=18446744073709551615;
+
+eval create table t1 (a $type primary key auto_increment);
+eval insert into t1 values($range_max-1);
+--error ER_AUTOINC_READ_FAILED
+insert into t1 values(NULL);
+--error ER_AUTOINC_READ_FAILED
+insert into t1 values(NULL);
+truncate table t1;
+eval insert into t1 values($range_max-1);
+--error ER_AUTOINC_READ_FAILED
+insert into t1 values(NULL);
+--error ER_AUTOINC_READ_FAILED
+insert into t1 values(NULL);
+select * from t1;
+truncate table t1;
+--error ER_AUTOINC_READ_FAILED
+eval insert into t1 values($range_max),(NULL);
+select * from t1;
+truncate table t1;
+--error ER_AUTOINC_READ_FAILED
+eval insert into t1 values($range_max-1),(NULL),(NULL);
+drop table t1;
+
+--echo #
+--echo # Test IGNORE and strict mode
+--echo #
+create table t1 (a smallint primary key auto_increment);
+insert ignore into t1 values(32766),(NULL),(NULL),(1);
+select * from t1;
+truncate table t1;
+
+set @org_mode=@@sql_mode;
+set @@sql_mode='ansi,traditional';
+insert ignore into t1 values(32766),(NULL),(NULL);
+truncate table t1;
+--error 167
+insert into t1 values(32766),(NULL),(NULL);
+set @@sql_mode=@org_mode;
+drop table t1;
+
+--echo #
+--echo # Test auto increment with negative numbers
+--echo #
+CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (-5), (NULL);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Test inserting a value out-of-range into an auto increment column
+--echo #
+CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (32768);
+--error 167
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Test old behaviour
+--echo #
+create table t1 (a smallint primary key auto_increment);
+insert into t1 values(32766),(NULL);
+delete from t1 where a=32767;
+--error 167
+insert into t1 values(NULL);
+drop table t1;
diff --git a/mysql-test/t/auto_increment_ranges_innodb.test b/mysql-test/t/auto_increment_ranges_innodb.test
new file mode 100644
index 00000000000..c2afee7ac66
--- /dev/null
+++ b/mysql-test/t/auto_increment_ranges_innodb.test
@@ -0,0 +1,7 @@
+#
+# Test of auto_increment at end of range
+#
+
+--source include/have_innodb.inc
+set default_storage_engine=innodb;
+--source auto_increment_ranges.inc
diff --git a/mysql-test/t/auto_increment_ranges_myisam.test b/mysql-test/t/auto_increment_ranges_myisam.test
new file mode 100644
index 00000000000..90e84377540
--- /dev/null
+++ b/mysql-test/t/auto_increment_ranges_myisam.test
@@ -0,0 +1,7 @@
+#
+# Test of auto_increment at end of range
+#
+
+set default_storage_engine=MYISAM;
+--source auto_increment_ranges.inc
+
diff --git a/mysql-test/t/replace.test b/mysql-test/t/replace.test
index ff910bdf5cf..3d32a8c0da6 100644
--- a/mysql-test/t/replace.test
+++ b/mysql-test/t/replace.test
@@ -25,9 +25,9 @@ drop table t1;
 
 create table t1 (a tinyint not null auto_increment primary key, b char(20) default "default_value");
 insert into t1 values (126,"first"),(63, "middle"),(0,"last");
---error ER_DUP_ENTRY
+--error 167
 insert into t1 values (0,"error");
---error ER_DUP_ENTRY
+--error 167
 replace into t1 values (0,"error");
 replace into t1 values (126,"first updated");
 replace into t1 values (63,default);