summaryrefslogtreecommitdiff
path: root/mysql-test/r/innodb-autoinc.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/innodb-autoinc.result')
-rw-r--r--mysql-test/r/innodb-autoinc.result288
1 files changed, 288 insertions, 0 deletions
diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result
index 1e4b088c6cd..ade4db35ce6 100644
--- a/mysql-test/r/innodb-autoinc.result
+++ b/mysql-test/r/innodb-autoinc.result
@@ -579,3 +579,291 @@ c1
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;
View Lorry Controller Status