diff options
Diffstat (limited to 'mysql-test/suite/innodb_plugin/t/innodb-autoinc.test')
| -rw-r--r-- | mysql-test/suite/innodb_plugin/t/innodb-autoinc.test | 639 |
1 files changed, 0 insertions, 639 deletions
diff --git a/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test b/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test deleted file mode 100644 index 633b0ef360b..00000000000 --- a/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test +++ /dev/null @@ -1,639 +0,0 @@ --- source include/have_innodb_plugin.inc -# embedded server ignores 'delayed', so skip this --- source include/not_embedded.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings - -# -# Bug #34335 -# -CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (9223372036854775807, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; -# -## Test AUTOINC overflow -## - -# TINYINT -CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (127, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (255, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; -# -# SMALLINT -# -CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (32767, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (65535, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; -# -# MEDIUMINT -# -CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (8388607, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (16777215, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; -# -# INT -# -CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (2147483647, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (4294967295, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; -# -# BIGINT -# -CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (9223372036854775807, null); --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (18446744073709551615, null); --- error ER_AUTOINC_READ_FAILED,1467 -INSERT INTO t1 (c2) VALUES ('innodb'); -SELECT * FROM t1; -DROP TABLE t1; - -# -# Bug 37531 -# After truncate, auto_increment behaves incorrectly for InnoDB -# -CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1), (2), (3); -INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -SELECT c1 FROM t1; -SHOW CREATE TABLE t1; -TRUNCATE TABLE t1; -SHOW CREATE TABLE t1; -INSERT INTO t1 VALUES (1), (2), (3); -INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -SELECT c1 FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -# -# Deleting all records should not reset the AUTOINC counter. -# -CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1), (2), (3); -INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -SELECT c1 FROM t1; -SHOW CREATE TABLE t1; -DELETE FROM t1; -SHOW CREATE TABLE t1; -INSERT INTO t1 VALUES (1), (2), (3); -INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -SELECT c1 FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -# -# Bug 38839 -# Reset the last value generated at end of statement -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (NULL, 1); -DELETE FROM t1 WHERE c1 = 1; -INSERT INTO t1 VALUES (2,1); -INSERT INTO t1 VALUES (NULL,8); -SELECT * FROM t1; -DROP TABLE t1; -# Bug 38839 -- same as above but for multi value insert -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (NULL, 1); -DELETE FROM t1 WHERE c1 = 1; -INSERT INTO t1 VALUES (2,1), (NULL, 8); -INSERT INTO t1 VALUES (NULL,9); -SELECT * FROM t1; -DROP TABLE t1; - -# -# Test changes to AUTOINC next value calculation -SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (NULL),(5),(NULL); -INSERT INTO t1 VALUES (250),(NULL); -SELECT * FROM t1; -INSERT INTO t1 VALUES (1000); -SET @@INSERT_ID=400; -INSERT INTO t1 VALUES(NULL),(NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# Test with SIGNED INT column, by inserting a 0 for the first column value -# 0 is treated in the same was NULL. -# Reset the AUTOINC session variables -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES(0); -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; -INSERT INTO t1 VALUES (-1), (NULL),(2),(NULL); -INSERT INTO t1 VALUES (250),(NULL); -SELECT * FROM t1; -SET @@INSERT_ID=400; -# Duplicate error expected here for autoinc_lock_mode != TRADITIONAL --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 VALUES(NULL),(NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# Test with SIGNED INT column -# Reset the AUTOINC session variables -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES(-1); -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; -SHOW VARIABLES LIKE "%auto_inc%"; -INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL); -INSERT INTO t1 VALUES (250),(NULL); -SELECT * FROM t1; -INSERT INTO t1 VALUES (1000); -SET @@INSERT_ID=400; -INSERT INTO t1 VALUES(NULL),(NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# Test with UNSIGNED INT column, single insert -# The sign in the value is ignored and a new column value is generated -# Reset the AUTOINC session variables -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES(-1); -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; -SHOW VARIABLES LIKE "%auto_inc%"; -INSERT INTO t1 VALUES (-2); -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (2); -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (250); -INSERT INTO t1 VALUES (NULL); -SELECT * FROM t1; -INSERT INTO t1 VALUES (1000); -SET @@INSERT_ID=400; -INSERT INTO t1 VALUES(NULL); -INSERT INTO t1 VALUES(NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# Test with UNSIGNED INT column, multi-value inserts -# The sign in the value is ignored and a new column value is generated -# Reset the AUTOINC session variables -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES(-1); -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; -SHOW VARIABLES LIKE "%auto_inc%"; -INSERT INTO t1 VALUES (-2),(NULL),(2),(NULL); -INSERT INTO t1 VALUES (250),(NULL); -SELECT * FROM t1; -INSERT INTO t1 VALUES (1000); -SET @@INSERT_ID=400; -# Duplicate error expected here for autoinc_lock_mode != TRADITIONAL --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 VALUES(NULL),(NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# -# Check for overflow handling when increment is > 1 -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -# TODO: Fix the autoinc init code -# We have to do this because of a bug in the AUTOINC init code. -INSERT INTO t1 VALUES(NULL); -INSERT INTO t1 VALUES (9223372036854775794); #-- 2^63 - 14 -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; -SHOW VARIABLES LIKE "%auto_inc%"; -# This should just fit -INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# -# Check for overflow handling when increment and offser are > 1 -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -# TODO: Fix the autoinc init code -# We have to do this because of a bug in the AUTOINC init code. -INSERT INTO t1 VALUES(NULL); -INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13 -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; -SHOW VARIABLES LIKE "%auto_inc%"; ---error ER_AUTOINC_READ_FAILED -INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# -# Check for overflow handling when increment and offset are odd numbers -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -# TODO: Fix the autoinc init code -# We have to do this because of a bug in the AUTOINC init code. -INSERT INTO t1 VALUES(NULL); -INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13 -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7; -SHOW VARIABLES LIKE "%auto_inc%"; ---error ER_AUTOINC_READ_FAILED -INSERT INTO t1 VALUES (NULL),(NULL), (NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# Check for overflow handling when increment and offset are odd numbers -# and check for large -ve numbers -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -# TODO: Fix the autoinc init code -# We have to do this because of a bug in the AUTOINC init code. -INSERT INTO t1 VALUES(NULL); -INSERT INTO t1 VALUES(-9223372036854775806); #-- -2^63 + 2 -INSERT INTO t1 VALUES(-9223372036854775807); #-- -2^63 + 1 -INSERT INTO t1 VALUES(-9223372036854775808); #-- -2^63 -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3; -SHOW VARIABLES LIKE "%auto_inc%"; -INSERT INTO t1 VALUES (NULL),(NULL), (NULL); -SELECT * FROM t1; -DROP TABLE t1; -# -# Check for overflow handling when increment and offset are very -# large numbers 2^60 -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; -# TODO: Fix the autoinc init code -# We have to do this because of a bug in the AUTOINC init code. -INSERT INTO t1 VALUES(NULL); -INSERT INTO t1 VALUES (18446744073709551610); #-- 2^64 - 2 -SELECT * FROM t1; -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; -SHOW VARIABLES LIKE "%auto_inc%"; ---error ER_WARN_DATA_OUT_OF_RANGE -INSERT INTO t1 VALUES (NULL),(NULL); -SELECT * FROM t1; -DROP TABLE t1; - -# -# Check for floating point autoinc column handling -# -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SET @@INSERT_ID=1; -SHOW VARIABLES LIKE "%auto_inc%"; -CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES(NULL, 1); -INSERT INTO t1 VALUES(NULL, 2); -SELECT * FROM t1; -ALTER TABLE t1 CHANGE c1 c1 SERIAL; -SELECT * FROM t1; -INSERT INTO t1 VALUES(NULL, 3); -INSERT INTO t1 VALUES(NULL, 4); -SELECT * FROM t1; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES(NULL, 1); -INSERT INTO t1 VALUES(NULL, 2); -SELECT * FROM t1; -ALTER TABLE t1 CHANGE c1 c1 SERIAL; -SELECT * FROM t1; -INSERT INTO t1 VALUES(NULL, 3); -INSERT INTO t1 VALUES(NULL, 4); -SELECT * FROM t1; -DROP TABLE t1; - -# -# Bug# 42714: AUTOINC column calculated next value not greater than highest -# value stored in table. -# -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5; -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; -CREATE TABLE t1 ( - a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, - b INT(10) UNSIGNED NOT NULL, - c ENUM('FALSE','TRUE') DEFAULT NULL, - PRIMARY KEY (a)) ENGINE = InnoDB; -CREATE TABLE t2 ( - m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, - n INT(10) UNSIGNED NOT NULL, - o enum('FALSE','TRUE') DEFAULT NULL, - PRIMARY KEY (m)) ENGINE = InnoDB; -INSERT INTO t2 (n,o) VALUES - (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), - (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false'); -SHOW CREATE TABLE t2; -INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; -SHOW CREATE TABLE t1; -INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; -SELECT * FROM t1; -SHOW CREATE TABLE t1; -INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; -SELECT * FROM t1; -SHOW CREATE TABLE t1; -INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; -SELECT * FROM t1; -SHOW CREATE TABLE t1; -INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; -SHOW CREATE TABLE t1; -INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; -SHOW CREATE TABLE t1; -INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; -SELECT * FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; -DROP TABLE t2; -# -# 43203: Overflow from auto incrementing causes server segv -# - -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; -CREATE TABLE t1( - c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT - PRIMARY KEY) ENGINE=InnoDB; -INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); -CREATE TABLE t2( - c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT - PRIMARY KEY) ENGINE=InnoDB; --- error ER_DUP_ENTRY,1062 -INSERT INTO t2 SELECT c1 FROM t1; --- error ER_DUP_ENTRY,1467 -INSERT INTO t2 SELECT NULL FROM t1; -DROP TABLE t1; -DROP TABLE t2; - -# If the user has specified negative values for an AUTOINC column then -# InnoDB should ignore those values when setting the table's max value. -SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; -SHOW VARIABLES LIKE "%auto_inc%"; -# TINYINT -CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-127, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-127, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; -# -# SMALLINT -# -CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-32767, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-32757, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; -# -# MEDIUMINT -# -CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-8388607, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-8388607, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; -# -# INT -# -CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-2147483647, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-2147483647, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; -# -# BIGINT -# -CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (-1, 'innodb'); -INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); -INSERT INTO t1 VALUES (NULL, NULL); -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; -# -# End negative number check - -## -# 47125: auto_increment start value is ignored if an index is created -# and engine=innodb -# -CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; -CREATE INDEX i1 on t1(c2); -SHOW CREATE TABLE t1; -INSERT INTO t1 (c2) values (0); -SELECT * FROM t1; -DROP TABLE t1; - -## -# 49032: Use the correct function to read the AUTOINC column value -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; -INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); -# Restart the server --- source include/restart_mysqld.inc -INSERT INTO t1(C2) VALUES ('innodb'); -SHOW CREATE TABLE t1; -DROP TABLE t1; -CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; -INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); -# Restart the server --- source include/restart_mysqld.inc -INSERT INTO t1(C2) VALUES ('innodb'); -SHOW CREATE TABLE t1; -DROP TABLE t1; - -## -# 47720: REPLACE INTO Autoincrement column with negative values -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; -INSERT INTO t1 SET c1 = 1; -SHOW CREATE TABLE t1; -INSERT INTO t1 SET c1 = 2; -INSERT INTO t1 SET c1 = -1; -SELECT * FROM t1; --- error ER_DUP_ENTRY,1062 -INSERT INTO t1 SET c1 = -1; -SHOW CREATE TABLE t1; -REPLACE INTO t1 VALUES (-1); -SELECT * FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -## -# 49497: Error 1467 (ER_AUTOINC_READ_FAILED) on inserting a negative value -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL); -SELECT * FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; -CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL); -SELECT * FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; -CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL); -INSERT INTO t1 VALUES (4), (5), (6), (NULL); -SELECT * FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; -CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5); -SELECT * FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; -CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL); -SELECT * FROM t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -## -# 55277: Failing assertion: auto_inc > 0 -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (18446744073709551615); -# Restart the server --- source include/restart_mysqld.inc -SHOW CREATE TABLE t1; -DROP TABLE t1; |