Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 691 insertions, 0 deletions

diff --git a/mysql-test/main/auto_increment.result b/mysql-test/main/auto_increment.result
new file mode 100644
index 00000000..a1c7d500
--- /dev/null
+++ b/mysql-test/main/auto_increment.result
@@ -0,0 +1,691 @@
+drop table if exists t1;
+drop table if exists t2;
+SET SQL_WARNINGS=1;
+create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
+insert into t1 values (1,1),(NULL,3),(NULL,4);
+delete from t1 where a=4;
+insert into t1 values (NULL,5),(NULL,6);
+select * from t1;
+a	b
+1	1
+3	3
+5	5
+6	6
+delete from t1 where a=6;
+replace t1 values (3,1);
+ALTER TABLE t1 add c int;
+replace t1 values (3,3,3);
+insert into t1 values (NULL,7,7);
+update t1 set a=8,b=b+1,c=c+1 where a=7;
+insert into t1 values (NULL,9,9);
+select * from t1;
+a	b	c
+1	1	NULL
+3	3	3
+5	5	NULL
+8	8	8
+9	9	9
+drop table t1;
+create table t1 (
+skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
+sval char(20)
+);
+insert into t1 values (NULL, "hello");
+insert into t1 values (NULL, "hey");
+select * from t1;
+skey	sval
+1	hello
+2	hey
+select _rowid,t1._rowid,skey,sval from t1;
+_rowid	_rowid	skey	sval
+1	1	1	hello
+2	2	2	hey
+drop table t1;
+create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b));
+insert into t1 values ("a",1),("b",2),("a",2),("c",1);
+insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
+insert into t1 (a) values ("a"),("b"),("c"),("d");
+insert into t1 (a) values ('k'),('d');
+insert into t1 (a) values ("a");
+insert into t1 values ("d",last_insert_id());
+select * from t1;
+a	b
+a	1
+a	2
+a	3
+a	4
+a	5
+b	2
+b	3
+b	4
+c	1
+c	2
+c	3
+d	1
+d	2
+d	5
+e	1
+k	1
+drop table t1;
+create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ordid), index(ord,ordid));
+insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
+select * from t1;
+ordid	ord
+1	sdj
+2	sdj
+drop table t1;
+create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid));
+insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
+select * from t1;
+ordid	ord
+1	abc
+2	abc
+3	abc
+1	sdj
+2	sdj
+3	sdj
+1	zzz
+drop table t1;
+create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid,  id));
+create table t2 (sid char(20), id int(2));
+insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
+insert into t1 select * from t2;
+select * from t1;
+sid	id
+skr	1
+skr	2
+test	1
+drop table t1,t2;
+create table t1 (a int not null primary key auto_increment);
+insert into t1 values (0);
+update t1 set a=0;
+select * from t1;
+a
+0
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	warning	Found row where the auto_increment column has the value 0
+test.t1	check	status	OK
+drop table t1;
+create table t1 (a int not null auto_increment primary key);
+insert into t1 values (NULL);
+insert into t1 values (-1);
+select last_insert_id();
+last_insert_id()
+1
+insert into t1 values (NULL);
+select * from t1;
+a
+-1
+1
+2
+drop table t1;
+create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
+insert into t1 values (NULL);
+insert into t1 values (-1);
+select last_insert_id();
+last_insert_id()
+1
+insert into t1 values (NULL);
+select * from t1;
+a
+1
+-1
+2
+drop table t1;
+create table t1 (i tinyint unsigned not null auto_increment primary key);
+insert into t1 set i = 254;
+insert into t1 set i = null;
+select last_insert_id();
+last_insert_id()
+255
+explain extended select last_insert_id();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select last_insert_id() AS `last_insert_id()`
+insert into t1 set i = 254;
+ERROR 23000: Duplicate entry '254' for key 'PRIMARY'
+select last_insert_id();
+last_insert_id()
+255
+insert into t1 set i = null;
+ERROR 22003: Out of range value for column 'i' at row 1
+select last_insert_id();
+last_insert_id()
+255
+drop table t1;
+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();
+last_insert_id()
+255
+insert into t1 set i = null;
+ERROR 22003: Out of range value for column 'i' at row 1
+select last_insert_id();
+last_insert_id()
+255
+drop table t1;
+create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
+insert into t1 values (NULL, 10);
+select last_insert_id();
+last_insert_id()
+1
+insert into t1 values (NULL, 15);
+select last_insert_id();
+last_insert_id()
+2
+insert into t1 values (NULL, 10);
+ERROR 23000: Duplicate entry '10' for key 'b'
+select last_insert_id();
+last_insert_id()
+2
+drop table t1;
+create table t1(a int auto_increment,b int null,primary key(a));
+SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
+insert into t1(a,b)values(NULL,1);
+insert into t1(a,b)values(200,2);
+insert into t1(a,b)values(0,3);
+insert into t1(b)values(4);
+insert into t1(b)values(5);
+insert into t1(b)values(6);
+insert into t1(b)values(7);
+select * from t1 order by b;
+a	b
+1	1
+200	2
+0	3
+201	4
+202	5
+203	6
+204	7
+alter table t1 modify b mediumint;
+select * from t1 order by b;
+a	b
+1	1
+200	2
+0	3
+201	4
+202	5
+203	6
+204	7
+create table t2 (a int);
+insert t2 values (1),(2);
+alter table t2 add b int auto_increment primary key;
+select * from t2;
+a	b
+1	1
+2	2
+drop table t2;
+delete from t1 where a=0;
+update t1 set a=0 where b=5;
+select * from t1 order by b;
+a	b
+1	1
+200	2
+201	4
+0	5
+203	6
+204	7
+delete from t1 where a=0;
+update t1 set a=NULL where b=6;
+Warnings:
+Warning	1048	Column 'a' cannot be null
+update t1 set a=300 where b=7;
+SET SQL_MODE='';
+insert into t1(a,b)values(NULL,8);
+insert into t1(a,b)values(400,9);
+insert into t1(a,b)values(0,10);
+insert into t1(b)values(11);
+insert into t1(b)values(12);
+insert into t1(b)values(13);
+insert into t1(b)values(14);
+select * from t1 order by b;
+a	b
+1	1
+200	2
+201	4
+0	6
+300	7
+301	8
+400	9
+401	10
+402	11
+403	12
+404	13
+405	14
+delete from t1 where a=0;
+update t1 set a=0 where b=12;
+select * from t1 order by b;
+a	b
+1	1
+200	2
+201	4
+300	7
+301	8
+400	9
+401	10
+402	11
+0	12
+404	13
+405	14
+delete from t1 where a=0;
+update t1 set a=NULL where b=13;
+Warnings:
+Warning	1048	Column 'a' cannot be null
+update t1 set a=500 where b=14;
+select * from t1 order by b;
+a	b
+1	1
+200	2
+201	4
+300	7
+301	8
+400	9
+401	10
+402	11
+0	13
+500	14
+drop table t1;
+create table t1 (a bigint);
+insert into t1 values (1), (2), (3), (NULL), (NULL);
+alter table t1 modify a bigint not null auto_increment primary key;
+select * from t1;
+a
+1
+2
+3
+4
+5
+drop table t1;
+create table t1 (a bigint);
+insert into t1 values (1), (2), (3), (0), (0);
+alter table t1 modify a bigint not null auto_increment primary key;
+select * from t1;
+a
+1
+2
+3
+4
+5
+drop table t1;
+create table t1 (a bigint);
+insert into t1 values (0), (1), (2), (3);
+set sql_mode=NO_AUTO_VALUE_ON_ZERO;
+alter table t1 modify a bigint not null auto_increment primary key;
+set sql_mode= '';
+select * from t1;
+a
+0
+1
+2
+3
+drop table t1;
+create table t1 (a int auto_increment primary key , b int null);
+set sql_mode=NO_AUTO_VALUE_ON_ZERO;
+insert into t1 values (0,1),(1,2),(2,3);
+select * from t1;
+a	b
+0	1
+1	2
+2	3
+set sql_mode= '';
+alter table t1 modify b varchar(255);
+insert into t1 values (0,4);
+select * from t1;
+a	b
+0	1
+1	2
+2	3
+3	4
+drop table t1;
+CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
+INSERT INTO t1 (b) VALUES ('aaaa');
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+INSERT INTO t1 (b) VALUES ('');
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+INSERT INTO t1 (b) VALUES ('bbbb');
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE IF EXISTS t1;
+CREATE TABLE `t1` (
+t1_name VARCHAR(255) DEFAULT NULL,
+t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+KEY (t1_name),
+PRIMARY KEY (t1_id)
+) AUTO_INCREMENT = 1000;
+INSERT INTO t1 (t1_name) VALUES('MySQL');
+INSERT INTO t1 (t1_name) VALUES('MySQL');
+INSERT INTO t1 (t1_name) VALUES('MySQL');
+SELECT * from t1;
+t1_name	t1_id
+MySQL	1000
+MySQL	1001
+MySQL	1002
+SHOW CREATE TABLE `t1`;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t1_name` varchar(255) DEFAULT NULL,
+  `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`t1_id`),
+  KEY `t1_name` (`t1_name`)
+) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE `t1`;
+create table t1(a int not null auto_increment primary key);
+create table t2(a int not null auto_increment primary key, t1a int);
+insert into t1 values(NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
+insert into t1 values (NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID());
+insert into t1 values (NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
+select * from t2;
+a	t1a
+1	1
+2	1
+3	2
+4	2
+5	2
+6	3
+7	3
+8	3
+9	3
+drop table t1, t2;
+End of 4.1 tests
+CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`));
+insert into t1 (b) values (1);
+replace into t1 (b) values (2), (1), (3);
+select * from t1;
+a	b
+3	1
+2	2
+4	3
+truncate table t1;
+insert into t1 (b) values (1);
+replace into t1 (b) values (2);
+replace into t1 (b) values (1);
+replace into t1 (b) values (3);
+select * from t1;
+a	b
+3	1
+2	2
+4	3
+drop table t1;
+create table t1 (rowid int not null auto_increment, val int not null,primary
+key (rowid), unique(val));
+replace into t1 (val) values ('1'),('2');
+replace into t1 (val) values ('1'),('2');
+insert into t1 (val) values ('1'),('2');
+ERROR 23000: Duplicate entry '1' for key 'val'
+select * from t1;
+rowid	val
+3	1
+4	2
+drop table t1;
+create table t1 (a int not null auto_increment primary key, val int);
+insert into t1 (val) values (1);
+update t1 set a=2 where a=1;
+insert into t1 (val) values (1);
+select * from t1;
+a	val
+2	1
+3	1
+drop table t1;
+CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
+INSERT INTO t1 VALUES(0, 0);
+INSERT INTO t1 VALUES(1, 1);
+ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
+ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
+DROP TABLE t1;
+create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
+insert into t1 values(null,1,1,now());
+insert into t1 values(null,0,0,null);
+replace into t1 values(null,1,0,null);
+select last_insert_id();
+last_insert_id()
+3
+drop table t1;
+create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e));
+insert into t1 values(null,1,1,1,now());
+insert into t1 values(null,0,0,0,null);
+replace into t1 values(null,1,0,2,null);
+select last_insert_id();
+last_insert_id()
+3
+drop table t1;
+#
+# Bug#46616: Assertion `!table->auto_increment_field_not_null' on view   
+# manipulations
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (1);
+CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
+INSERT INTO t2 SELECT a FROM t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+UPDATE t2 SET a = 2;
+SELECT a FROM t2;
+a
+2
+DROP TABLE t1, t2;
+#
+# Bug#39828 autoinc wraps around when offset and increment > 1
+#
+CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engine=MyISAM;
+INSERT INTO t1 VALUES(1);
+INSERT INTO t1 VALUES (18446744073709551601);
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=10;
+SELECT @@SESSION.AUTO_INCREMENT_OFFSET;
+@@SESSION.AUTO_INCREMENT_OFFSET
+1
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+ERROR 22003: Out of range value for column 'c1' at row 2
+SELECT * FROM t1;
+c1
+1
+18446744073709551601
+18446744073709551611
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=default;
+SET @@SESSION.AUTO_INCREMENT_OFFSET=default;
+DROP TABLE t1;
+End of 5.1 tests
+#
+# Bug#50619 assert in handler::update_auto_increment
+#
+CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk));
+INSERT INTO t1 VALUES (NULL), (-1), (NULL);
+SELECT * FROM t1;
+pk
+-1
+1
+2
+DROP TABLE t1;
+CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk));
+INSERT INTO t1 VALUES (NULL), (18446744073709551615-1), (NULL);
+ERROR HY000: Failed to read auto-increment value from storage engine
+SELECT * FROM t1;
+pk
+1
+18446744073709551614
+DROP TABLE t1;
+CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk));
+insert into t1 values((1<<63)+1);
+insert into t1 values(null);
+select last_insert_id();
+last_insert_id()
+9223372036854775810
+select * from t1;
+pk
+9223372036854775809
+9223372036854775810
+drop table t1;
+CREATE TABLE t1 (pk BIGINT AUTO_INCREMENT, PRIMARY KEY (pk));
+insert into t1 values(-5);
+insert into t1 values(null);
+select last_insert_id();
+last_insert_id()
+1
+select * from t1;
+pk
+-5
+1
+drop table t1;
+#
+# End of 5.3 tests
+#
+#
+# MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA
+#
+CREATE TABLE t1 (
+id TINYINT NOT NULL AUTO_INCREMENT,
+name CHAR(30) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=MyISAM;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
+  `name` char(30) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 (name) VALUES ('dog');
+UPDATE t1 SET id=-1 WHERE id=1;
+INSERT INTO t1 (name) VALUES ('cat');
+SELECT * FROM t1;
+id	name
+-1	dog
+2	cat
+DROP TABLE t1;
+#
+# End of 5.5 tests
+#
+#
+# MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number
+#
+SET @engine='MyISAM';
+CREATE PROCEDURE autoinc_mdev15353_one(engine VARCHAR(64), t VARCHAR(64))
+BEGIN
+DECLARE query TEXT DEFAULT 'CREATE TABLE t1 ('
+                             '  id TTT NOT NULL AUTO_INCREMENT,'
+                             '  name CHAR(30) NOT NULL,'
+                             '  PRIMARY KEY (id)) ENGINE=EEE';
+EXECUTE IMMEDIATE REPLACE(REPLACE(query,'TTT', t), 'EEE', engine);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (name) VALUES ('dog');
+SELECT * FROM t1;
+UPDATE t1 SET id=-1 WHERE id=1;
+SELECT * FROM t1;
+INSERT INTO t1 (name) VALUES ('cat');
+SELECT * FROM t1;
+DROP TABLE t1;
+END;
+$$
+CALL autoinc_mdev15353_one(@engine, 'tinyint');
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
+  `name` char(30) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id	name
+1	dog
+id	name
+-1	dog
+id	name
+-1	dog
+2	cat
+CALL autoinc_mdev15353_one(@engine, 'smallint');
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` smallint(6) NOT NULL AUTO_INCREMENT,
+  `name` char(30) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id	name
+1	dog
+id	name
+-1	dog
+id	name
+-1	dog
+2	cat
+CALL autoinc_mdev15353_one(@engine, 'mediumint');
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
+  `name` char(30) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id	name
+1	dog
+id	name
+-1	dog
+id	name
+-1	dog
+2	cat
+CALL autoinc_mdev15353_one(@engine, 'int');
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` char(30) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id	name
+1	dog
+id	name
+-1	dog
+id	name
+-1	dog
+2	cat
+CALL autoinc_mdev15353_one(@engine, 'bigint');
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `name` char(30) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id	name
+1	dog
+id	name
+-1	dog
+id	name
+-1	dog
+2	cat
+CALL autoinc_mdev15353_one(@engine, 'float');
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` float NOT NULL AUTO_INCREMENT,
+  `name` char(30) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id	name
+1	dog
+id	name
+-1	dog
+id	name
+-1	dog
+2	cat
+CALL autoinc_mdev15353_one(@engine, 'double');
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` double NOT NULL AUTO_INCREMENT,
+  `name` char(30) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+id	name
+1	dog
+id	name
+-1	dog
+id	name
+-1	dog
+2	cat
+DROP PROCEDURE autoinc_mdev15353_one;
+#
+# End of 10.2 tests
+#