Merge 10.7 into 10.8

10 files changed, 69 insertions, 90 deletions

diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result
index 31fa5f8c8d5..4c2fbcf1dd6 100644
--- a/mysql-test/main/long_unique_bugs.result
+++ b/mysql-test/main/long_unique_bugs.result
@@ -317,6 +317,15 @@ ERROR 23000: Duplicate entry '1' for key 'v2'
 update t1,t2 set v1 = v2 , v5 = 0;
 ERROR 23000: Duplicate entry '-128' for key 'v1'
 drop table t1, t2;
+CREATE TABLE t1 (f TEXT UNIQUE);
+INSERT INTO t1 VALUES (NULL),(NULL);
+UPDATE t1 SET f = '';
+ERROR 23000: Duplicate entry '' for key 'f'
+SELECT * FROM t1;
+f
+
+NULL
+DROP TABLE t1;
 #
 # MDEV-21540 Initialization of already inited long unique index on reorganize partition
 #
diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test
index 18e430858b0..8d1accd6cd3 100644
--- a/mysql-test/main/long_unique_bugs.test
+++ b/mysql-test/main/long_unique_bugs.test
@@ -397,6 +397,17 @@ update t1 set v2 = 1, v3 = -128;
 update t1,t2 set v1 = v2 , v5 = 0;
 drop table t1, t2;
 
+#
+# MDEV-23264 Unique blobs allow duplicate values upon UPDATE
+#
+
+CREATE TABLE t1 (f TEXT UNIQUE);
+INSERT INTO t1 VALUES (NULL),(NULL);
+--error ER_DUP_ENTRY
+UPDATE t1 SET f = '';
+SELECT * FROM t1;
+DROP TABLE t1;
+
 --echo #
 --echo # MDEV-21540 Initialization of already inited long unique index on reorganize partition
 --echo #
diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result
index e1c4591f743..dc64892ec44 100644
--- a/mysql-test/main/mysql_upgrade.result
+++ b/mysql-test/main/mysql_upgrade.result
@@ -1320,10 +1320,6 @@ partition p2008 values less than (2009)
 );
 select length(table_name) from mysql.innodb_table_stats;
 length(table_name)
-79
-79
-79
-79
 drop table extralongname_extralongname_extralongname_extralongname_ext;
 # End of 10.0 tests
 set sql_mode=default;
diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result
index 830377f35a3..49f05f5e6ae 100644
--- a/mysql-test/main/order_by_innodb.result
+++ b/mysql-test/main/order_by_innodb.result
@@ -1,8 +1,8 @@
-drop table if exists t0,t1,t2,t3;
 #
 # MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB
 #
-CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB;
+CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB
+STATS_PERSISTENT=0;
 INSERT INTO t1 (a,c) VALUES
 (8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21),
 (20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28);
@@ -14,8 +14,6 @@ DROP TABLE t1;
 #
 # MDEV-9457: Poor query plan chosen for ORDER BY query by a recent 10.1
 #
-create table t0 (a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (
 pk int primary key,
 key1 int,
@@ -23,15 +21,9 @@ key2 int,
 col1 char(255),
 key(key1),
 key(key2)
-) engine=innodb;
-set @a=-1;
+) engine=innodb stats_persistent=0;
 insert into t1 
-select 
-@a:=@a+1, 
-@a, 
-@a, 
-repeat('abcd', 63) 
-from t0 A, t0 B, t0 C, t0 D;
+select seq,seq,seq,repeat('abcd', 63) from seq_0_to_9999;
 # The following must NOT use 'index' on PK. 
 # It should use index_merge(key1,key2) + filesort
 explain 
@@ -47,7 +39,7 @@ from t1
 where key1<3 or key2<3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	#	Using sort_union(key1,key2); Using where
-drop table t0, t1;
+drop table t1;
 #
 # MDEV-18094: Query with order by limit picking index scan over filesort
 #
@@ -78,9 +70,12 @@ drop table t1,t0;
 # MDEV-14071: wrong results with orderby_uses_equalities=on
 # (duplicate of MDEV-13994)
 #
-CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB;
-CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB;
-CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB;
+CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB
+STATS_PERSISTENT=0;
+CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB
+STATS_PERSISTENT=0;
+CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB
+STATS_PERSISTENT=0;
 INSERT INTO t1 VALUES
 (127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1),
 (381,0,1),(409,0,1),(466,0,1),(469,0,1),(498,0,1),(656,0,1);
@@ -150,7 +145,8 @@ DROP TABLE t1,t2,t3;
 #
 # MDEV-25858: Query results are incorrect when indexes are added
 #
-CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb;
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb
+STATS_PERSISTENT=0;
 insert into t1 values (1),(2),(3);
 CREATE TABLE t2 (
 id int NOT NULL PRIMARY KEY,
diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test
index 19bd1e3007a..3ea68edfa3f 100644
--- a/mysql-test/main/order_by_innodb.test
+++ b/mysql-test/main/order_by_innodb.test
@@ -1,18 +1,15 @@
 #
 # ORDER BY handling (e.g. filesort) tests that require innodb
 #
---source include/have_innodb.inc
---source include/have_sequence.inc
-
---disable_warnings
-drop table if exists t0,t1,t2,t3;
---enable_warnings
+-- source include/have_innodb.inc
+-- source include/have_sequence.inc
 
 --echo #
 --echo # MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB
 --echo #
 
-CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB;
+CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB
+STATS_PERSISTENT=0;
 
 INSERT INTO t1 (a,c) VALUES
 (8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21),
@@ -25,9 +22,6 @@ DROP TABLE t1;
 --echo #
 --echo # MDEV-9457: Poor query plan chosen for ORDER BY query by a recent 10.1
 --echo #
-create table t0 (a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-
 create table t1 (
   pk int primary key,
   key1 int,
@@ -35,16 +29,10 @@ create table t1 (
   col1 char(255),
   key(key1),
   key(key2)
-) engine=innodb;
+) engine=innodb stats_persistent=0;
 
-set @a=-1;
 insert into t1 
-select 
-  @a:=@a+1, 
-  @a, 
-  @a, 
-  repeat('abcd', 63) 
-from t0 A, t0 B, t0 C, t0 D;
+select seq,seq,seq,repeat('abcd', 63) from seq_0_to_9999;
 
 --echo # The following must NOT use 'index' on PK. 
 --echo # It should use index_merge(key1,key2) + filesort
@@ -61,7 +49,7 @@ select *
 from t1
 where key1<3 or key2<3;
 
-drop table t0, t1;
+drop table t1;
 
 --echo #
 --echo # MDEV-18094: Query with order by limit picking index scan over filesort
@@ -94,9 +82,12 @@ drop table t1,t0;
 --echo # (duplicate of MDEV-13994)
 --echo #
 
-CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB;
-CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB;
-CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB;
+CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB
+STATS_PERSISTENT=0;
+CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB
+STATS_PERSISTENT=0;
+CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB
+STATS_PERSISTENT=0;
 
 INSERT INTO t1 VALUES
   (127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1),
@@ -140,7 +131,8 @@ DROP TABLE t1,t2,t3;
 --echo # MDEV-25858: Query results are incorrect when indexes are added
 --echo #
 
-CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb;
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb
+STATS_PERSISTENT=0;
 insert into t1 values (1),(2),(3);
 
 CREATE TABLE t2 (
diff --git a/mysql-test/main/range_innodb.result b/mysql-test/main/range_innodb.result
index 6cd1c133b93..eddfbfd0d62 100644
--- a/mysql-test/main/range_innodb.result
+++ b/mysql-test/main/range_innodb.result
@@ -1,15 +1,11 @@
 #
 # Range optimizer (and related) tests that need InnoDB.
 # 
-drop table if exists t0, t1, t2;
 #
 # MDEV-6735: Range checked for each record used with key
 #
 create table t0(a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t1(a int);
-insert into t1 select A.a + B.a* 10 + C.a * 100 + D.a * 1000
-from t0 A, t0 B, t0 C, t0 D;
 create table t2 ( 
 a int,
 b int,
@@ -22,12 +18,12 @@ key(b)
 ) engine=innodb;
 insert into t2 
 select 
-a,a,
+seq,seq,
 repeat('0123456789', 10), 
 repeat('0123456789', 10), 
 repeat('0123456789', 10),
 repeat('0123456789', 10)
-from t1;
+from seq_0_to_9999;
 analyze table t2;
 Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	Engine-independent statistics collected
@@ -37,7 +33,7 @@ explain select * from t0 left join t2 on t2.a <t0.a and t2.b between 50 and 250;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	
 1	SIMPLE	t2	range	a,b	b	5	NULL	201	Using where; Using join buffer (flat, BNL join)
-drop table t0,t1,t2;
+drop table t0,t2;
 #
 # MDEV-10466: constructing an invalid SEL_ARG
 #
@@ -89,15 +85,14 @@ drop table t1,t2;
 #
 set @optimizer_switch_save= @@optimizer_switch;
 set optimizer_switch='index_merge_sort_intersection=off';
-create table t0 (a int)engine=innodb;
+create table t0(a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (
 a int, b int, c int,
 key(a),key(b),key(c)
 )engine=innodb;
 insert into t1
-select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a
-from t0 A, t0 B, t0 C, t0 D where D.a<5;
+select a.seq/10, a.seq/10, a.seq from seq_0_to_499 a, seq_0_to_4 b;
 SET @saved_dbug = @@GLOBAL.debug_dbug;
 set @@global.debug_dbug="+d,ha_index_init_fail";
 explain select * from t1 where a=10 and b=10;
diff --git a/mysql-test/main/range_innodb.test b/mysql-test/main/range_innodb.test
index b6dea2fbe06..276b9cea08f 100644
--- a/mysql-test/main/range_innodb.test
+++ b/mysql-test/main/range_innodb.test
@@ -4,12 +4,9 @@
 
 --source include/have_innodb.inc
 --source include/have_debug.inc
+--source include/have_sequence.inc
 --source include/no_valgrind_without_big.inc
 
---disable_warnings
-drop table if exists t0, t1, t2;
---enable_warnings
-
 --echo #
 --echo # MDEV-6735: Range checked for each record used with key
 --echo #
@@ -17,10 +14,6 @@ drop table if exists t0, t1, t2;
 create table t0(a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
-create table t1(a int);
-insert into t1 select A.a + B.a* 10 + C.a * 100 + D.a * 1000
-from t0 A, t0 B, t0 C, t0 D;
-
 create table t2 ( 
  a int,
  b int,
@@ -34,18 +27,18 @@ create table t2 (
 
 insert into t2 
 select 
-   a,a,
+   seq,seq,
   repeat('0123456789', 10), 
   repeat('0123456789', 10), 
   repeat('0123456789', 10),
   repeat('0123456789', 10)
-from t1;
+from seq_0_to_9999;
 
 analyze table t2;
 --echo # The following must not use "Range checked for each record":
 explain select * from t0 left join t2 on t2.a <t0.a and t2.b between 50 and 250;
 
-drop table t0,t1,t2;
+drop table t0,t2;
 
 
 --echo #
@@ -98,15 +91,14 @@ drop table t1,t2;
 
 set @optimizer_switch_save= @@optimizer_switch;
 set optimizer_switch='index_merge_sort_intersection=off';
-create table t0 (a int)engine=innodb;
+create table t0(a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (
   a int, b int, c int,
   key(a),key(b),key(c)
 )engine=innodb;
 insert into t1
-select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a
-from t0 A, t0 B, t0 C, t0 D where D.a<5;
+select a.seq/10, a.seq/10, a.seq from seq_0_to_499 a, seq_0_to_4 b;
 SET @saved_dbug = @@GLOBAL.debug_dbug;
 set @@global.debug_dbug="+d,ha_index_init_fail";
 explain select * from t1 where a=10 and b=10;
@@ -122,8 +114,6 @@ set @@optimizer_switch= @optimizer_switch_save;
 --echo # MDEV-27262: Index intersection with full scan over an index
 --echo #
 
---source include/have_sequence.inc
-
 CREATE TABLE t1 (
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
   p char(32) DEFAULT NULL,
diff --git a/mysql-test/main/rowid_filter_innodb_debug.result b/mysql-test/main/rowid_filter_innodb_debug.result
index 56226fe25ce..5a3fa374bd1 100644
--- a/mysql-test/main/rowid_filter_innodb_debug.result
+++ b/mysql-test/main/rowid_filter_innodb_debug.result
@@ -2,10 +2,8 @@ set default_storage_engine=innodb;
 #
 # MDEV-22761 KILL QUERY during rowid_filter, crashes
 #
-create table t0(a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t2(a int);
-insert into t2 select A.a + B.a* 10 from t0 A, t0 B;
+insert into t2 select * from seq_0_to_99;
 CREATE TABLE t3 (
 key1 int ,
 key2 int,
@@ -19,11 +17,10 @@ engine
 InnoDB
 insert into t3
 select
-A.a,
-B.a,
+A.seq,
+B.seq,
 'filler-data-filler-data'
-from
-t2 A, t2 B;
+from seq_0_to_99 A, seq_0_to_99 B;
 analyze table t2,t3;
 Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	Engine-independent statistics collected
@@ -42,7 +39,6 @@ select * from t2, t3
 where 
 t3.key1=t2.a and t3.key2 in (2,3);
 connect  con1, localhost, root,,;
-connection con1;
 set debug_sync='now WAIT_FOR at_rowid_filter_check';
 kill query $target_id;
 set debug_sync='now SIGNAL go';
@@ -50,7 +46,7 @@ connection default;
 disconnect con1;
 ERROR 70100: Query execution was interrupted
 set debug_sync='RESET';
-drop table t0,t2,t3;
+drop table t2,t3;
 set default_storage_engine=default;
 set @save_optimizer_switch= @@optimizer_switch;
 set @save_use_stat_tables= @@use_stat_tables;
@@ -67,7 +63,6 @@ INSERT INTO t1 VALUES (0,0),(1,0),(-1,1), (-2,1), (-2,3), (-3,4), (-2,4);
 set debug_sync='handler_rowid_filter_check SIGNAL killme WAIT_FOR go';
 SELECT * FROM t1 WHERE a > 0 AND b=0;
 connect  con1, localhost, root,,;
-connection con1;
 set debug_sync='now WAIT_FOR killme';
 kill query @id;
 set debug_sync='now SIGNAL go';
diff --git a/mysql-test/main/rowid_filter_innodb_debug.test b/mysql-test/main/rowid_filter_innodb_debug.test
index 31fbd937304..f89a2a82da8 100644
--- a/mysql-test/main/rowid_filter_innodb_debug.test
+++ b/mysql-test/main/rowid_filter_innodb_debug.test
@@ -31,7 +31,6 @@ set debug_sync='handler_rowid_filter_check SIGNAL killme WAIT_FOR go';
 send SELECT * FROM t1 WHERE a > 0 AND b=0;
 
 connect (con1, localhost, root,,);
-connection con1;
 let $ignore= `SELECT @id := $ID`;
 set debug_sync='now WAIT_FOR killme';
 kill query @id;
diff --git a/mysql-test/main/rowid_filter_myisam_debug.result b/mysql-test/main/rowid_filter_myisam_debug.result
index 32a989f50da..75a8fad6947 100644
--- a/mysql-test/main/rowid_filter_myisam_debug.result
+++ b/mysql-test/main/rowid_filter_myisam_debug.result
@@ -1,10 +1,8 @@
 #
 # MDEV-22761 KILL QUERY during rowid_filter, crashes
 #
-create table t0(a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t2(a int);
-insert into t2 select A.a + B.a* 10 from t0 A, t0 B;
+insert into t2 select * from seq_0_to_99;
 CREATE TABLE t3 (
 key1 int ,
 key2 int,
@@ -18,11 +16,10 @@ engine
 MyISAM
 insert into t3
 select
-A.a,
-B.a,
+A.seq,
+B.seq,
 'filler-data-filler-data'
-from
-t2 A, t2 B;
+from seq_0_to_99 A, seq_0_to_99 B;
 analyze table t2,t3;
 Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	Engine-independent statistics collected
@@ -41,7 +38,6 @@ select * from t2, t3
 where 
 t3.key1=t2.a and t3.key2 in (2,3);
 connect  con1, localhost, root,,;
-connection con1;
 set debug_sync='now WAIT_FOR at_rowid_filter_check';
 kill query $target_id;
 set debug_sync='now SIGNAL go';
@@ -49,4 +45,4 @@ connection default;
 disconnect con1;
 ERROR 70100: Query execution was interrupted
 set debug_sync='RESET';
-drop table t0,t2,t3;
+drop table t2,t3;