Applying InnoDB snashot 5.1-ss6129

Detailed revision comments:

r6051 | sunny | 2009-10-12 07:05:00 +0300 (Mon, 12 Oct 2009) | 6 lines
branches/5.1: Ignore negative values supplied by the user when calculating the
next value to store in dict_table_t. Setting autoincrement columns top negative
values is undefined behavior and this change should bring the behavior of
InnoDB closer to what users expect. Added several tests to check.
rb://162

2 files changed, 320 insertions, 0 deletions

diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result
index d2e8eb19e0c..ce96baf11c1 100644
--- a/mysql-test/r/innodb-autoinc.result
+++ b/mysql-test/r/innodb-autoinc.result
@@ -889,3 +889,218 @@ d1
 3
 4
 DROP TABLE t1;
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
+SHOW VARIABLES LIKE "%auto_inc%";
+Variable_name	Value
+auto_increment_increment	1
+auto_increment_offset	1
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` tinyint(4) NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+-127	innodb
+-1	innodb
+1	NULL
+2	NULL
+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');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (-127, 'innodb');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (NULL, NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+1	NULL
+2	innodb
+3	innodb
+4	NULL
+DROP TABLE t1;
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` smallint(6) NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+-32767	innodb
+-1	innodb
+1	NULL
+2	NULL
+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');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (-32757, 'innodb');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (NULL, NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+1	NULL
+2	innodb
+3	innodb
+4	NULL
+DROP TABLE t1;
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` mediumint(9) NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+-8388607	innodb
+-1	innodb
+1	NULL
+2	NULL
+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');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (-8388607, 'innodb');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (NULL, NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+1	NULL
+2	innodb
+3	innodb
+4	NULL
+DROP TABLE t1;
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+-2147483647	innodb
+-1	innodb
+1	NULL
+2	NULL
+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');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (-2147483647, 'innodb');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (NULL, NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+1	NULL
+2	innodb
+3	innodb
+4	NULL
+DROP TABLE t1;
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` bigint(20) NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+-9223372036854775807	innodb
+-1	innodb
+1	NULL
+2	NULL
+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');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (-9223372036854775807, 'innodb');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+INSERT INTO t1 VALUES (NULL, NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+  `c2` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1	c2
+1	NULL
+2	innodb
+3	innodb
+4	NULL
+DROP TABLE t1;
diff --git a/mysql-test/t/innodb-autoinc.test b/mysql-test/t/innodb-autoinc.test
index 61c42f45733..a4ffc64c99f 100644
--- a/mysql-test/t/innodb-autoinc.test
+++ b/mysql-test/t/innodb-autoinc.test
@@ -498,3 +498,108 @@ ALTER TABLE t1 AUTO_INCREMENT = 3;
 INSERT INTO t1 VALUES(null);
 SELECT * FROM t1;
 DROP TABLE t1;
+
+# 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