Merge 10.6 into 10.8

11 files changed, 216 insertions, 14 deletions

diff --git a/mysql-test/main/debug_sync.test b/mysql-test/main/debug_sync.test
index 1dc3dc9c71e..6e75ba9624c 100644
--- a/mysql-test/main/debug_sync.test
+++ b/mysql-test/main/debug_sync.test
@@ -231,15 +231,12 @@ SHOW VARIABLES LIKE 'DEBUG_SYNC';
 #       immediately after setting of the DEBUG_SYNC variable.
 #       So it is executed before the SET statement ends.
 #
-# NOTE: There is only one global signal (say "signal post" or "flag mast").
-#       A SIGNAL action writes its signal into it ("sets a flag").
-#       The signal persists until explicitly overwritten.
+# NOTE: There can be multiple active signals at the same time.
+#       A SIGNAL action appends its signal into signals set.
+#       The signal persists until waited on.
 #       To avoid confusion for later tests, it is recommended to clear
-#       the signal by signalling "empty" ("setting the 'empty' flag"):
-#       SET DEBUG_SYNC= 'now SIGNAL empty';
-#       Preferably you can reset the whole facility with:
+#       the signal set by running
 #       SET DEBUG_SYNC= 'RESET';
-#       The signal is then '' (really empty) which connot be done otherwise.
 #
 
 #
diff --git a/mysql-test/main/empty_table.result b/mysql-test/main/empty_table.result
index 2bca3e792fa..90aec2eda3b 100644
--- a/mysql-test/main/empty_table.result
+++ b/mysql-test/main/empty_table.result
@@ -16,3 +16,61 @@ ERROR 42S02: Table 'test.t2' doesn't exist
 show status like "Empty_queries";
 Variable_name	Value
 Empty_queries	2
+# End of 4.1 tests
+#
+# MDEV-30333 Wrong result with not_null_range_scan and LEFT JOIN with empty table
+#
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 (b) VALUES (1),(2);
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+SET optimizer_switch= 'not_null_range_scan=off';
+explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	0.00	Const row not found
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` is null order by `test`.`t1`.`b`
+SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+b
+1
+2
+SET optimizer_switch = 'not_null_range_scan=on';
+explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	0.00	Const row not found
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` is null order by `test`.`t1`.`b`
+SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+b
+1
+2
+flush tables;
+SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+b
+1
+2
+drop table t1,t2;
+# Second test in MDEV-30333
+CREATE TABLE t1 (a int, b varchar(10)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (69,'foo'),(71,'bar');
+CREATE TABLE t2 (c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+CREATE TABLE t3 (d int, e int, KEY(e)) ENGINE=MyISAM;
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1;
+a	b	c	d	e
+69	foo	1	NULL	NULL
+71	bar	1	NULL	NULL
+69	foo	2	NULL	NULL
+71	bar	2	NULL	NULL
+SET optimizer_switch = 'not_null_range_scan=on';
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1;
+a	b	c	d	e
+69	foo	1	NULL	NULL
+71	bar	1	NULL	NULL
+69	foo	2	NULL	NULL
+71	bar	2	NULL	NULL
+DROP TABLE t1, t2, t3;
+set @@optimizer_switch=@save_optimizer_switch;
+End of 10.5 tests
diff --git a/mysql-test/main/empty_table.test b/mysql-test/main/empty_table.test
index 754671868ba..a17b0c897d5 100644
--- a/mysql-test/main/empty_table.test
+++ b/mysql-test/main/empty_table.test
@@ -21,4 +21,38 @@ drop table t1;
 select * from t2;
 show status like "Empty_queries";
 
-# End of 4.1 tests
+--echo # End of 4.1 tests
+
+--echo #
+--echo # MDEV-30333 Wrong result with not_null_range_scan and LEFT JOIN with empty table
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 (b) VALUES (1),(2);
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+SET optimizer_switch= 'not_null_range_scan=off'; # Default
+explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+SET optimizer_switch = 'not_null_range_scan=on';
+explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+flush tables;
+SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b;
+drop table t1,t2;
+
+--echo # Second test in MDEV-30333
+
+CREATE TABLE t1 (a int, b varchar(10)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (69,'foo'),(71,'bar');
+CREATE TABLE t2 (c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+CREATE TABLE t3 (d int, e int, KEY(e)) ENGINE=MyISAM;
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1;
+SET optimizer_switch = 'not_null_range_scan=on';
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1;
+DROP TABLE t1, t2, t3;
+set @@optimizer_switch=@save_optimizer_switch;
+
+--echo End of 10.5 tests
+
diff --git a/mysql-test/main/fetch_first.result b/mysql-test/main/fetch_first.result
index 69a0336d722..df182381d1c 100644
--- a/mysql-test/main/fetch_first.result
+++ b/mysql-test/main/fetch_first.result
@@ -1378,3 +1378,31 @@ a
 bar
 foo
 DROP TABLE t;
+#
+# MDEV-30324: Wrong result upon SELECT DISTINCT .. WITH TIES using index
+#
+CREATE TABLE t1 (a int, b char(3), KEY (a));
+INSERT INTO t1 VALUES (2,'foo'),(3,'bar'),(3,'bar'),(3,'zzz');
+EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	1	Using temporary
+SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES;
+a	b
+2	foo
+EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	2	Using temporary
+SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
+a	b
+2	foo
+3	bar
+3	zzz
+EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES;
+a	b
+2	foo
+3	bar
+3	zzz
+DROP TABLE t1;
diff --git a/mysql-test/main/fetch_first.test b/mysql-test/main/fetch_first.test
index 62cdd717f81..98bbf1ca06b 100644
--- a/mysql-test/main/fetch_first.test
+++ b/mysql-test/main/fetch_first.test
@@ -1059,3 +1059,22 @@ SELECT a FROM t ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
 
 # Cleanup
 DROP TABLE t;
+
+--echo #
+--echo # MDEV-30324: Wrong result upon SELECT DISTINCT .. WITH TIES using index
+--echo #
+CREATE TABLE t1 (a int, b char(3), KEY (a));
+INSERT INTO t1 VALUES (2,'foo'),(3,'bar'),(3,'bar'),(3,'zzz');
+
+EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES;
+--sorted_result
+SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES;
+EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
+--sorted_result
+SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
+EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES;
+--sorted_result
+SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES;
+ 
+# Cleanup
+DROP TABLE t1;
diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result
index 45a75ab082c..c963fdc966d 100644
--- a/mysql-test/main/func_json.result
+++ b/mysql-test/main/func_json.result
@@ -1623,6 +1623,21 @@ id	doc
 {"$oid":"611c0a463b150154132f6636"}	{ "_id" : { "$oid" : "611c0a463b150154132f6636" }, "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : 1.0 } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] }
 DROP TABLE arrNestTest;
 #
+# MDEV-30412 JSON_OBJECTAGG doesn't escape double quote in key
+#
+SELECT JSON_OBJECTAGG('"', 1);
+JSON_OBJECTAGG('"', 1)
+{"\"":1}
+SELECT JSON_OBJECTAGG('\"', 1);
+JSON_OBJECTAGG('\"', 1)
+{"\"":1}
+SELECT JSON_OBJECTAGG('\\', 1);
+JSON_OBJECTAGG('\\', 1)
+{"\\":1}
+#
+# End of 10.5 tests
+#
+#
 # MDEV-26054 Server crashes in Item_func_json_arrayagg::get_str_from_field
 #
 CREATE TABLE t (a VARCHAR(8));
@@ -1649,5 +1664,5 @@ JSON_INSERT(JSON_OBJECT(l1, l2, l3, l4), '$.k3', 'v3')	JSON_SET(JSON_OBJECT(l1,
 {"k1": "v1", "k2": "v2", "k3": "v3"}	{"k1": "v1", "k2": "new v2"}	{"k1": "v1", "k2": "new v2"}
 DROP TABLE t;
 #
-# End of 10.5 tests
+# End of 10.6 tests
 #
diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test
index cbc6156a8c7..464e7d762d8 100644
--- a/mysql-test/main/func_json.test
+++ b/mysql-test/main/func_json.test
@@ -1068,6 +1068,18 @@ SELECT * FROM arrNestTest;
 DROP TABLE arrNestTest;
 
 --echo #
+--echo # MDEV-30412 JSON_OBJECTAGG doesn't escape double quote in key
+--echo #
+
+SELECT JSON_OBJECTAGG('"', 1);
+SELECT JSON_OBJECTAGG('\"', 1);
+SELECT JSON_OBJECTAGG('\\', 1);
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
+
+--echo #
 --echo # MDEV-26054 Server crashes in Item_func_json_arrayagg::get_str_from_field
 --echo #
 
@@ -1078,7 +1090,6 @@ SELECT JSON_ARRAYAGG(a) AS f FROM v;
 DROP VIEW v;
 DROP TABLE t;
 
-
 --echo #
 --echo # MDEV-29264 JSON functions overflow error based ON LONGTEXT field
 --echo #
@@ -1090,6 +1101,5 @@ SELECT JSON_INSERT(JSON_OBJECT(l1, l2, l3, l4), '$.k3', 'v3'),JSON_SET(JSON_OBJE
 DROP TABLE t;
 
 --echo #
---echo # End of 10.5 tests
+--echo # End of 10.6 tests
 --echo #
-
diff --git a/mysql-test/suite/innodb/r/alter_copy.result b/mysql-test/suite/innodb/r/alter_copy.result
index 72ae28e9652..8c9e5966b2e 100644
--- a/mysql-test/suite/innodb/r/alter_copy.result
+++ b/mysql-test/suite/innodb/r/alter_copy.result
@@ -51,7 +51,7 @@ ADD INDEX(a,b,d), ADD INDEX(a,d,b), ADD INDEX(b,c,d), ADD INDEX(b,d,c),
 ALGORITHM=COPY;
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR hung';
-# restart: --innodb-force-recovery=3
+# restart: --innodb-force-recovery=3 --debug_dbug=+d,recv_ran_out_of_buffer
 disconnect hang;
 FTS_INDEX_1.ibd
 FTS_INDEX_2.ibd
diff --git a/mysql-test/suite/innodb/r/recovery_memory.result b/mysql-test/suite/innodb/r/recovery_memory.result
index 4fa31009130..6faea097616 100644
--- a/mysql-test/suite/innodb/r/recovery_memory.result
+++ b/mysql-test/suite/innodb/r/recovery_memory.result
@@ -1,3 +1,7 @@
+call mtr.add_suppression("InnoDB: The change buffer is corrupted");
+call mtr.add_suppression("InnoDB: Plugin initialization aborted at srv0start.cc");
+call mtr.add_suppression("Plugin 'InnoDB' init function returned error");
+call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed.");
 CREATE TABLE t1(c TEXT, KEY(c(3072)))ENGINE=InnoDB;
 CREATE PROCEDURE dorepeat()
 LOOP
@@ -10,3 +14,19 @@ CALL dorepeat();
 connection default;
 # restart: --innodb_buffer_pool_size=5242880
 DROP TABLE t1;
+DROP PROCEDURE dorepeat;
+#
+# MDEV-30552  InnoDB recovery crashes when error
+#        handling scenario
+#
+SET DEBUG_DBUG="+d,ib_log_checkpoint_avoid_hard";
+CREATE TABLE t1(f1 INT NOT NULL)ENGINE=InnoDB;
+INSERT INTO t1 SELECT * FROM seq_1_to_65536;
+# restart: --innodb_buffer_pool_size=5242880 --debug_dbug=+d,ibuf_init_corrupt
+# restart
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/alter_copy.test b/mysql-test/suite/innodb/t/alter_copy.test
index 90f2171d10b..b62f812f4b7 100644
--- a/mysql-test/suite/innodb/t/alter_copy.test
+++ b/mysql-test/suite/innodb/t/alter_copy.test
@@ -57,7 +57,7 @@ ALTER TABLE t ADD INDEX(b,c,d,a),ADD INDEX(b,c,a,d),ADD INDEX(b,a,c,d),ADD INDEX
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR hung';
 let $shutdown_timeout=0;
---let $restart_parameters= --innodb-force-recovery=3
+--let $restart_parameters= --innodb-force-recovery=3 --debug_dbug="+d,recv_ran_out_of_buffer"
 --source include/restart_mysqld.inc
 disconnect hang;
 let $shutdown_timeout=;
diff --git a/mysql-test/suite/innodb/t/recovery_memory.test b/mysql-test/suite/innodb/t/recovery_memory.test
index d9afd52c499..e723ba25d36 100644
--- a/mysql-test/suite/innodb/t/recovery_memory.test
+++ b/mysql-test/suite/innodb/t/recovery_memory.test
@@ -1,5 +1,10 @@
 --source include/have_innodb.inc
 --source include/big_test.inc
+--source include/have_sequence.inc
+call mtr.add_suppression("InnoDB: The change buffer is corrupted");
+call mtr.add_suppression("InnoDB: Plugin initialization aborted at srv0start.cc");
+call mtr.add_suppression("Plugin 'InnoDB' init function returned error");
+call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed.");
 CREATE TABLE t1(c TEXT, KEY(c(3072)))ENGINE=InnoDB;
 DELIMITER |;
 
@@ -19,3 +24,19 @@ let $shutdown_timeout=0;
 let $restart_parameters=--innodb_buffer_pool_size=5242880;
 --source include/restart_mysqld.inc
 DROP TABLE t1;
+DROP PROCEDURE dorepeat;
+
+--echo #
+--echo # MDEV-30552  InnoDB recovery crashes when error
+--echo #        handling scenario
+--echo #
+SET DEBUG_DBUG="+d,ib_log_checkpoint_avoid_hard";
+CREATE TABLE t1(f1 INT NOT NULL)ENGINE=InnoDB;
+INSERT INTO t1 SELECT * FROM seq_1_to_65536;
+let $shutdown_timeout=0;
+let $restart_parameters=--innodb_buffer_pool_size=5242880 --debug_dbug="+d,ibuf_init_corrupt";
+--source include/restart_mysqld.inc
+let $restart_parameters=;
+--source include/restart_mysqld.inc
+SHOW CREATE TABLE t1;
+DROP TABLE t1;