diff options
Diffstat (limited to 'storage/innobase/mysql-test/innodb-autoinc.result')
| -rw-r--r-- | storage/innobase/mysql-test/innodb-autoinc.result | 891 |
1 files changed, 891 insertions, 0 deletions
diff --git a/storage/innobase/mysql-test/innodb-autoinc.result b/storage/innobase/mysql-test/innodb-autoinc.result new file mode 100644 index 00000000000..d2e8eb19e0c --- /dev/null +++ b/storage/innobase/mysql-test/innodb-autoinc.result @@ -0,0 +1,891 @@ +drop table if exists t1; +CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (9223372036854775807, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +9223372036854775807 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (127, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +127 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (255, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +255 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (32767, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +32767 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (65535, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +65535 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (8388607, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +8388607 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (16777215, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +16777215 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2147483647, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +2147483647 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (4294967295, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +4294967295 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (9223372036854775807, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +9223372036854775807 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (18446744073709551615, null); +INSERT INTO t1 (c2) VALUES ('innodb'); +Got one of the listed errors +SELECT * FROM t1; +c1 c2 +18446744073709551615 NULL +DROP TABLE t1; +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; +c1 +1 +2 +3 +4 +5 +6 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 +TRUNCATE TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +SELECT c1 FROM t1; +c1 +1 +2 +3 +4 +5 +6 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 +DROP TABLE t1; +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; +c1 +1 +2 +3 +4 +5 +6 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 +DELETE FROM t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +SELECT c1 FROM t1; +c1 +1 +2 +3 +7 +8 +9 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table '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; +c1 c2 +2 1 +3 8 +DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table '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; +c1 c2 +2 1 +3 8 +5 9 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table '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; +c1 +5 +10 +110 +250 +310 +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +SELECT * FROM t1; +c1 +5 +10 +110 +250 +310 +400 +410 +1000 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(0); +SELECT * FROM t1; +c1 +1 +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; +c1 +-1 +1 +2 +10 +110 +250 +410 +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +Got one of the listed errors +SELECT * FROM t1; +c1 +-1 +1 +2 +10 +110 +250 +410 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(-1); +SELECT * FROM t1; +c1 +-1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL); +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +c1 +-2 +-1 +1 +2 +10 +250 +310 +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +SELECT * FROM t1; +c1 +-2 +-1 +1 +2 +10 +250 +310 +400 +410 +1000 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(-1); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t1; +c1 +1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +INSERT INTO t1 VALUES (-2); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +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; +c1 +1 +2 +10 +110 +210 +250 +310 +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +c1 +1 +2 +10 +110 +210 +250 +310 +400 +1000 +1010 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(-1); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +SELECT * FROM t1; +c1 +1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +INSERT INTO t1 VALUES (-2),(NULL),(2),(NULL); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +c1 +1 +2 +10 +110 +210 +250 +410 +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +Got one of the listed errors +SELECT * FROM t1; +c1 +1 +2 +10 +110 +210 +250 +410 +1000 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (9223372036854775794); +SELECT * FROM t1; +c1 +1 +9223372036854775794 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 2 +auto_increment_offset 10 +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +SELECT * FROM t1; +c1 +1 +9223372036854775794 +9223372036854775796 +9223372036854775798 +9223372036854775800 +9223372036854775802 +9223372036854775804 +9223372036854775806 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551603); +SELECT * FROM t1; +c1 +1 +18446744073709551603 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 2 +auto_increment_offset 10 +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +SELECT * FROM t1; +c1 +1 +18446744073709551603 +18446744073709551604 +18446744073709551606 +18446744073709551608 +18446744073709551610 +18446744073709551612 +18446744073709551614 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551603); +SELECT * FROM t1; +c1 +1 +18446744073709551603 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 5 +auto_increment_offset 7 +INSERT INTO t1 VALUES (NULL),(NULL); +SELECT * FROM t1; +c1 +1 +18446744073709551603 +18446744073709551607 +18446744073709551612 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES(-9223372036854775806); +INSERT INTO t1 VALUES(-9223372036854775807); +INSERT INTO t1 VALUES(-9223372036854775808); +SELECT * FROM t1; +c1 +-9223372036854775808 +-9223372036854775807 +-9223372036854775806 +1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 3 +auto_increment_offset 3 +INSERT INTO t1 VALUES (NULL),(NULL), (NULL); +SELECT * FROM t1; +c1 +-9223372036854775808 +-9223372036854775807 +-9223372036854775806 +1 +3 +6 +9 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551610); +SELECT * FROM t1; +c1 +1 +18446744073709551610 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; +Warnings: +Warning 1292 Truncated incorrect auto_increment_increment value: '1152921504606846976' +Warning 1292 Truncated incorrect auto_increment_offset value: '1152921504606846976' +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 65535 +auto_increment_offset 65535 +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +c1 +1 +18446744073709551610 +18446744073709551615 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +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; +c1 c2 +1 1 +2 2 +ALTER TABLE t1 CHANGE c1 c1 SERIAL; +SELECT * FROM t1; +c1 c2 +1 1 +2 2 +INSERT INTO t1 VALUES(NULL, 3); +INSERT INTO t1 VALUES(NULL, 4); +SELECT * FROM t1; +c1 c2 +1 1 +2 2 +3 3 +4 4 +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; +c1 c2 +1 1 +2 2 +ALTER TABLE t1 CHANGE c1 c1 SERIAL; +SELECT * FROM t1; +c1 c2 +1 1 +2 2 +INSERT INTO t1 VALUES(NULL, 3); +INSERT INTO t1 VALUES(NULL, 4); +SELECT * FROM t1; +c1 c2 +1 1 +2 2 +3 3 +4 4 +DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +DROP TABLE IF EXISTS t2; +Warnings: +Note 1051 Unknown table '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; +Table Create Table +t2 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 AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 +INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; +SHOW CREATE TABLE t1; +Table Create Table +t1 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 AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 +INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; +SELECT * FROM t1; +a b c +1 1 TRUE +2 1 FALSE +3 2 TRUE +4 2 FALSE +5 3 TRUE +6 3 FALSE +7 4 TRUE +8 4 FALSE +9 5 TRUE +10 5 FALSE +13 1 TRUE +14 1 FALSE +15 2 TRUE +16 2 FALSE +17 3 TRUE +18 3 FALSE +19 4 TRUE +20 4 FALSE +21 5 TRUE +22 5 FALSE +SHOW CREATE TABLE t1; +Table Create Table +t1 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 AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SELECT * FROM t1; +a b c +1 1 TRUE +2 1 FALSE +3 2 TRUE +4 2 FALSE +5 3 TRUE +6 3 FALSE +7 4 TRUE +8 4 FALSE +9 5 TRUE +10 5 FALSE +13 1 TRUE +14 1 FALSE +15 2 TRUE +16 2 FALSE +17 3 TRUE +18 3 FALSE +19 4 TRUE +20 4 FALSE +21 5 TRUE +22 5 FALSE +23 1 FALSE +24 2 FALSE +25 3 FALSE +26 4 FALSE +27 5 FALSE +SHOW CREATE TABLE t1; +Table Create Table +t1 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 AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SELECT * FROM t1; +a b c +1 1 TRUE +2 1 FALSE +3 2 TRUE +4 2 FALSE +5 3 TRUE +6 3 FALSE +7 4 TRUE +8 4 FALSE +9 5 TRUE +10 5 FALSE +13 1 TRUE +14 1 FALSE +15 2 TRUE +16 2 FALSE +17 3 TRUE +18 3 FALSE +19 4 TRUE +20 4 FALSE +21 5 TRUE +22 5 FALSE +23 1 FALSE +24 2 FALSE +25 3 FALSE +26 4 FALSE +27 5 FALSE +30 1 FALSE +31 2 FALSE +32 3 FALSE +33 4 FALSE +34 5 FALSE +SHOW CREATE TABLE t1; +Table Create Table +t1 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 AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SHOW CREATE TABLE t1; +Table Create Table +t1 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 AUTO_INCREMENT=44 DEFAULT CHARSET=latin1 +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SHOW CREATE TABLE t1; +Table Create Table +t1 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 AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SELECT * FROM t1; +a b c +1 1 TRUE +2 1 FALSE +3 2 TRUE +4 2 FALSE +5 3 TRUE +6 3 FALSE +7 4 TRUE +8 4 FALSE +9 5 TRUE +10 5 FALSE +13 1 TRUE +14 1 FALSE +15 2 TRUE +16 2 FALSE +17 3 TRUE +18 3 FALSE +19 4 TRUE +20 4 FALSE +21 5 TRUE +22 5 FALSE +23 1 FALSE +24 2 FALSE +25 3 FALSE +26 4 FALSE +27 5 FALSE +30 1 FALSE +31 2 FALSE +32 3 FALSE +33 4 FALSE +34 5 FALSE +37 1 FALSE +38 2 FALSE +39 3 FALSE +40 4 FALSE +41 5 FALSE +44 1 FALSE +45 2 FALSE +46 3 FALSE +47 4 FALSE +48 5 FALSE +51 1 FALSE +52 2 FALSE +53 3 FALSE +54 4 FALSE +55 5 FALSE +SHOW CREATE TABLE t1; +Table Create Table +t1 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 AUTO_INCREMENT=58 DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +DROP TABLE IF EXISTS t2; +Warnings: +Note 1051 Unknown table '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; +INSERT INTO t2 SELECT c1 FROM t1; +Got one of the listed errors +INSERT INTO t2 SELECT NULL FROM t1; +Got one of the listed errors +DROP TABLE t1; +DROP TABLE t2; +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (null); +INSERT INTO t1 VALUES (null); +ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT; +SELECT * FROM t1; +d1 +1 +3 +SELECT * FROM t1; +d1 +1 +3 +INSERT INTO t1 VALUES(null); +Got one of the listed errors +ALTER TABLE t1 AUTO_INCREMENT = 3; +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +d1 +1 +3 +4 +DROP TABLE t1; |