Merge 10.6bb-10.6-MDEV-29911

73 files changed, 2866 insertions, 240 deletions

diff --git a/mysql-test/main/analyze_format_json_emb.result b/mysql-test/main/analyze_format_json_emb.result
new file mode 100644
index 00000000000..d61e205f031
--- /dev/null
+++ b/mysql-test/main/analyze_format_json_emb.result
@@ -0,0 +1,11 @@
+#
+# MDEV-31121: ANALYZE statement produces 0 for all timings in embedded serve
+#
+create table t1 (a int);
+insert into t1 values (0),(0);
+set @js='$out';
+set @out=(select json_extract(@js,'$**.query_block.r_total_time_ms'));
+select cast(json_extract(@out,'$[0]') as DOUBLE) > 0;
+cast(json_extract(@out,'$[0]') as DOUBLE) > 0
+1
+drop table t1;
diff --git a/mysql-test/main/analyze_format_json_emb.test b/mysql-test/main/analyze_format_json_emb.test
new file mode 100644
index 00000000000..dcf6f24dd8e
--- /dev/null
+++ b/mysql-test/main/analyze_format_json_emb.test
@@ -0,0 +1,18 @@
+--source include/is_embedded.inc
+--source include/big_test.inc
+
+--echo #
+--echo # MDEV-31121: ANALYZE statement produces 0 for all timings in embedded serve
+--echo #
+create table t1 (a int);
+insert into t1 values (0),(0);
+let $out=`
+analyze format=json select sleep(1+a) from t1
+`;
+
+evalp set @js='$out';
+set @out=(select json_extract(@js,'$**.query_block.r_total_time_ms'));
+select cast(json_extract(@out,'$[0]') as DOUBLE) > 0;
+
+drop table t1;
+
diff --git a/mysql-test/main/cli_options_force_protocol_not_win.result b/mysql-test/main/cli_options_force_protocol_not_win.result
index 20b12eb2cc0..46ee29dc509 100644
--- a/mysql-test/main/cli_options_force_protocol_not_win.result
+++ b/mysql-test/main/cli_options_force_protocol_not_win.result
@@ -4,21 +4,19 @@
 #
 # The following group of tests should produce no warnings
 #
-# exec MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:\|WARNING:"
+# exec MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:"
 Connection:		Localhost via UNIX socket
-# exec MYSQL --port=MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:\|WARNING:"
+# exec MYSQL --port=MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:"
 Connection:		localhost via TCP/IP
-# exec MYSQL --host=localhost --port=MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:\|WARNING:"
+# exec MYSQL --host=localhost --port=MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:"
 Connection:		Localhost via UNIX socket
-# exec MYSQL --host=127.0.0.1 --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
+# exec MYSQL --host=127.0.0.1 --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
 Connection:		127.0.0.1 via TCP/IP
-# exec MYSQL --host=localhost --socket=MASTER_MYSOCK --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
+# exec MYSQL --host=localhost --socket=MASTER_MYSOCK --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
+Connection:		localhost via TCP/IP
+# exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:"
 Connection:		Localhost via UNIX socket
-# exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:\|WARNING:"
+# exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:"
 Connection:		Localhost via UNIX socket
-#
-# The remaining tests should produce warnings
-#
-# exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
-WARNING: Forcing protocol to  TCP  due to option specification. Please explicitly state intended protocol.
+# exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
 Connection:		localhost via TCP/IP
diff --git a/mysql-test/main/cli_options_force_protocol_not_win.test b/mysql-test/main/cli_options_force_protocol_not_win.test
index a3c495f26ee..a92f6b93c4d 100644
--- a/mysql-test/main/cli_options_force_protocol_not_win.test
+++ b/mysql-test/main/cli_options_force_protocol_not_win.test
@@ -10,28 +10,26 @@
 --echo # The following group of tests should produce no warnings
 --echo #
 
---echo # exec MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:\|WARNING:"
---exec $MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:\|WARNING:"
+--echo # exec MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:"
+--exec $MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:"
 
---echo # exec MYSQL --port=MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:\|WARNING:"
---exec $MYSQL --port=$MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:\|WARNING:"
+--echo # exec MYSQL --port=MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:"
+--exec $MYSQL --port=$MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:"
 
---echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:\|WARNING:"
---exec $MYSQL --host=localhost --port=$MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:\|WARNING:"
+--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:"
+--exec $MYSQL --host=localhost --port=$MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:"
 
---echo # exec MYSQL --host=127.0.0.1 --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
---exec $MYSQL --host=127.0.0.1 --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
+--echo # exec MYSQL --host=127.0.0.1 --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
+--exec $MYSQL --host=127.0.0.1 --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
 
---echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
---exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
+--echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
+--exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
 
---echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:\|WARNING:"
---exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:\|WARNING:"
+--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:"
+--exec $MYSQL --host=localhost --port=MASTER_MYPORT --socket=$MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:"
 
+--echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:"
+--exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:"
 
---echo #
---echo # The remaining tests should produce warnings
---echo #
-
---echo # exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
---exec $MYSQL --host=localhost --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:"
+--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
+--exec $MYSQL --host=localhost --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:"
diff --git a/mysql-test/main/cli_options_force_protocol_win.result b/mysql-test/main/cli_options_force_protocol_win.result
index 4fffc2a4837..eedfde4f1de 100644
--- a/mysql-test/main/cli_options_force_protocol_win.result
+++ b/mysql-test/main/cli_options_force_protocol_win.result
@@ -1,24 +1,17 @@
 #
 # MDEV-14974: --port ignored for --host=localhost
 #
-#
-# The following group of tests should produce no warnings
-#
-# exec MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+# exec MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:"
 Connection:		localhost via TCP/IP
-# exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+# exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:"
 Connection:		localhost via TCP/IP
-# exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+# exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
 Connection:		localhost via TCP/IP
-# exec MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
-Connection:		localhost via named pipe
-# exec MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
-Connection:		localhost via named pipe
-# exec MYSQL --host=localhost -W --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+# exec MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:"
 Connection:		localhost via named pipe
-#
-# The remaining tests should produce warnings
-#
-# exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
-WARNING: Forcing protocol to  PIPE  due to option specification. Please explicitly state intended protocol.
+# exec MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:"
 Connection:		localhost via named pipe
+# exec MYSQL --host=localhost -W --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
+Connection:		localhost via TCP/IP
+# exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
+Connection:		localhost via TCP/IP
diff --git a/mysql-test/main/cli_options_force_protocol_win.test b/mysql-test/main/cli_options_force_protocol_win.test
index 54fbb78e5dc..63024f3b620 100644
--- a/mysql-test/main/cli_options_force_protocol_win.test
+++ b/mysql-test/main/cli_options_force_protocol_win.test
@@ -5,33 +5,23 @@
 --source include/not_embedded.inc
 --source include/windows.inc
 
+--echo # exec MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:"
+--exec $MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:"
 
---echo #
---echo # The following group of tests should produce no warnings
---echo #
-
---echo # exec MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
---exec $MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
-
---echo # exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
---exec $MYSQL --host=localhost --port=$MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:"
+--exec $MYSQL --host=localhost --port=$MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:"
 
---echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
---exec $MYSQL --host=localhost --port=$MASTER_MYPORT --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
+--exec $MYSQL --host=localhost --port=$MASTER_MYPORT --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
 
---echo # exec MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
---exec $MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+--echo # exec MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:"
+--exec $MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:"
 
---echo # exec MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
---exec $MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+--echo # exec MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:"
+--exec $MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:"
 
---echo # exec MYSQL --host=localhost -W --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
---exec $MYSQL --host=localhost -W --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
-
-
---echo #
---echo # The remaining tests should produce warnings
---echo #
+--echo # exec MYSQL --host=localhost -W --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
+--exec $MYSQL --host=localhost -W --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
 
---echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
---exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:"
+--echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
+--exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:"
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index f47920c0e33..2cbdf9ae0b3 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -18248,4 +18248,87 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
 drop view v1;
 drop table t1;
+#
+# MDEV-31102: execution of PS for query where pushdown of condition
+#             into view defined as union is applied
+#
+create table t1 (
+n int,
+lv varchar(31) charset latin1,
+mv varchar(31) charset utf8mb3
+) engine=myisam;
+insert into t1 values (1,'aa','xxx'), ('2','bb','yyy'), (3,'cc','zzz');
+create view v1 as
+select case when n=1 then lv when n=2 then mv else NULL end as r from t1
+union
+select 'a';
+select * from v1 where r < 'x';
+r
+aa
+a
+explain extended select * from v1 where r < 'x';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `v1`.`r` AS `r` from `test`.`v1` where `v1`.`r` < 'x'
+explain format=json select * from v1 where r < 'x';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "v1.r < 'x'",
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union2,3>",
+            "access_type": "ALL",
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 3,
+                    "filtered": 100,
+                    "attached_condition": "case when t1.n = 1 then convert(t1.lv using utf8mb3) when t1.n = 2 then t1.mv else NULL end < 'x'"
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "operation": "UNION",
+                  "table": {
+                    "message": "No tables used"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+prepare stmt from "select * from v1 where r < 'x'";
+execute stmt;
+r
+aa
+a
+execute stmt;
+r
+aa
+a
+deallocate prepare stmt;
+drop view v1;
+drop table t1;
 # End of 10.4 tests
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 17115c143ac..08d0854df57 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3943,4 +3943,34 @@ explain select * from v1;
 drop view v1;
 drop table t1;
 
+--echo #
+--echo # MDEV-31102: execution of PS for query where pushdown of condition
+--echo #             into view defined as union is applied
+--echo #
+
+create table t1 (
+   n int,
+   lv varchar(31) charset latin1,
+   mv varchar(31) charset utf8mb3
+) engine=myisam;
+insert into t1 values (1,'aa','xxx'), ('2','bb','yyy'), (3,'cc','zzz');
+create view v1 as
+select case when n=1 then lv when n=2 then mv else NULL end as r from t1
+union
+select 'a';
+
+let $q=
+select * from v1 where r < 'x';
+
+eval $q;
+eval explain extended $q;
+eval explain format=json $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop view v1;
+drop table t1;
+
 --echo # End of 10.4 tests
diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
index 74876836a53..188a7f334b1 100644
--- a/mysql-test/main/derived_split_innodb.result
+++ b/mysql-test/main/derived_split_innodb.result
@@ -287,4 +287,541 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DERIVED	t4	ALL	NULL	NULL	NULL	NULL	40	Using filesort
 drop table t3, t4;
 # End of 10.3 tests
+#
+# MDEV-26301: Split optimization refills temporary table too many times
+#
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+analyze table t1,t2,t3 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	Table is already up to date
+explain
+select * from
+(t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ref	a	a	5	test.t1.b	2	Using where
+1	SIMPLE	t3	ref	a	a	5	test.t1.b	3	Using where
+create table t10 (
+grp_id int,
+col1 int,
+key(grp_id)
+);
+insert into t10
+select
+A.seq,
+B.seq
+from
+seq_1_to_100 A,
+seq_1_to_100 B;
+create table t11 (
+col1 int,
+col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+analyze table t10,t11 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t10	analyze	status	Engine-independent statistics collected
+test.t10	analyze	status	Table is already up to date
+test.t11	analyze	status	Engine-independent statistics collected
+test.t11	analyze	status	OK
+explain select * from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id) T on T.grp_id=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
+1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
+2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
+2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+# The important part in the below output is:
+#        "lateral": 1,
+#        "query_block": {
+#          "select_id": 2,
+#          "r_loops": 5,  <-- must be 5, not 30.
+analyze format=json select * from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id) T on T.grp_id=t1.b;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "const_condition": "1",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 5,
+      "r_rows": 5,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t1.b"],
+      "r_loops": 5,
+      "rows": 2,
+      "r_rows": 2,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100,
+      "attached_condition": "trigcond(trigcond(t1.b is not null))"
+    },
+    "table": {
+      "table_name": "t3",
+      "access_type": "ref",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t1.b"],
+      "r_loops": 10,
+      "rows": 3,
+      "r_rows": 3,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100,
+      "attached_condition": "trigcond(trigcond(t1.b is not null))"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["grp_id"],
+      "ref": ["test.t1.b"],
+      "r_loops": 30,
+      "rows": 10,
+      "r_rows": 1,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100,
+      "attached_condition": "trigcond(trigcond(t1.b is not null))",
+      "materialized": {
+        "lateral": 1,
+        "query_block": {
+          "select_id": 2,
+          "r_loops": 5,
+          "r_total_time_ms": "REPLACED",
+          "outer_ref_condition": "t1.b is not null",
+          "table": {
+            "table_name": "t10",
+            "access_type": "ref",
+            "possible_keys": ["grp_id"],
+            "key": "grp_id",
+            "key_length": "5",
+            "used_key_parts": ["grp_id"],
+            "ref": ["test.t1.b"],
+            "r_loops": 5,
+            "rows": 100,
+            "r_rows": 100,
+            "r_table_time_ms": "REPLACED",
+            "r_other_time_ms": "REPLACED",
+            "filtered": 100,
+            "r_filtered": 100
+          },
+          "block-nl-join": {
+            "table": {
+              "table_name": "t11",
+              "access_type": "ALL",
+              "r_loops": 5,
+              "rows": 10,
+              "r_rows": 10,
+              "r_table_time_ms": "REPLACED",
+              "r_other_time_ms": "REPLACED",
+              "filtered": 100,
+              "r_filtered": 100
+            },
+            "buffer_type": "flat",
+            "buffer_size": "1Kb",
+            "join_type": "BNL",
+            "attached_condition": "trigcond(t11.col1 = t10.col1)",
+            "r_loops": 500,
+            "r_filtered": 10,
+            "r_unpack_time_ms": "REPLACED",
+            "r_other_time_ms": "REPLACED",
+            "r_effective_rows": 10
+          }
+        }
+      }
+    }
+  }
+}
+create table t21 (pk int primary key);
+insert into t21 values (1),(2),(3);
+create table t22 (pk int primary key);
+insert into t22 values (1),(2),(3);
+explain
+select * from
+t21, t22,
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id) T on T.grp_id=t1.b
+where
+t21.pk=1 and t22.pk=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t21	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t22	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
+1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
+2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
+2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+explain
+select * from
+t21,
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from
+t22 join t10 left join t11 on t11.col1=t10.col1
+where
+t22.pk=1
+group by grp_id) T on T.grp_id=t1.b
+where
+t21.pk=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t21	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
+1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
+2	LATERAL DERIVED	t22	const	PRIMARY	PRIMARY	4	const	1	Using index
+2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
+2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+create table t5 (
+pk int primary key
+);
+insert into t5 select seq from seq_1_to_1000;
+explain
+select * from
+t21,
+(
+(((t1 join t5 on t5.pk=t1.b)) left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from
+t22 join t10 left join t11 on t11.col1=t10.col1
+where
+t22.pk=1
+group by grp_id) T on T.grp_id=t1.b
+where
+t21.pk=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t21	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using index
+1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	
+1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
+2	LATERAL DERIVED	t22	const	PRIMARY	PRIMARY	4	const	1	Using index
+2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t5.pk	100	Using index condition
+2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+drop table t1,t2,t3,t5, t10, t11, t21, t22;
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+analyze table t1,t2,t3 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	Table is already up to date
+create table t10 (
+grp_id int,
+col1 int,
+key(grp_id)
+);
+insert into t10
+select
+A.seq,
+B.seq
+from
+seq_1_to_100 A,
+seq_1_to_100 B;
+create table t11 (
+col1 int,
+col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+analyze table t10,t11 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t10	analyze	status	Engine-independent statistics collected
+test.t10	analyze	status	Table is already up to date
+test.t11	analyze	status	Engine-independent statistics collected
+test.t11	analyze	status	OK
+explain select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+) 
+left join
+( 
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
+1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
+2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
+2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+) 
+left join
+( 
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+a	b	a	b	a	b	grp_id	count(*)
+1	1	1	1	1	1	1	100
+1	1	1	1	1	2	1	100
+1	1	1	1	1	3	1	100
+1	1	1	2	1	1	1	100
+1	1	1	2	1	2	1	100
+1	1	1	2	1	3	1	100
+2	2	2	1	2	1	2	100
+2	2	2	1	2	2	2	100
+2	2	2	1	2	3	2	100
+2	2	2	2	2	1	2	100
+2	2	2	2	2	2	2	100
+2	2	2	2	2	3	2	100
+3	3	3	1	3	1	3	100
+3	3	3	1	3	2	3	100
+3	3	3	1	3	3	3	100
+3	3	3	2	3	1	3	100
+3	3	3	2	3	2	3	100
+3	3	3	2	3	3	3	100
+4	4	4	1	4	1	4	100
+4	4	4	1	4	2	4	100
+4	4	4	1	4	3	4	100
+4	4	4	2	4	1	4	100
+4	4	4	2	4	2	4	100
+4	4	4	2	4	3	4	100
+5	5	5	1	5	1	5	100
+5	5	5	1	5	2	5	100
+5	5	5	1	5	3	5	100
+5	5	5	2	5	1	5	100
+5	5	5	2	5	2	5	100
+5	5	5	2	5	3	5	100
+set join_cache_level=4;
+explain select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+) 
+left join
+( 
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
+1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
+2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
+2	LATERAL DERIVED	t11	hash_ALL	NULL	#hash#$hj	5	test.t10.col1	10	Using where; Using join buffer (flat, BNLH join)
+select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+) 
+left join
+( 
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+a	b	a	b	a	b	grp_id	count(*)
+1	1	1	1	1	1	1	100
+1	1	1	1	1	2	1	100
+1	1	1	1	1	3	1	100
+1	1	1	2	1	1	1	100
+1	1	1	2	1	2	1	100
+1	1	1	2	1	3	1	100
+2	2	2	1	2	1	2	100
+2	2	2	1	2	2	2	100
+2	2	2	1	2	3	2	100
+2	2	2	2	2	1	2	100
+2	2	2	2	2	2	2	100
+2	2	2	2	2	3	2	100
+3	3	3	1	3	1	3	100
+3	3	3	1	3	2	3	100
+3	3	3	1	3	3	3	100
+3	3	3	2	3	1	3	100
+3	3	3	2	3	2	3	100
+3	3	3	2	3	3	3	100
+4	4	4	1	4	1	4	100
+4	4	4	1	4	2	4	100
+4	4	4	1	4	3	4	100
+4	4	4	2	4	1	4	100
+4	4	4	2	4	2	4	100
+4	4	4	2	4	3	4	100
+5	5	5	1	5	1	5	100
+5	5	5	1	5	2	5	100
+5	5	5	1	5	3	5	100
+5	5	5	2	5	1	5	100
+5	5	5	2	5	2	5	100
+5	5	5	2	5	3	5	100
+set join_cache_level=default;
+drop index a on t2;
+drop index a on t3;
+explain select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+) 
+left join
+( 
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	50	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; Using join buffer (incremental, BNL join)
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	1000	Using where
+2	DERIVED	t10	ALL	grp_id	NULL	NULL	NULL	10000	Using temporary; Using filesort
+2	DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+) 
+left join
+( 
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+a	b	a	b	a	b	grp_id	count(*)
+1	1	1	1	1	1	1	100
+1	1	1	2	1	1	1	100
+1	1	1	1	1	2	1	100
+1	1	1	2	1	2	1	100
+1	1	1	1	1	3	1	100
+1	1	1	2	1	3	1	100
+2	2	2	1	2	1	2	100
+2	2	2	2	2	1	2	100
+2	2	2	1	2	2	2	100
+2	2	2	2	2	2	2	100
+2	2	2	1	2	3	2	100
+2	2	2	2	2	3	2	100
+3	3	3	1	3	1	3	100
+3	3	3	2	3	1	3	100
+3	3	3	1	3	2	3	100
+3	3	3	2	3	2	3	100
+3	3	3	1	3	3	3	100
+3	3	3	2	3	3	3	100
+4	4	4	1	4	1	4	100
+4	4	4	2	4	1	4	100
+4	4	4	1	4	2	4	100
+4	4	4	2	4	2	4	100
+4	4	4	1	4	3	4	100
+4	4	4	2	4	3	4	100
+5	5	5	1	5	1	5	100
+5	5	5	2	5	1	5	100
+5	5	5	1	5	2	5	100
+5	5	5	2	5	2	5	100
+5	5	5	1	5	3	5	100
+5	5	5	2	5	3	5	100
+drop table t1,t2,t3;
+drop table t10, t11;
+#
+# MDEV-31194: Server crash or assertion failure with join_cache_level=4
+# (a followup to the above bug, MDEV-26301)
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (3),(4);
+CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=Aria;
+INSERT INTO t2 VALUES (1),(2);
+set @tmp1= @@optimizer_switch, @tmp2= @@join_cache_level;
+set
+optimizer_switch= 'derived_with_keys=off',
+join_cache_level= 4;
+SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a;
+a
+set optimizer_switch= @tmp1, join_cache_level= @tmp2;
+DROP TABLE t1, t2;
+# End of 10.4 tests
 SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;
diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test
index 1ebe27cd12c..6ccbc31d886 100644
--- a/mysql-test/main/derived_split_innodb.test
+++ b/mysql-test/main/derived_split_innodb.test
@@ -233,4 +233,239 @@ drop table t3, t4;
 
 --echo # End of 10.3 tests
 
+
+--echo #
+--echo # MDEV-26301: Split optimization refills temporary table too many times
+--echo #
+
+# 5 values
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+
+# 5 value groups of size 2 each
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+
+# 5 value groups of size 3 each
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+
+analyze table t1,t2,t3 persistent for all;
+
+explain
+select * from
+  (t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b;
+
+# Now, create tables for Groups.
+
+create table t10 (
+  grp_id int,
+  col1 int,
+  key(grp_id)
+);
+
+# 100 groups of 100 values each
+insert into t10
+select
+  A.seq,
+  B.seq
+from
+  seq_1_to_100 A,
+  seq_1_to_100 B;
+
+# and X10 multiplier
+
+create table t11 (
+  col1 int,
+  col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+
+analyze table t10,t11 persistent for all;
+
+let $q1=
+select * from
+  (
+    (t1 left join t2 on t2.a=t1.b)
+    left join t3 on t3.a=t1.b
+  ) left join (select grp_id, count(*)
+               from t10 left join t11 on t11.col1=t10.col1
+               group by grp_id) T on T.grp_id=t1.b;
+
+eval
+explain $q1;
+
+--echo # The important part in the below output is:
+--echo #        "lateral": 1,
+--echo #        "query_block": {
+--echo #          "select_id": 2,
+--echo #          "r_loops": 5,  <-- must be 5, not 30.
+--source include/analyze-format.inc
+
+eval
+analyze format=json $q1;
+
+create table t21 (pk int primary key);
+insert into t21 values (1),(2),(3);
+
+create table t22 (pk int primary key);
+insert into t22 values (1),(2),(3);
+
+# Same as above but throw in a couple of const tables.
+explain
+select * from
+  t21, t22,
+  (
+    (t1 left join t2 on t2.a=t1.b)
+    left join t3 on t3.a=t1.b
+  ) left join (select grp_id, count(*)
+               from t10 left join t11 on t11.col1=t10.col1
+               group by grp_id) T on T.grp_id=t1.b
+where
+  t21.pk=1 and t22.pk=2;
+
+explain
+select * from
+  t21,
+  (
+    (t1 left join t2 on t2.a=t1.b)
+    left join t3 on t3.a=t1.b
+  ) left join (select grp_id, count(*)
+               from
+                 t22 join t10 left join t11 on t11.col1=t10.col1
+               where
+                 t22.pk=1
+               group by grp_id) T on T.grp_id=t1.b
+where
+  t21.pk=1;
+
+# And also add a non-const table
+
+create table t5 (
+  pk int primary key
+  );
+insert into t5 select seq from seq_1_to_1000;
+
+explain
+select * from
+  t21,
+  (
+    (((t1 join t5 on t5.pk=t1.b)) left join t2 on t2.a=t1.b)
+    left join t3 on t3.a=t1.b
+  ) left join (select grp_id, count(*)
+               from
+                 t22 join t10 left join t11 on t11.col1=t10.col1
+               where
+                 t22.pk=1
+               group by grp_id) T on T.grp_id=t1.b
+where
+  t21.pk=1;
+
+drop table t1,t2,t3,t5, t10, t11, t21, t22;
+
+# 5 values
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+
+# 5 value groups of size 2 each
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+
+# 5 value groups of size 3 each
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+
+analyze table t1,t2,t3 persistent for all;
+
+create table t10 (
+  grp_id int,
+  col1 int,
+  key(grp_id)
+);
+
+# 100 groups of 100 values each
+insert into t10
+select
+  A.seq,
+  B.seq
+from
+  seq_1_to_100 A,
+  seq_1_to_100 B;
+
+# and X10 multiplier
+
+create table t11 (
+  col1 int,
+  col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+
+analyze table t10,t11 persistent for all;
+
+let $q=  
+select *
+from
+  (
+    (t1 left join t2 on t2.a=t1.b)
+     left join
+    t3
+    on t3.a=t1.b
+  ) 
+  left join
+  ( 
+    select grp_id, count(*)
+    from t10 left join t11 on t11.col1=t10.col1
+    group by grp_id
+  )dt
+  on dt.grp_id=t1.b;
+
+eval explain $q;
+eval $q;
+
+set join_cache_level=4;
+eval explain $q;
+eval $q;
+
+set join_cache_level=default;
+
+drop index a on t2;
+drop index a on t3;
+
+eval explain $q;
+eval $q;
+
+drop table t1,t2,t3;
+drop table t10, t11;
+
+
+--echo #
+--echo # MDEV-31194: Server crash or assertion failure with join_cache_level=4
+--echo # (a followup to the above bug, MDEV-26301)
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (3),(4);
+
+CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=Aria;
+INSERT INTO t2 VALUES (1),(2);
+
+set @tmp1= @@optimizer_switch, @tmp2= @@join_cache_level;
+set
+  optimizer_switch= 'derived_with_keys=off',
+  join_cache_level= 4;
+
+SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a;
+
+set optimizer_switch= @tmp1, join_cache_level= @tmp2;
+
+# Cleanup
+DROP TABLE t1, t2;
+
+--echo # End of 10.4 tests
+
 SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;
diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index c367b882e7f..4cb567ccab0 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -4171,3 +4171,71 @@ deallocate prepare stmt;
 drop view v;
 drop table t1,t2,t3;
 # End of 10.4 tests
+#
+# MDEV-31143: view with ORDER BY used in query with rownum() in WHERE
+#
+create table t1 (id int primary key);
+insert into t1 values (3), (7), (1);
+create table t2 (a int);
+insert into t2 values (2), (4);
+create view v as select a from t2 order by a;
+set big_tables= 1;
+Warnings:
+Warning	1287	'@@big_tables' is deprecated and will be removed in a future release
+select t1.id from v, t1 where rownum() = 1 group by t1.id;
+id
+1
+set big_tables=default;
+Warnings:
+Warning	1287	'@@big_tables' is deprecated and will be removed in a future release
+drop view v;
+drop table t1, t2;
+#
+# MDEV-31162: multi-table mergeable view with ORDER BY used
+#             in query with rownum() in WHERE
+#
+create table t1 (a INT) engine=MyISAM;
+insert into t1 values (1),(2);
+create table t2 (b INT) engine=MyISAM;
+insert into t2 values (3),(4);
+create view v1 AS select * from t1 join t2 order by b;
+explain select * from v1 where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+select * from v1 where rownum() <= 2;
+a	b
+1	3
+2	3
+prepare stmt from "select * from v1 where rownum() <= 2";
+execute stmt;
+a	b
+1	3
+2	3
+execute stmt;
+a	b
+1	3
+2	3
+deallocate prepare stmt;
+create view v2 AS select * from t1 join t2 order by b/a;
+explain select * from v2 where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+select * from v2 where rownum() <= 2;
+a	b
+2	3
+1	3
+prepare stmt from "select * from v2 where rownum() <= 2";
+execute stmt;
+a	b
+2	3
+1	3
+execute stmt;
+a	b
+2	3
+1	3
+deallocate prepare stmt;
+drop view v1,v2;
+drop table t1,t2;
+# End of 10.6 tests
diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test
index 5422fbcfd1d..777389a9844 100644
--- a/mysql-test/main/derived_view.test
+++ b/mysql-test/main/derived_view.test
@@ -2752,3 +2752,65 @@ drop view v;
 drop table t1,t2,t3;
 
 --echo # End of 10.4 tests
+
+--echo #
+--echo # MDEV-31143: view with ORDER BY used in query with rownum() in WHERE
+--echo #
+
+create table t1 (id int primary key);
+insert into t1 values (3), (7), (1);
+
+create table t2 (a int);
+insert into t2 values (2), (4);
+
+create view v as select a from t2 order by a;
+
+set big_tables= 1;
+select t1.id from v, t1 where rownum() = 1 group by t1.id;
+
+set big_tables=default;
+
+drop view v;
+drop table t1, t2;
+
+--echo #
+--echo # MDEV-31162: multi-table mergeable view with ORDER BY used
+--echo #             in query with rownum() in WHERE
+--echo #
+
+create table t1 (a INT) engine=MyISAM;
+insert into t1 values (1),(2);
+
+create table t2 (b INT) engine=MyISAM;
+insert into t2 values (3),(4);
+
+create view v1 AS select * from t1 join t2 order by b;
+let $q1=
+select * from v1 where rownum() <= 2;
+
+eval explain $q1;
+--sorted_result
+eval $q1;
+
+eval prepare stmt from "$q1";
+--sorted_result
+execute stmt;
+--sorted_result
+execute stmt;
+deallocate prepare stmt;
+
+create view v2 AS select * from t1 join t2 order by b/a;
+let $q2=
+select * from v2 where rownum() <= 2;
+
+eval explain $q2;
+eval $q2;
+eval prepare stmt from "$q2";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop view v1,v2;
+drop table t1,t2;
+
+--echo # End of 10.6 tests
diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result
index 0c5eccad754..fa9f0259a0f 100644
--- a/mysql-test/main/distinct.result
+++ b/mysql-test/main/distinct.result
@@ -1093,6 +1093,7 @@ sum(distinct 1)	sum(t1.d) > 5	c
 1	1	0
 1	0	5
 1	1	6
+SET @sort_buffer_size_save= @@sort_buffer_size;
 set @@sort_buffer_size=1024;
 insert into t1 select -seq,-seq from seq_1_to_100;
 select distinct sum(distinct 1), sum(t1.d) > 2, length(group_concat(t1.d)) > 1000 from (t1 e join t1) group by t1.c having t1.c > -2 ;
@@ -1106,6 +1107,19 @@ sum(distinct 1)	sum(t1.d) > 2	length(group_concat(t1.d)) > 1000	c
 1	1	0	5
 1	1	0	6
 drop table t1;
+set @@sort_buffer_size=@sort_buffer_size_save;
+#
+# MDEV-31113 Server crashes in store_length / Type_handler_string_result::make_sort_key
+# with DISTINCT and group function
+#
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+SELECT DISTINCT CONVERT(STDDEV(f), CHAR(16)) AS f1, UUID() AS f2 FROM t GROUP BY f2 WITH ROLLUP;
+f1	f2
+0.0000	#
+0.0000	#
+0.5000	#
+DROP TABLE t;
 # End of 10.4 tests
 #
 # MDEV-27382: OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN
diff --git a/mysql-test/main/distinct.test b/mysql-test/main/distinct.test
index 9039edff20f..893e2dcc9a7 100644
--- a/mysql-test/main/distinct.test
+++ b/mysql-test/main/distinct.test
@@ -834,11 +834,24 @@ select distinct sum(distinct 1), sum(t1.d) > 5 from (t1 e join t1) group by t1.c
 select distinct sum(distinct 1), sum(t1.d) > 5, t1.c from (t1 e join t1) group by t1.c;
 
 # Force usage of remove_dup_with_compare() algorithm
+SET @sort_buffer_size_save= @@sort_buffer_size;
 set @@sort_buffer_size=1024;
 insert into t1 select -seq,-seq from seq_1_to_100;
 select distinct sum(distinct 1), sum(t1.d) > 2, length(group_concat(t1.d)) > 1000 from (t1 e join t1) group by t1.c having t1.c > -2 ;
 select distinct sum(distinct 1), sum(t1.d) > 2, length(group_concat(t1.d)) > 1000,t1.c from (t1 e join t1) group by t1.c having t1.c > -2;
 drop table t1;
+set @@sort_buffer_size=@sort_buffer_size_save;
+
+--echo #
+--echo # MDEV-31113 Server crashes in store_length / Type_handler_string_result::make_sort_key
+--echo # with DISTINCT and group function
+--echo #
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+--replace_column 2 #
+SELECT DISTINCT CONVERT(STDDEV(f), CHAR(16)) AS f1, UUID() AS f2 FROM t GROUP BY f2 WITH ROLLUP;
+DROP TABLE t;
 
 --echo # End of 10.4 tests
 
diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result
index b46665c279c..c44d7baadea 100644
--- a/mysql-test/main/explain_innodb.result
+++ b/mysql-test/main/explain_innodb.result
@@ -18,3 +18,21 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DERIVED	t1	range	NULL	id	53	NULL	2	Using index for group-by
 SET GLOBAL slow_query_log = @sql_tmp;
 drop table t1;
+#
+# MDEV-31181: Server crash in subselect_uniquesubquery_engine::print
+# upon EXPLAIN EXTENDED DELETE
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (pk INT PRIMARY KEY);
+INSERT INTO t2 VALUES (1),(2);
+EXPLAIN EXTENDED DELETE FROM t1 WHERE a IN (SELECT pk FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	unique_subquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ delete  from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`))))
+drop table t1, t2;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test
index 2c29a6e26da..3dcad4c2d49 100644
--- a/mysql-test/main/explain_innodb.test
+++ b/mysql-test/main/explain_innodb.test
@@ -18,3 +18,22 @@ SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0;
 
 SET GLOBAL slow_query_log = @sql_tmp;
 drop table t1;
+
+
+--echo #
+--echo # MDEV-31181: Server crash in subselect_uniquesubquery_engine::print
+--echo # upon EXPLAIN EXTENDED DELETE
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (pk INT PRIMARY KEY);
+INSERT INTO t2 VALUES (1),(2);
+ 
+EXPLAIN EXTENDED DELETE FROM t1 WHERE a IN (SELECT pk FROM t2);
+
+drop table t1, t2;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/insert_innodb.result b/mysql-test/main/insert_innodb.result
index 314412bcfdd..b8f3979d2ba 100644
--- a/mysql-test/main/insert_innodb.result
+++ b/mysql-test/main/insert_innodb.result
@@ -51,7 +51,7 @@ CREATE TEMPORARY TABLE v0 ( v1 TEXT ( 15 ) CHAR SET BINARY NOT NULL NOT NULL UNI
 ERROR HY000: Field 'v1' doesn't have a default value
 CREATE TEMPORARY TABLE t1 (i TEXT(15) NOT NULL DEFAULT '' UNIQUE CHECK (i)) engine=innodb
 REPLACE SELECT NULL AS a;
-ERROR HY000: Field 'DB_ROW_HASH_1' doesn't have a default value
+ERROR 23000: CONSTRAINT `t1.i` failed for `test`.`t1`
 #
 # End of 10.5 tests
 #
diff --git a/mysql-test/main/insert_innodb.test b/mysql-test/main/insert_innodb.test
index b5a9fc72c07..56ddb2eafbf 100644
--- a/mysql-test/main/insert_innodb.test
+++ b/mysql-test/main/insert_innodb.test
@@ -71,7 +71,7 @@ DROP TABLE t2, t1;
 --error ER_NO_DEFAULT_FOR_FIELD
 CREATE TEMPORARY TABLE v0 ( v1 TEXT ( 15 ) CHAR SET BINARY NOT NULL NOT NULL UNIQUE CHECK ( v1 ) ) REPLACE SELECT NULL AS v3 , 74 AS v2 ;
 
---error ER_NO_DEFAULT_FOR_FIELD
+--error ER_CONSTRAINT_FAILED
 CREATE TEMPORARY TABLE t1 (i TEXT(15) NOT NULL DEFAULT '' UNIQUE CHECK (i)) engine=innodb
   REPLACE SELECT NULL AS a;
 
diff --git a/mysql-test/main/insert_update.result b/mysql-test/main/insert_update.result
index 68a1003ad85..83344971c59 100644
--- a/mysql-test/main/insert_update.result
+++ b/mysql-test/main/insert_update.result
@@ -412,3 +412,44 @@ select if( @stamp1 = @stamp2, "correct", "wrong");
 if( @stamp1 = @stamp2, "correct", "wrong")
 correct
 drop table t1;
+#
+# MDEV-31164 default current_timestamp() not working when used INSERT ON DUPLICATE KEY in some cases
+#
+set timestamp=unix_timestamp('2000-10-20 0:0:0');
+create table t1 (pk integer primary key, val varchar(20) not null, ts timestamp);
+insert t1 (pk, val) values(1, 'val1');
+select * from t1;
+pk	val	ts
+1	val1	2000-10-20 00:00:00
+set timestamp=unix_timestamp('2000-10-20 1:0:0');
+insert t1 (pk, val) select 2, 'val3' union select 3, 'val4'
+    on duplicate key update ts=now();
+select * from t1;
+pk	val	ts
+1	val1	2000-10-20 00:00:00
+2	val3	2000-10-20 01:00:00
+3	val4	2000-10-20 01:00:00
+set timestamp=unix_timestamp('2000-10-20 2:0:0');
+insert t1 (pk, val) select 1, 'val1' union select 4, 'val2'
+    on duplicate key update ts=now();
+select * from t1;
+pk	val	ts
+1	val1	2000-10-20 02:00:00
+2	val3	2000-10-20 01:00:00
+3	val4	2000-10-20 01:00:00
+4	val2	2000-10-20 02:00:00
+set timestamp=unix_timestamp('2000-10-20 3:0:0');
+insert t1 (pk, val) select 5, 'val1' union select 1, 'val2'
+    on duplicate key update ts=now();
+select * from t1;
+pk	val	ts
+1	val1	2000-10-20 03:00:00
+2	val3	2000-10-20 01:00:00
+3	val4	2000-10-20 01:00:00
+4	val2	2000-10-20 02:00:00
+5	val1	2000-10-20 03:00:00
+drop table t1;
+set timestamp=default;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/insert_update.test b/mysql-test/main/insert_update.test
index 06e16be84d7..25953938ad1 100644
--- a/mysql-test/main/insert_update.test
+++ b/mysql-test/main/insert_update.test
@@ -311,3 +311,29 @@ insert into t1(f1) values(1) on duplicate key update f1=1;
 select @stamp2:=f2 from t1;
 select if( @stamp1 = @stamp2, "correct", "wrong");
 drop table t1;
+
+--echo #
+--echo # MDEV-31164 default current_timestamp() not working when used INSERT ON DUPLICATE KEY in some cases
+--echo #
+set timestamp=unix_timestamp('2000-10-20 0:0:0');
+create table t1 (pk integer primary key, val varchar(20) not null, ts timestamp);
+insert t1 (pk, val) values(1, 'val1');
+select * from t1;
+set timestamp=unix_timestamp('2000-10-20 1:0:0');
+insert t1 (pk, val) select 2, 'val3' union select 3, 'val4'
+    on duplicate key update ts=now();
+select * from t1;
+set timestamp=unix_timestamp('2000-10-20 2:0:0');
+insert t1 (pk, val) select 1, 'val1' union select 4, 'val2'
+    on duplicate key update ts=now();
+select * from t1;
+set timestamp=unix_timestamp('2000-10-20 3:0:0');
+insert t1 (pk, val) select 5, 'val1' union select 1, 'val2'
+    on duplicate key update ts=now();
+select * from t1;
+drop table t1;
+set timestamp=default;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/join_cache_cardinality.result b/mysql-test/main/join_cache_cardinality.result
new file mode 100644
index 00000000000..0a76080e601
--- /dev/null
+++ b/mysql-test/main/join_cache_cardinality.result
@@ -0,0 +1,105 @@
+create table t1 (a int, b int, c int);
+insert into t1 select seq,seq/2, seq/4 from seq_1_to_100;
+create table t2 (a int, b int, c int);
+insert into t2 select seq, seq/2, seq/4 from seq_1_to_200;
+analyze table t1,t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+set optimizer_trace=1;
+set join_cache_level=6;
+set optimizer_switch='hash_join_cardinality=on';
+explain select *
+from t1, t2
+where t1.a=t2.a and t1.a=t2.b and t1.c=t2.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	15	test.t1.a,test.t1.a,test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "a",
+                "avg_frequency": 1
+            },
+            {
+                "field": "b",
+                "avg_frequency": 2
+            },
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 1
+    }
+]
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+ROWS_FOR_PLAN
+[100]
+explain select *
+from t1, t2 where t1.c=t2.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 3.9216
+    }
+]
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+ROWS_FOR_PLAN
+[392.16]
+explain select *
+from t1 straight_join t2 where t1.c=t2.c and t2.a<30;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+# Note that rows is the same:
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 3.9216
+    }
+]
+# Despite available selectivity:
+select json_detailed(json_extract(@json, '$**.selectivity_for_columns')) as JS;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["NULL < a < 30"],
+            "selectivity_from_histogram": 0.1484375
+        }
+    ]
+]
+drop table t1,t2;
diff --git a/mysql-test/main/join_cache_cardinality.test b/mysql-test/main/join_cache_cardinality.test
new file mode 100644
index 00000000000..b178810b807
--- /dev/null
+++ b/mysql-test/main/join_cache_cardinality.test
@@ -0,0 +1,41 @@
+--source include/have_sequence.inc
+
+# Embedded doesn't have optimizer trace:
+--source include/not_embedded.inc
+
+create table t1 (a int, b int, c int);
+insert into t1 select seq,seq/2, seq/4 from seq_1_to_100;
+
+create table t2 (a int, b int, c int);
+insert into t2 select seq, seq/2, seq/4 from seq_1_to_200;
+
+analyze table t1,t2 persistent for all;
+
+set optimizer_trace=1;
+set join_cache_level=6;
+set optimizer_switch='hash_join_cardinality=on';
+explain select *
+from t1, t2
+where t1.a=t2.a and t1.a=t2.b and t1.c=t2.c;
+
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+
+explain select *
+from t1, t2 where t1.c=t2.c;
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+
+explain select *
+from t1 straight_join t2 where t1.c=t2.c and t2.a<30;
+set @json= (select trace from information_schema.optimizer_trace);
+--echo # Note that rows is the same:
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+
+--echo # Despite available selectivity:
+select json_detailed(json_extract(@json, '$**.selectivity_for_columns')) as JS;
+drop table t1,t2;
diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result
index 283ea870a5f..ed13c668ec3 100644
--- a/mysql-test/main/long_unique_bugs.result
+++ b/mysql-test/main/long_unique_bugs.result
@@ -451,6 +451,12 @@ a	b
 1	xxx
 drop table t1;
 #
+# MDEV-22756 SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value
+#
+create table t1 (f text not null, unique (f));
+insert into t1 (f) select 'f';
+drop table t1;
+#
 # End of 10.4 tests
 #
 #
diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test
index 86003a6881c..3c1c101ffa0 100644
--- a/mysql-test/main/long_unique_bugs.test
+++ b/mysql-test/main/long_unique_bugs.test
@@ -444,6 +444,13 @@ select * from t1;
 drop table t1;
 
 --echo #
+--echo # MDEV-22756 SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value
+--echo #
+create table t1 (f text not null, unique (f));
+insert into t1 (f) select 'f';
+drop table t1;
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
 
diff --git a/mysql-test/main/mysqld--help,win.rdiff b/mysql-test/main/mysqld--help,win.rdiff
index 337755252ba..bcfefbab1a8 100644
--- a/mysql-test/main/mysqld--help,win.rdiff
+++ b/mysql-test/main/mysqld--help,win.rdiff
@@ -1,6 +1,12 @@
---- a/mysql-test/r/mysqld--help.result
-+++ b/mysql-test/r/mysqld--help.result
-@@ -647,6 +646,7 @@
+@@ -180,6 +180,7 @@
+  --console           Write error output on screen; don't remove the console
+  window on windows.
+  --core-file         Write core on crashes
++ (Defaults to on; use --skip-core-file to disable.)
+  -h, --datadir=name  Path to the database root directory
+  --date-format=name  The DATE format (ignored)
+  --datetime-format=name 
+@@ -650,6 +651,7 @@
   Use MySQL-5.6 (instead of MariaDB-5.3) format for TIME,
   DATETIME, TIMESTAMP columns.
   (Defaults to on; use --skip-mysql56-temporal-format to disable.)
@@ -8,7 +14,7 @@
   --net-buffer-length=# 
   Buffer length for TCP/IP and socket communication
   --net-read-timeout=# 
-@@ -1236,6 +1236,10 @@
+@@ -1281,6 +1283,10 @@
   Log slow queries to given log file. Defaults logging to
   'hostname'-slow.log. Must be enabled to activate other
   slow log options
@@ -19,7 +25,7 @@
   --socket=name       Socket file to use for connection
   --sort-buffer-size=# 
   Each thread that needs to do a sort allocates a buffer of
-@@ -1260,6 +1264,7 @@
+@@ -1305,6 +1311,7 @@
   deleting or updating every row in a table.
   --stack-trace       Print a symbolic stack trace on failure
   (Defaults to on; use --skip-stack-trace to disable.)
@@ -27,7 +33,7 @@
   --standard-compliant-cte 
   Allow only CTEs compliant to SQL standard
   (Defaults to on; use --skip-standard-compliant-cte to disable.)
-@@ -1330,6 +1335,11 @@
+@@ -1380,6 +1387,11 @@
   --thread-pool-max-threads=# 
   Maximum allowed number of worker threads in the thread
   pool
@@ -39,7 +45,7 @@
   --thread-pool-oversubscribe=# 
   How many additional active worker threads in a group are
   allowed.
-@@ -1370,8 +1380,8 @@
+@@ -1418,8 +1430,8 @@
   automatically convert it to an on-disk MyISAM or Aria
   table.
   -t, --tmpdir=name   Path for temporary files. Several paths may be specified,
@@ -50,7 +56,7 @@
   --transaction-alloc-block-size=# 
   Allocation block size for transactions to be stored in
   binary log
-@@ -1587,6 +1596,7 @@
+@@ -1634,6 +1646,7 @@
  myisam-stats-method NULLS_UNEQUAL
  myisam-use-mmap FALSE
  mysql56-temporal-format TRUE
@@ -58,7 +64,7 @@
  net-buffer-length 16384
  net-read-timeout 30
  net-retry-count 10
-@@ -1726,6 +1736,7 @@
+@@ -1788,6 +1801,7 @@
  slave-type-conversions 
  slow-launch-time 2
  slow-query-log FALSE
@@ -66,8 +72,8 @@
  sort-buffer-size 2097152
  sql-mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  sql-safe-updates FALSE
-@@ -1753,6 +1764,8 @@
- thread-cache-size 151
+@@ -1814,6 +1828,8 @@
+ thread-pool-exact-stats FALSE
  thread-pool-idle-timeout 60
  thread-pool-max-threads 65536
 +thread-pool-min-threads 1
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index a3dac7fcfcb..5c1a8840fa5 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -185,7 +185,7 @@ The following specify which files/extra groups are read (specified before remain
  ALWAYS
  --console           Write error output on screen; don't remove the console
  window on windows.
- --core-file         Write core on errors.
+ --core-file         Write core on crashes
  -h, --datadir=name  Path to the database root directory
  --date-format=name  The DATE format (ignored)
  --datetime-format=name 
@@ -734,7 +734,8 @@ The following specify which files/extra groups are read (specified before remain
  extended_keys, exists_to_in, orderby_uses_equalities, 
  condition_pushdown_for_derived, split_materialized, 
  condition_pushdown_for_subquery, rowid_filter, 
- condition_pushdown_from_having, not_null_range_scan
+ condition_pushdown_from_having, not_null_range_scan, 
+ hash_join_cardinality
  --optimizer-trace=name 
  Controls tracing of the Optimizer:
  optimizer_trace=option=val[,option=val...], where option
@@ -1505,6 +1506,7 @@ column-compression-zlib-wrap FALSE
 completion-type NO_CHAIN
 concurrent-insert AUTO
 console TRUE
+core-file TRUE
 date-format %Y-%m-%d
 datetime-format %Y-%m-%d %H:%i:%s
 deadlock-search-depth-long 15
diff --git a/mysql-test/main/mysqltest_tracking_info.result b/mysql-test/main/mysqltest_tracking_info.result
index 61bb3f2d1e2..791f2a2b4a4 100644
--- a/mysql-test/main/mysqltest_tracking_info.result
+++ b/mysql-test/main/mysqltest_tracking_info.result
@@ -38,7 +38,7 @@ SET @@session.session_track_system_variables='optimizer_switch';
 set optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off';
 -- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
 -- optimizer_switch
--- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+-- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 
 Warnings:
 Warning	1681	'engine_condition_pushdown=on' is deprecated and will be removed in a future release
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index c747423c99b..725b095c72e 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -457,6 +457,11 @@ select * from v2	{
                   ]
                 },
                 {
+                  "check_split_materialized": {
+                    "not_applicable": "no candidate field can be accessed through ref"
+                  }
+                },
+                {
                   "best_join_order": ["t1"]
                 },
                 {
@@ -780,6 +785,11 @@ explain select * from v1	{
                   ]
                 },
                 {
+                  "check_split_materialized": {
+                    "not_applicable": "group list has no candidates"
+                  }
+                },
+                {
                   "best_join_order": ["t1"]
                 },
                 {
@@ -8904,6 +8914,110 @@ SET optimizer_trace=DEFAULT;
 DROP VIEW v;
 DROP TABLE t;
 #
+# MDEV-26301: Split optimization improvements: Optimizer Trace coverage
+#
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+analyze table t1,t2,t3 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	Table is already up to date
+create table t10 (
+grp_id int,
+col1 int,
+key(grp_id)
+);
+insert into t10
+select
+A.seq,
+B.seq
+from
+seq_1_to_100 A,
+seq_1_to_100 B;
+create table t11 (
+col1 int,
+col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+analyze table t10,t11 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t10	analyze	status	Engine-independent statistics collected
+test.t10	analyze	status	Table is already up to date
+test.t11	analyze	status	Engine-independent statistics collected
+test.t11	analyze	status	OK
+set optimizer_trace=1;
+explain
+select * from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id) T on T.grp_id=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	t2	ref	a	a	5	test.t1.b	2	Using where
+1	PRIMARY	t3	ref	a	a	5	test.t1.b	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.b	10	Using where
+2	LATERAL DERIVED	t10	ref	grp_id	grp_id	5	test.t1.b	100	
+2	LATERAL DERIVED	t11	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS
+from information_schema.optimizer_trace;
+JS
+[
+    {
+        "split_candidates": 
+        ["t10.grp_id"]
+    }
+]
+select 
+json_detailed(
+json_remove(
+json_extract(trace, '$**.choose_best_splitting')
+, '$[0].split_plan_search[0]'
+    )
+) as JS
+from information_schema.optimizer_trace;
+JS
+[
+    {
+        "considered_keys": 
+        [
+            {
+                "table_name": "t10",
+                "index": "grp_id",
+                "rec_per_key": 100,
+                "param_tables": 1
+            }
+        ],
+        "refills": 5,
+        "spl_pd_boundary": 2,
+        "split_plan_search": 
+        [],
+        "lead_table": "t10",
+        "index": "grp_id",
+        "parts": 1,
+        "split_sel": 0.001,
+        "cost": 2535.968504,
+        "unsplit_cost": 253440.0075,
+        "records": 100,
+        "chosen": true
+    }
+]
+drop table t1,t2,t3,t10,t11;
+set optimizer_trace=DEFAULT;
+#
 # End of 10.4 tests
 #
 set optimizer_trace='enabled=on';
@@ -9326,69 +9440,73 @@ from
 information_schema.optimizer_trace;
 json_detailed(json_extract(trace, '$**.choose_best_splitting'))
 [
-    [
-        {
-            "considered_execution_plans": 
-            [
-                {
-                    "plan_prefix": 
-                    [],
-                    "table": "t2",
-                    "best_access_path": 
+    {
+        "considered_keys": 
+        [
+            {
+                "table_name": "t2",
+                "index": "idx_a",
+                "rec_per_key": 1.8367,
+                "param_tables": 1
+            }
+        ],
+        "refills": 4,
+        "spl_pd_boundary": 2,
+        "split_plan_search": 
+        [
+            {
+                "considered_execution_plans": 
+                [
                     {
-                        "considered_access_paths": 
-                        [
+                        "plan_prefix": 
+                        [],
+                        "table": "t2",
+                        "best_access_path": 
+                        {
+                            "considered_access_paths": 
+                            [
+                                {
+                                    "access_type": "ref",
+                                    "index": "idx_a",
+                                    "used_range_estimates": false,
+                                    "cause": "not available",
+                                    "rows": 1.8367,
+                                    "cost": 2.000585794,
+                                    "chosen": true
+                                },
+                                {
+                                    "type": "scan",
+                                    "chosen": false,
+                                    "cause": "cost"
+                                }
+                            ],
+                            "chosen_access_method": 
                             {
-                                "access_type": "ref",
-                                "index": "idx_a",
-                                "used_range_estimates": false,
-                                "cause": "not available",
-                                "rows": 1.8367,
+                                "type": "ref",
+                                "records": 1.8367,
                                 "cost": 2.000585794,
-                                "chosen": true
-                            },
-                            {
-                                "type": "scan",
-                                "chosen": false,
-                                "cause": "cost"
+                                "uses_join_buffering": false
                             }
-                        ],
-                        "chosen_access_method": 
-                        {
-                            "type": "ref",
-                            "records": 1.8367,
-                            "cost": 2.000585794,
-                            "uses_join_buffering": false
-                        }
-                    },
-                    "rows_for_plan": 1.8367,
-                    "cost_for_plan": 2.367925794,
-                    "cost_for_sorting": 1.8367
-                }
-            ]
-        },
-        {
-            "best_splitting": 
-            {
-                "table": "t2",
-                "key": "idx_a",
-                "record_count": 4,
-                "cost": 2.488945919,
-                "unsplit_cost": 25.72361682
+                        },
+                        "rows_for_plan": 1.8367,
+                        "cost_for_plan": 2.367925794,
+                        "cost_for_sorting": 1.8367
+                    }
+                ]
             }
-        }
-    ]
-]
-select
-json_detailed(json_extract(trace, '$**.lateral_derived')) 
-from
-information_schema.optimizer_trace;
-json_detailed(json_extract(trace, '$**.lateral_derived'))
-[
+        ],
+        "lead_table": "t2",
+        "index": "idx_a",
+        "parts": 1,
+        "split_sel": 0.020407778,
+        "cost": 2.488945919,
+        "unsplit_cost": 25.72361682,
+        "records": 1,
+        "chosen": true
+    },
     {
-        "startup_cost": 9.955783677,
-        "splitting_cost": 2.488945919,
-        "records": 1
+        "considered_keys": 
+        []
     }
 ]
 drop table t1,t2;
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index a30a536125a..171f5a2627f 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -702,6 +702,76 @@ DROP VIEW v;
 DROP TABLE t;
 
 --echo #
+--echo # MDEV-26301: Split optimization improvements: Optimizer Trace coverage
+--echo #
+
+# 5 values
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+ 
+# 5 value groups of size 2 each
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+ 
+# 5 value groups of size 3 each
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+ 
+analyze table t1,t2,t3 persistent for all;
+
+create table t10 (
+  grp_id int,
+  col1 int,
+  key(grp_id)
+);
+ 
+# 100 groups of 100 values each
+insert into t10
+select
+  A.seq,
+  B.seq
+from
+  seq_1_to_100 A,
+  seq_1_to_100 B;
+ 
+# and X10 multiplier
+create table t11 (
+  col1 int,
+  col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+ 
+analyze table t10,t11 persistent for all;
+
+set optimizer_trace=1;
+explain
+select * from
+  (
+    (t1 left join t2 on t2.a=t1.b)
+    left join t3 on t3.a=t1.b
+  ) left join (select grp_id, count(*)
+               from t10 left join t11 on t11.col1=t10.col1
+               group by grp_id) T on T.grp_id=t1.b;
+
+select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS
+from information_schema.optimizer_trace;
+
+select 
+  json_detailed(
+    json_remove(
+      json_extract(trace, '$**.choose_best_splitting')
+      , '$[0].split_plan_search[0]'
+    )
+  ) as JS
+from information_schema.optimizer_trace;
+
+drop table t1,t2,t3,t10,t11;
+set optimizer_trace=DEFAULT;
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
 
@@ -909,13 +979,6 @@ from
   information_schema.optimizer_trace;
 --enable_view_protocol
 
-# Same as above. just to show that splitting plan has some coverage in the
-# trace.
-select
-  json_detailed(json_extract(trace, '$**.lateral_derived')) 
-from
-  information_schema.optimizer_trace;
-
 drop table t1,t2;
 
 --echo #
diff --git a/mysql-test/main/rownum.result b/mysql-test/main/rownum.result
index 3ad51c93260..b61269b1b47 100644
--- a/mysql-test/main/rownum.result
+++ b/mysql-test/main/rownum.result
@@ -139,6 +139,13 @@ select * from t1,t2 where t1.a=t2.a and rownum()<=2 order by t1.a,t2.a;
 a	b	a	b
 2	20	2	21
 3	30	3	31
+create view v1 as
+select * from (select * from t1 order by a desc) as t where rownum() <= 2;
+select * from v1;
+a	b
+3	30
+2	20
+drop view v1;
 #
 # Having
 #
@@ -984,3 +991,29 @@ next row is 3
 3
 next row is 5
 5
+#
+# MDEV-31073: Server crash, assertion `table != 0 &&
+# view->field_translation != 0' failure with ROWNUM and view
+#
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT * FROM t;
+UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1;
+DROP VIEW v;
+DROP TABLE t;
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, 3 as e FROM t;
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+DROP VIEW v;
+DROP TABLE t;
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, ROWNUM() as e FROM t;
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+ERROR HY000: The target table v of the UPDATE is not updatable
+DROP VIEW v;
+DROP TABLE t;
+#
+# End of 10.6 tests
+#
diff --git a/mysql-test/main/rownum.test b/mysql-test/main/rownum.test
index bdd0bfa4f41..291bd9bd993 100644
--- a/mysql-test/main/rownum.test
+++ b/mysql-test/main/rownum.test
@@ -58,6 +58,11 @@ select *,rownum() from t1,t2 order by t2.a desc, t1.a desc;
 select * from (select * from t1 order by a desc) as t where rownum() <= 2;
 select * from t1,t2 where t1.a=t2.a and rownum()<=2 order by t1.a,t2.a;
 
+create view v1 as
+select * from (select * from t1 order by a desc) as t where rownum() <= 2;
+select * from v1;
+drop view v1;
+
 --echo #
 --echo # Having
 --echo #
@@ -568,3 +573,40 @@ drop table t1;
 --echo # Table value constructors
 --echo #
 values ("first row"),("next row is 3"),(rownum()),("next row is 5"),(rownum());
+
+--echo #
+--echo # MDEV-31073: Server crash, assertion `table != 0 &&
+--echo # view->field_translation != 0' failure with ROWNUM and view
+--echo #
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT * FROM t;
+UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1;
+
+# Cleanup
+DROP VIEW v;
+DROP TABLE t;
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, 3 as e FROM t;
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+
+# Cleanup
+DROP VIEW v;
+DROP TABLE t;
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, ROWNUM() as e FROM t;
+--error ER_NON_UPDATABLE_TABLE
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+
+# Cleanup
+DROP VIEW v;
+DROP TABLE t;
+
+--echo #
+--echo # End of 10.6 tests
+--echo #
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index b5a48d341ae..69e3c336c71 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -834,7 +834,7 @@ flush table t1;
 set optimizer_use_condition_selectivity=4;
 explain extended select * from t1 where a=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1025	0.39	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1025	0.78	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 0
 drop table t1;
@@ -1649,7 +1649,7 @@ test.t1	analyze	status	Table is already up to date
 # Check what info the optimizer has about selectivities
 explain extended select * from t1 use index () where a in (17,51,5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	3.90	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	3.91	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5)
 explain extended select * from t1 use index () where b=2;
@@ -1941,9 +1941,9 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	25.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+DROP TABLE t1;
+# End of 10.2 tests
 set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
 set histogram_size=@save_histogram_size;
 set use_stat_tables= @save_use_stat_tables;
-DROP TABLE t1;
-# End of 10.2 tests
 set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test
index 4e4513d09d6..df3850d74b7 100644
--- a/mysql-test/main/selectivity.test
+++ b/mysql-test/main/selectivity.test
@@ -1321,16 +1321,17 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
 FLUSH TABLES;
 
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
-set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
-set histogram_size=@save_histogram_size;
-set use_stat_tables= @save_use_stat_tables;
 
 DROP TABLE t1;
 
 --echo # End of 10.2 tests
 
+
 #
 # Clean up
 #
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
 --source include/restore_charset.inc
 set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 3edf6d8e929..24dac99523e 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -845,7 +845,7 @@ flush table t1;
 set optimizer_use_condition_selectivity=4;
 explain extended select * from t1 where a=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1025	0.39	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1025	0.78	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 0
 drop table t1;
@@ -1661,7 +1661,7 @@ test.t1	analyze	status	OK
 # Check what info the optimizer has about selectivities
 explain extended select * from t1 use index () where a in (17,51,5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	3.90	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	3.91	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5)
 explain extended select * from t1 use index () where b=2;
@@ -1953,11 +1953,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	25.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+DROP TABLE t1;
+# End of 10.2 tests
 set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
 set histogram_size=@save_histogram_size;
 set use_stat_tables= @save_use_stat_tables;
-DROP TABLE t1;
-# End of 10.2 tests
 set @@global.histogram_size=@save_histogram_size;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 set @tmp_ust= @@use_stat_tables;
diff --git a/mysql-test/main/selectivity_innodb_notembedded.result b/mysql-test/main/selectivity_innodb_notembedded.result
new file mode 100644
index 00000000000..8b06fe7556b
--- /dev/null
+++ b/mysql-test/main/selectivity_innodb_notembedded.result
@@ -0,0 +1,104 @@
+SET SESSION STORAGE_ENGINE='InnoDB';
+Warnings:
+Warning	1287	'@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead
+set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+drop table if exists t0,t1,t2,t3;
+select @@global.use_stat_tables;
+@@global.use_stat_tables
+COMPLEMENTARY
+select @@session.use_stat_tables;
+@@session.use_stat_tables
+COMPLEMENTARY
+set @save_use_stat_tables=@@use_stat_tables;
+set use_stat_tables='preferably';
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_histogram_size=@@histogram_size;
+set @save_histogram_type=@@histogram_type;
+set join_cache_level=2;
+set @@global.histogram_size=0,@@local.histogram_size=0;
+set histogram_type='single_prec_hb';
+set optimizer_use_condition_selectivity=3;
+#
+# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram
+#
+create table t0(a int);
+insert into t0 select 1 from seq_1_to_78;
+create table t1(a int);
+insert into t1 select 1 from seq_1_to_26;
+create table t10 (a int);
+insert into t10 select 0 from t0, seq_1_to_4;
+insert into t10 select 8693 from t1;
+insert into t10 select 8694 from t1;
+insert into t10 select 8695 from t1;
+insert into t10 select 34783 from t1;
+insert into t10 select 34784 from t1;
+insert into t10 select 34785 from t1;
+insert into t10 select 34785 from t0, seq_1_to_8;
+insert into t10 select 65214 from t1;
+insert into t10 select 65215 from t1;
+insert into t10 select 65216 from t1;
+insert into t10 select 65216 from t0, seq_1_to_52;
+insert into t10 select 65217 from t1;
+insert into t10 select 65218 from t1;
+insert into t10 select 65219 from t1;
+insert into t10 select 65219 from t0;
+insert into t10 select 73913 from t1;
+insert into t10 select 73914 from t1;
+insert into t10 select 73915 from t1;
+insert into t10 select 73915 from t0, seq_1_to_40;
+insert into t10 select 78257 from t1;
+insert into t10 select 78258 from t1;
+insert into t10 select 78259 from t1;
+insert into t10 select 91300 from t1;
+insert into t10 select 91301 from t1;
+insert into t10 select 91302 from t1;
+insert into t10 select 91302 from t0, seq_1_to_6;
+insert into t10 select 91303 from t1;
+insert into t10 select 91304 from t1;
+insert into t10 select 91305 from t1;
+insert into t10 select 91305 from t0, seq_1_to_8;
+insert into t10 select  99998 from t1;
+insert into t10 select  99999 from t1;
+insert into t10 select 100000 from t1;
+set use_stat_tables=preferably;
+analyze table t10 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t10	analyze	status	Engine-independent statistics collected
+test.t10	analyze	status	OK
+flush tables;
+set @tmp=@@optimizer_trace;
+set optimizer_trace=1;
+explain select * from t10  where a in (91303);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t10	ALL	NULL	NULL	NULL	NULL	9984	Using where
+# Must have selectivity_from_histogram <= 1.0:
+select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel
+from information_schema.optimizer_trace;
+sel
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["91303 <= a <= 91303"],
+            "selectivity_from_histogram": 0.035714283
+        }
+    ]
+]
+set optimizer_trace=@tmp;
+drop table t0,t1,t10;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+#
+# End of 10.4 tests
+#
+#
+# Clean up
+#
+set @@global.histogram_size=@save_histogram_size;
+set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
+SET SESSION STORAGE_ENGINE=DEFAULT;
+Warnings:
+Warning	1287	'@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead
diff --git a/mysql-test/main/selectivity_innodb_notembedded.test b/mysql-test/main/selectivity_innodb_notembedded.test
new file mode 100644
index 00000000000..387f7dcb7de
--- /dev/null
+++ b/mysql-test/main/selectivity_innodb_notembedded.test
@@ -0,0 +1,16 @@
+--source include/have_innodb.inc
+# This test is slow on buildbot.
+--source include/big_test.inc
+--source include/default_optimizer_switch.inc
+--source include/not_embedded.inc
+
+SET SESSION STORAGE_ENGINE='InnoDB';
+
+set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+
+--source selectivity_notembedded.test
+
+set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
+
+SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/main/selectivity_no_engine.result b/mysql-test/main/selectivity_no_engine.result
index 3811b12a1be..00e2b8fc397 100644
--- a/mysql-test/main/selectivity_no_engine.result
+++ b/mysql-test/main/selectivity_no_engine.result
@@ -36,12 +36,12 @@ test.t2	analyze	status	OK
 # The following two must have the same in 'Extra' column:
 explain extended select * from t2 where col1 IN (20, 180);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1100	1.35	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1100	1.00	Using where
 Warnings:
 Note	1003	select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` in (20,180)
 explain extended select * from t2 where col1 IN (180, 20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1100	1.35	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1100	1.00	Using where
 Warnings:
 Note	1003	select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` in (180,20)
 drop table t1, t2;
@@ -102,7 +102,7 @@ test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 explain extended select * from t1 where col1 in (1,2,3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10000	3.37	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10000	2.97	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where `test`.`t1`.`col1` in (1,2,3)
 # Must not cause fp division by zero, or produce nonsense numbers:
@@ -314,6 +314,29 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	5	Using where
 1	SIMPLE	b	hash_ALL	NULL	#hash#$hj	1341	test.a.Host,test.a.User,test.a.Password,test.a.Select_priv,test.a.Insert_priv,test.a.Update_priv,test.a.Delete_priv,test.a.Create_priv,test.a.Drop_priv,test.a.Reload_priv,test.a.Shutdown_priv,test.a.Process_priv,test.a.File_priv,test.a.Grant_priv,test.a.References_priv,test.a.Index_priv,test.a.Alter_priv,test.a.Show_db_priv,test.a.Super_priv,test.a.Create_tmp_table_priv,test.a.Lock_tables_priv,test.a.Execute_priv,test.a.Repl_slave_priv,test.a.Repl_client_priv,test.a.Create_view_priv,test.a.Show_view_priv,test.a.Create_routine_priv,test.a.Alter_routine_priv,test.a.Create_user_priv,test.a.Event_priv,test.a.Trigger_priv,test.a.Create_tablespace_priv,test.a.Delete_history_priv,test.a.ssl_type,test.a.ssl_cipher,test.a.x509_issuer,test.a.x509_subject,test.a.max_questions,test.a.max_updates,test.a.max_connections,test.a.max_user_connections,test.a.plugin,test.a.authentication_string,test.a.password_expired,test.a.is_role,test.a.default_role,test.a.max_statement_time	5	Using where; Using join buffer (flat, BNLH join)
 DROP TABLE t1,t2,t3;
+#
+# MDEV-31199: Assertion `field->table->stats_is_read' fails with hash_join_cardinality=on
+#
+CREATE TABLE t1 (a VARCHAR(255));
+INSERT INTO t1 VALUES ('u'),('uu');
+CREATE TABLE t2 (b VARCHAR(255)) CHARACTER SET utf8mb4;
+INSERT INTO t2 VALUES ('x'),('xx');
+CREATE TABLE t3 (c VARCHAR(255));
+INSERT INTO t3 VALUES ('z'),('zz');
+ANALYZE TABLE t1, t2, t3 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
+set @tmp1=@@optimizer_switch, @tmp2=@@join_cache_level;
+set optimizer_switch='hash_join_cardinality=on', join_cache_level=3;
+SELECT t1.* FROM t1 JOIN (SELECT DISTINCT b FROM t2 JOIN t3) sq ON sq.b = t1.a;
+a
+set optimizer_switch=@tmp1, join_cache_level=@tmp2;
+DROP TABLE t1, t2, t3;
 # 
 # End of the test file
 # 
diff --git a/mysql-test/main/selectivity_no_engine.test b/mysql-test/main/selectivity_no_engine.test
index 5bc78e03781..47bac21a2e1 100644
--- a/mysql-test/main/selectivity_no_engine.test
+++ b/mysql-test/main/selectivity_no_engine.test
@@ -250,6 +250,26 @@ SELECT * FROM t1 AS a NATURAL JOIN t1 AS b;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # MDEV-31199: Assertion `field->table->stats_is_read' fails with hash_join_cardinality=on
+--echo #
+CREATE TABLE t1 (a VARCHAR(255));
+INSERT INTO t1 VALUES ('u'),('uu');
+
+CREATE TABLE t2 (b VARCHAR(255)) CHARACTER SET utf8mb4;
+INSERT INTO t2 VALUES ('x'),('xx');
+
+CREATE TABLE t3 (c VARCHAR(255));
+INSERT INTO t3 VALUES ('z'),('zz');
+
+ANALYZE TABLE t1, t2, t3 PERSISTENT FOR ALL; # Optional, fails either way
+
+set @tmp1=@@optimizer_switch, @tmp2=@@join_cache_level;
+set optimizer_switch='hash_join_cardinality=on', join_cache_level=3;
+SELECT t1.* FROM t1 JOIN (SELECT DISTINCT b FROM t2 JOIN t3) sq ON sq.b = t1.a;
+set optimizer_switch=@tmp1, join_cache_level=@tmp2;
+DROP TABLE t1, t2, t3;
+
 --echo # 
 --echo # End of the test file
 --echo # 
diff --git a/mysql-test/main/selectivity_notembedded.result b/mysql-test/main/selectivity_notembedded.result
new file mode 100644
index 00000000000..d2e90a19a68
--- /dev/null
+++ b/mysql-test/main/selectivity_notembedded.result
@@ -0,0 +1,95 @@
+drop table if exists t0,t1,t2,t3;
+select @@global.use_stat_tables;
+@@global.use_stat_tables
+COMPLEMENTARY
+select @@session.use_stat_tables;
+@@session.use_stat_tables
+COMPLEMENTARY
+set @save_use_stat_tables=@@use_stat_tables;
+set use_stat_tables='preferably';
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_histogram_size=@@histogram_size;
+set @save_histogram_type=@@histogram_type;
+set join_cache_level=2;
+set @@global.histogram_size=0,@@local.histogram_size=0;
+set histogram_type='single_prec_hb';
+set optimizer_use_condition_selectivity=3;
+#
+# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram
+#
+create table t0(a int);
+insert into t0 select 1 from seq_1_to_78;
+create table t1(a int);
+insert into t1 select 1 from seq_1_to_26;
+create table t10 (a int);
+insert into t10 select 0 from t0, seq_1_to_4;
+insert into t10 select 8693 from t1;
+insert into t10 select 8694 from t1;
+insert into t10 select 8695 from t1;
+insert into t10 select 34783 from t1;
+insert into t10 select 34784 from t1;
+insert into t10 select 34785 from t1;
+insert into t10 select 34785 from t0, seq_1_to_8;
+insert into t10 select 65214 from t1;
+insert into t10 select 65215 from t1;
+insert into t10 select 65216 from t1;
+insert into t10 select 65216 from t0, seq_1_to_52;
+insert into t10 select 65217 from t1;
+insert into t10 select 65218 from t1;
+insert into t10 select 65219 from t1;
+insert into t10 select 65219 from t0;
+insert into t10 select 73913 from t1;
+insert into t10 select 73914 from t1;
+insert into t10 select 73915 from t1;
+insert into t10 select 73915 from t0, seq_1_to_40;
+insert into t10 select 78257 from t1;
+insert into t10 select 78258 from t1;
+insert into t10 select 78259 from t1;
+insert into t10 select 91300 from t1;
+insert into t10 select 91301 from t1;
+insert into t10 select 91302 from t1;
+insert into t10 select 91302 from t0, seq_1_to_6;
+insert into t10 select 91303 from t1;
+insert into t10 select 91304 from t1;
+insert into t10 select 91305 from t1;
+insert into t10 select 91305 from t0, seq_1_to_8;
+insert into t10 select  99998 from t1;
+insert into t10 select  99999 from t1;
+insert into t10 select 100000 from t1;
+set use_stat_tables=preferably;
+analyze table t10 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t10	analyze	status	Engine-independent statistics collected
+test.t10	analyze	status	OK
+flush tables;
+set @tmp=@@optimizer_trace;
+set optimizer_trace=1;
+explain select * from t10  where a in (91303);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t10	ALL	NULL	NULL	NULL	NULL	9984	Using where
+# Must have selectivity_from_histogram <= 1.0:
+select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel
+from information_schema.optimizer_trace;
+sel
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["91303 <= a <= 91303"],
+            "selectivity_from_histogram": 0.035714283
+        }
+    ]
+]
+set optimizer_trace=@tmp;
+drop table t0,t1,t10;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+#
+# End of 10.4 tests
+#
+#
+# Clean up
+#
+set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity_notembedded.test b/mysql-test/main/selectivity_notembedded.test
new file mode 100644
index 00000000000..6752bd3c7e1
--- /dev/null
+++ b/mysql-test/main/selectivity_notembedded.test
@@ -0,0 +1,121 @@
+--source include/no_valgrind_without_big.inc
+--source include/have_stat_tables.inc
+--source include/have_sequence.inc
+--source include/default_charset.inc
+--source include/not_embedded.inc
+
+--disable_warnings
+drop table if exists t0,t1,t2,t3;
+--enable_warnings
+
+select @@global.use_stat_tables;
+select @@session.use_stat_tables;
+
+set @save_use_stat_tables=@@use_stat_tables;
+set use_stat_tables='preferably';
+
+--source include/default_optimizer_switch.inc
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_histogram_size=@@histogram_size;
+set @save_histogram_type=@@histogram_type;
+set join_cache_level=2;
+set @@global.histogram_size=0,@@local.histogram_size=0;
+set histogram_type='single_prec_hb';
+
+# check that statistics on nulls is used
+
+set optimizer_use_condition_selectivity=3;
+
+--echo #
+--echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram
+--echo #
+create table t0(a int);  # This holds how many rows we hold in a bucket.
+insert into t0 select 1 from seq_1_to_78;
+
+create table t1(a int);  # one-third of a bucket
+insert into t1 select 1 from seq_1_to_26;
+
+create table t10 (a int);
+insert into t10 select 0 from t0, seq_1_to_4;
+
+insert into t10 select 8693 from t1;
+insert into t10 select 8694 from t1;
+insert into t10 select 8695 from t1;
+
+
+insert into t10 select 34783 from t1;
+insert into t10 select 34784 from t1;
+insert into t10 select 34785 from t1;
+
+
+insert into t10 select 34785 from t0, seq_1_to_8;
+
+insert into t10 select 65214 from t1;
+insert into t10 select 65215 from t1;
+insert into t10 select 65216 from t1;
+
+insert into t10 select 65216 from t0, seq_1_to_52;
+
+insert into t10 select 65217 from t1;
+insert into t10 select 65218 from t1;
+insert into t10 select 65219 from t1;
+
+insert into t10 select 65219 from t0;
+
+
+insert into t10 select 73913 from t1;
+insert into t10 select 73914 from t1;
+insert into t10 select 73915 from t1;
+
+insert into t10 select 73915 from t0, seq_1_to_40;
+
+
+insert into t10 select 78257 from t1;
+insert into t10 select 78258 from t1;
+insert into t10 select 78259 from t1;
+
+insert into t10 select 91300 from t1;
+insert into t10 select 91301 from t1;
+insert into t10 select 91302 from t1;
+
+insert into t10 select 91302 from t0, seq_1_to_6;
+
+insert into t10 select 91303 from t1; # Only 1/3rd of bucket matches the search tuple
+insert into t10 select 91304 from t1;
+insert into t10 select 91305 from t1;
+
+insert into t10 select 91305 from t0, seq_1_to_8;
+
+insert into t10 select  99998 from t1;
+insert into t10 select  99999 from t1;
+insert into t10 select 100000 from t1;
+
+set use_stat_tables=preferably;
+analyze table t10 persistent for all;
+flush tables;
+
+set @tmp=@@optimizer_trace;
+set optimizer_trace=1;
+explain select * from t10  where a in (91303);
+
+--echo # Must have selectivity_from_histogram <= 1.0:
+select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel
+from information_schema.optimizer_trace;
+
+set optimizer_trace=@tmp;
+drop table t0,t1,t10;
+
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
+
+--echo #
+--echo # Clean up
+--echo #
+--source include/restore_charset.inc
+set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index aec4a5d09f2..329f367844e 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6951,6 +6951,24 @@ create algorithm=merge view v as
 select * from t1 left join t2 on t1.a=t2.b and t1.a in (select d from t3);
 ERROR 42S22: Unknown column 'd' in 'field list'
 drop table t1,t2,t3;
+#
+# MDEV-31189: Server crash or assertion failure in upon 2nd
+# execution of PS with views and HAVING
+#
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v1 AS SELECT 1 AS a;
+CREATE VIEW v2 AS SELECT a FROM v1;
+PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+DROP VIEW v1;
+DROP VIEW v2;
+DROP TABLE t;
 # End of 10.4 tests
 #
 # MDEV-13115: SELECT .. SKIP LOCKED - ensure SHOW CREATE VIEW is correct
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index c6cc9a69f93..fa3f0d312a7 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6695,6 +6695,25 @@ create algorithm=merge view v as
 
 drop table t1,t2,t3;
 
+--echo #
+--echo # MDEV-31189: Server crash or assertion failure in upon 2nd
+--echo # execution of PS with views and HAVING
+--echo #
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2); # Optional, fails either way
+CREATE VIEW v1 AS SELECT 1 AS a;
+CREATE VIEW v2 AS SELECT a FROM v1;
+
+PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
+EXECUTE stmt;
+EXECUTE stmt;
+
+# Cleanup
+DROP VIEW v1;
+DROP VIEW v2;
+DROP TABLE t;
+
 --echo # End of 10.4 tests
 
 --echo #
diff --git a/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001 b/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001
new file mode 100644
index 00000000000..0fa163d0484
--- /dev/null
+++ b/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001
diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.cnf b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.cnf
index 73c9ad655bf..73c9ad655bf 100644
--- a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.cnf
+++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.cnf
diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.result b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.result
index d632c565ad2..33c1f1413b4 100644
--- a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.result
+++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.result
@@ -4,6 +4,7 @@
 include/rpl_init.inc [topology=1->2]
 connection server_2;
 include/stop_slave.inc
+CHANGE MASTER TO MASTER_USE_GTID=NO;
 #####################################################
 # Part 1: unencrypted master
 #####################################################
diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.test b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.test
index 1e1b0cbd353..a34c9715239 100644
--- a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.test
+++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.test
@@ -1,14 +1,19 @@
 #
-# TODO: write here what the test checks after MDEV-11288 is fixed
-#
-# The test starts with unencrypted master. 
+# The test starts with unencrypted master.
 # It stops replication, generates a few statement and row events
-# on the master, then restarts the server with encrypted binlog, 
-# generates some more events and restarts it back without encryption 
+# on the master, then restarts the server with encrypted binlog,
+# generates some more events and restarts it back without encryption
 # (no encryption plugin).
-# Then it resumes replication and checks what happens when the server
-# tries to feed the binary logs (included the encrypted ones) 
-# to the slave.
+# Then it resumes replication and should error with
+# ER_MASTER_FATAL_ERROR_READING_BINLOG because the encrypted binlog is
+# sent and unable to be decrypted.
+#
+# Note this variation of encrypted_master_switch_to_unencrypted tests
+# using MASTER_USE_GTID=NO. In contrast to the GTID variant of this
+# test, at part 3 (the error case), the master will scan binlogs
+# starting from the first one (which is unencrypted initially, so
+# replication is okay) and continue until the slave encounters the
+# first encrypted event, which causes the slave to error.
 #
 
 --source include/have_binlog_format_mixed.inc
@@ -27,6 +32,7 @@
 --connection server_2
 --disable_connect_log
 --source include/stop_slave.inc
+CHANGE MASTER TO MASTER_USE_GTID=NO;
 --enable_connect_log
 
 --echo #####################################################
@@ -41,7 +47,7 @@ CREATE TABLE table1_no_encryption (
   pk INT AUTO_INCREMENT PRIMARY KEY,
   ts TIMESTAMP NULL,
   b BLOB
-) ENGINE=MyISAM; 
+) ENGINE=MyISAM;
 
 INSERT INTO table1_no_encryption VALUES (NULL,NOW(),'data_no_encryption');
 INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption;
@@ -74,7 +80,7 @@ CREATE TABLE table2_to_encrypt (
   pk INT AUTO_INCREMENT PRIMARY KEY,
   ts TIMESTAMP NULL,
   b BLOB
-) ENGINE=MyISAM; 
+) ENGINE=MyISAM;
 
 INSERT INTO table2_to_encrypt VALUES (NULL,NOW(),'data_to_encrypt');
 INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt;
@@ -101,7 +107,7 @@ CREATE TABLE table3_no_encryption (
   pk INT AUTO_INCREMENT PRIMARY KEY,
   ts TIMESTAMP NULL,
   b BLOB
-) ENGINE=MyISAM; 
+) ENGINE=MyISAM;
 
 INSERT INTO table3_no_encryption VALUES (NULL,NOW(),'data_no_encryption');
 INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption;
@@ -113,7 +119,7 @@ INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption;
 
 --connection server_2
 start slave;
-# The slave should be able to synchronize with master up to 
+# The slave should be able to synchronize with master up to
 # the previously saved position (when the log was still unencrypted)
 --sync_with_master
 
diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.cnf b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.cnf
new file mode 100644
index 00000000000..ad1b8b44c24
--- /dev/null
+++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.cnf
@@ -0,0 +1,8 @@
+!include my.cnf
+
+[mysqld.1]
+encrypt-binlog=0
+skip-file-key-management
+
+[mysqld.2]
+gtid-domain-id=1
diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.result b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.result
new file mode 100644
index 00000000000..16ea30557e7
--- /dev/null
+++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.result
@@ -0,0 +1,84 @@
+#################
+# Initialization
+#################
+include/rpl_init.inc [topology=1->2]
+connection server_2;
+include/stop_slave.inc
+CHANGE MASTER TO MASTER_USE_GTID=SLAVE_POS;
+call mtr.add_suppression(" Got fatal error 1236 from master when reading data from binary log: 'Could not set up decryption for binlog.'");
+#####################################################
+# Part 1: unencrypted master
+#####################################################
+connection server_1;
+CREATE TABLE table1_no_encryption (
+pk INT AUTO_INCREMENT PRIMARY KEY,
+ts TIMESTAMP NULL,
+b BLOB
+) ENGINE=MyISAM;
+INSERT INTO table1_no_encryption VALUES (NULL,NOW(),'data_no_encryption');
+INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption;
+FLUSH BINARY LOGS;
+SET binlog_format=ROW;
+INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption;
+INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption;
+NOT FOUND /table1_no_encryption/ in master-bin.0*
+#####################################################
+# Part 2: restart master, now with binlog encryption
+#####################################################
+connection default;
+connection server_1;
+CREATE TABLE table2_to_encrypt (
+pk INT AUTO_INCREMENT PRIMARY KEY,
+ts TIMESTAMP NULL,
+b BLOB
+) ENGINE=MyISAM;
+INSERT INTO table2_to_encrypt VALUES (NULL,NOW(),'data_to_encrypt');
+INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt;
+FLUSH BINARY LOGS;
+SET binlog_format=ROW;
+INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt;
+INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt;
+NOT FOUND /table2_to_encrypt/ in master-bin.0*
+#####################################################
+# Part 3: restart master again without encryption
+#####################################################
+connection default;
+connection server_1;
+CREATE TABLE table3_no_encryption (
+pk INT AUTO_INCREMENT PRIMARY KEY,
+ts TIMESTAMP NULL,
+b BLOB
+) ENGINE=MyISAM;
+INSERT INTO table3_no_encryption VALUES (NULL,NOW(),'data_no_encryption');
+INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption;
+INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption;
+#####################################################
+# Check: resume replication and check how it goes
+#####################################################
+connection server_2;
+start slave;
+include/wait_for_slave_io_error.inc [errno=1236]
+# Ensuring slave was unable to replicate any transactions..
+# ..success
+SHOW TABLES;
+Tables_in_test
+include/stop_slave.inc
+reset slave;
+##########
+# Cleanup
+##########
+connection server_1;
+reset master;
+SELECT COUNT(*) FROM table1_no_encryption;
+COUNT(*)
+8
+SELECT COUNT(*) FROM table2_to_encrypt;
+COUNT(*)
+8
+SELECT COUNT(*) FROM table3_no_encryption;
+COUNT(*)
+4
+DROP TABLE table1_no_encryption, table2_to_encrypt, table3_no_encryption;
+connection server_2;
+include/start_slave.inc
+include/rpl_end.inc
diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.test b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.test
new file mode 100644
index 00000000000..f882e8f3440
--- /dev/null
+++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.test
@@ -0,0 +1,154 @@
+#
+# The test starts with unencrypted master.
+# It stops replication, generates a few statement and row events
+# on the master, then restarts the server with encrypted binlog,
+# generates some more events and restarts it back without encryption
+# (no encryption plugin).
+# Then it resumes replication and should error with
+# ER_MASTER_FATAL_ERROR_READING_BINLOG because the encrypted binlog is
+# sent and unable to be decrypted.
+#
+# Note this variation of encrypted_master_switch_to_unencrypted tests
+# using MASTER_USE_GTID=SLAVE_POS. encrypted_master_switch_to_unencrypted
+# was the original test which only used binlog coordinates. When tested
+# using MASTER_USE_GTID=Slave_Pos, the master optimizes the detection of
+# an undecryptable binlog. I.e, the master will initially look for a
+# Gtid_list_log_event, but fail to decrypt it and fail immediately in
+# part 3.
+#
+
+--source include/have_binlog_format_mixed.inc
+
+--echo #################
+--echo # Initialization
+--echo #################
+
+--let $rpl_topology= 1->2
+--source include/rpl_init.inc
+
+--enable_connect_log
+
+# We stop replication because we want it to happen after the switch
+
+--connection server_2
+--disable_connect_log
+--source include/stop_slave.inc
+CHANGE MASTER TO MASTER_USE_GTID=SLAVE_POS;
+--enable_connect_log
+call mtr.add_suppression(" Got fatal error 1236 from master when reading data from binary log: 'Could not set up decryption for binlog.'");
+
+--echo #####################################################
+--echo # Part 1: unencrypted master
+--echo #####################################################
+
+--connection server_1
+
+CREATE TABLE table1_no_encryption (
+  pk INT AUTO_INCREMENT PRIMARY KEY,
+  ts TIMESTAMP NULL,
+  b BLOB
+) ENGINE=MyISAM;
+
+INSERT INTO table1_no_encryption VALUES (NULL,NOW(),'data_no_encryption');
+INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption;
+FLUSH BINARY LOGS;
+SET binlog_format=ROW;
+INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption;
+INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption;
+
+# Make sure that binary logs are not encrypted
+
+--let SEARCH_RANGE = 500000
+--let SEARCH_FILE= master-bin.0*
+--let SEARCH_PATTERN= table1_no_encryption
+--source include/search_pattern_in_file.inc
+
+--echo #####################################################
+--echo # Part 2: restart master, now with binlog encryption
+--echo #####################################################
+
+--let $rpl_server_parameters= --encrypt-binlog=1 --plugin-load-add=$FILE_KEY_MANAGEMENT_SO --file-key-management --loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys.txt
+
+--let $rpl_server_number= 1
+--source restart_server.inc
+
+CREATE TABLE table2_to_encrypt (
+  pk INT AUTO_INCREMENT PRIMARY KEY,
+  ts TIMESTAMP NULL,
+  b BLOB
+) ENGINE=MyISAM;
+
+INSERT INTO table2_to_encrypt VALUES (NULL,NOW(),'data_to_encrypt');
+INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt;
+FLUSH BINARY LOGS;
+SET binlog_format=ROW;
+INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt;
+INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt;
+
+# Make sure that binary logs are encrypted
+
+--let SEARCH_FILE= master-bin.0*
+--let SEARCH_PATTERN= table2_to_encrypt
+--source include/search_pattern_in_file.inc
+
+--echo #####################################################
+--echo # Part 3: restart master again without encryption
+--echo #####################################################
+
+--let $rpl_server_parameters= --encrypt-binlog=0
+--let $rpl_server_number= 1
+--source restart_server.inc
+
+CREATE TABLE table3_no_encryption (
+  pk INT AUTO_INCREMENT PRIMARY KEY,
+  ts TIMESTAMP NULL,
+  b BLOB
+) ENGINE=MyISAM;
+
+INSERT INTO table3_no_encryption VALUES (NULL,NOW(),'data_no_encryption');
+INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption;
+INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption;
+
+--echo #####################################################
+--echo # Check: resume replication and check how it goes
+--echo #####################################################
+
+--connection server_2
+start slave;
+# Make slave to try to synchronize. It shouldn't work, the slave IO thread is
+# expected to abort with an error
+--let $slave_io_errno= 1236
+--source include/wait_for_slave_io_error.inc
+
+--echo # Ensuring slave was unable to replicate any transactions..
+--let $gsp= `SELECT @@global.gtid_slave_pos`
+if (`SELECT strcmp("$gsp","")`)
+{
+  die Slave without encryption configured should fail to read encrypted binlog;
+}
+--echo # ..success
+
+--sorted_result
+SHOW TABLES;
+
+--disable_connect_log
+--source include/stop_slave.inc
+--enable_connect_log
+reset slave;
+
+--echo ##########
+--echo # Cleanup
+--echo ##########
+
+--connection server_1
+reset master;
+
+SELECT COUNT(*) FROM table1_no_encryption;
+SELECT COUNT(*) FROM table2_to_encrypt;
+SELECT COUNT(*) FROM table3_no_encryption;
+DROP TABLE table1_no_encryption, table2_to_encrypt, table3_no_encryption;
+
+--connection server_2
+--disable_connect_log
+--source include/start_slave.inc
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/galera/r/galera_var_retry_autocommit.result b/mysql-test/suite/galera/r/galera_var_retry_autocommit.result
index 56c2c995402..50667b0a4fa 100644
--- a/mysql-test/suite/galera/r/galera_var_retry_autocommit.result
+++ b/mysql-test/suite/galera/r/galera_var_retry_autocommit.result
@@ -20,17 +20,25 @@ DROP TABLE t1;
 connection node_1;
 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB;
 SET SESSION wsrep_retry_autocommit = 1;
+SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit';
 SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue';
 INSERT INTO t1 (f1) VALUES (3);
 connection node_1a;
 SET DEBUG_SYNC = 'now WAIT_FOR before_cert';
 connection node_2;
 TRUNCATE TABLE t1;
-connection node_1;
+connection node_1a;
+SET DEBUG_SYNC = 'now WAIT_FOR wsrep_retry_autocommit_reached';
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 0
+SET DEBUG_SYNC = 'now SIGNAL wsrep_retry_autocommit_continue';
+connection node_1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
 SET DEBUG_SYNC = 'RESET';
+SET GLOBAL debug_dbug = NULL;
 DROP TABLE t1;
 connection node_1;
 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB;
@@ -64,6 +72,8 @@ SET SESSION wsrep_retry_autocommit = 64;
 SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit';
 SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue EXECUTE 64';
 INSERT INTO t1 VALUES (5);
+connection node_2;
+connection node_1;
 connection node_1;
 SELECT COUNT(*) FROM t1;
 COUNT(*)
diff --git a/mysql-test/suite/galera/t/galera_var_retry_autocommit.test b/mysql-test/suite/galera/t/galera_var_retry_autocommit.test
index bd10e448e06..c58eba1410e 100644
--- a/mysql-test/suite/galera/t/galera_var_retry_autocommit.test
+++ b/mysql-test/suite/galera/t/galera_var_retry_autocommit.test
@@ -25,6 +25,8 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu
 SET DEBUG_SYNC = 'now WAIT_FOR before_cert';
 
 --connection node_2
+--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
+--source include/wait_condition.inc
 TRUNCATE TABLE t1;
 
 --connection node_1
@@ -44,6 +46,7 @@ DROP TABLE t1;
 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB;
 
 SET SESSION wsrep_retry_autocommit = 1;
+SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit';
 SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue';
 --send INSERT INTO t1 (f1) VALUES (3)
 
@@ -51,14 +54,21 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu
 SET DEBUG_SYNC = 'now WAIT_FOR before_cert';
 
 --connection node_2
+--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
+--source include/wait_condition.inc
 TRUNCATE TABLE t1;
 
+--connection node_1a
+SET DEBUG_SYNC = 'now WAIT_FOR wsrep_retry_autocommit_reached';
+SELECT COUNT(*) FROM t1;
+SET DEBUG_SYNC = 'now SIGNAL wsrep_retry_autocommit_continue';
+
 --connection node_1
---error 0,ER_LOCK_DEADLOCK
 --reap
 SELECT COUNT(*) FROM t1;
 
 SET DEBUG_SYNC = 'RESET';
+SET GLOBAL debug_dbug = NULL;
 DROP TABLE t1;
 
 
@@ -79,6 +89,8 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu
 SET DEBUG_SYNC = 'now WAIT_FOR before_cert';
 
 --connection node_2
+--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
+--source include/wait_condition.inc
 TRUNCATE TABLE t1;
 
 --connection node_1a
@@ -114,6 +126,11 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu
 
 --send INSERT INTO t1 VALUES (5)
 
+--connection node_2
+--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
+--source include/wait_condition.inc
+
+--connection node_1
 --disable_query_log
 --disable_result_log
 --let $count = 64
diff --git a/mysql-test/suite/galera_sr/r/MDEV-30838.result b/mysql-test/suite/galera_sr/r/MDEV-30838.result
new file mode 100644
index 00000000000..6997b9c4d5d
--- /dev/null
+++ b/mysql-test/suite/galera_sr/r/MDEV-30838.result
@@ -0,0 +1,15 @@
+connection node_2;
+connection node_1;
+CREATE TABLE t1 (f1 INTEGER PRIMARY KEY);
+SET SESSION wsrep_trx_fragment_size=1;
+START TRANSACTION;
+INSERT INTO t1 VALUES(1);
+SET debug_dbug='+d,ib_create_table_fail_too_many_trx';
+INSERT INTO t1 VALUES(2);
+ERROR HY000: Error while appending streaming replication fragment
+COMMIT;
+SELECT * FROM t1;
+f1
+SET debug_dbug='-d,ib_create_table_fail_too_many_trx';
+DROP TABLE t1;
+CALL mtr.add_suppression("Error writing into mysql.wsrep_streaming_log: 177");
diff --git a/mysql-test/suite/galera_sr/t/MDEV-30838.test b/mysql-test/suite/galera_sr/t/MDEV-30838.test
new file mode 100644
index 00000000000..39ca7d2a375
--- /dev/null
+++ b/mysql-test/suite/galera_sr/t/MDEV-30838.test
@@ -0,0 +1,18 @@
+#
+# MDEV-30838 - Assertion `m_thd == _current_thd()' failed in
+# virtual int Wsrep_client_service::bf_rollback()
+#
+--source include/galera_cluster.inc
+--source include/have_debug_sync.inc
+CREATE TABLE t1 (f1 INTEGER PRIMARY KEY);
+SET SESSION wsrep_trx_fragment_size=1;
+START TRANSACTION;
+INSERT INTO t1 VALUES(1);
+SET debug_dbug='+d,ib_create_table_fail_too_many_trx';
+--error ER_ERROR_DURING_COMMIT
+INSERT INTO t1 VALUES(2);
+COMMIT;
+SELECT * FROM t1;
+SET debug_dbug='-d,ib_create_table_fail_too_many_trx';
+DROP TABLE t1;
+CALL mtr.add_suppression("Error writing into mysql.wsrep_streaming_log: 177");
diff --git a/mysql-test/suite/innodb_zip/r/page_size.result b/mysql-test/suite/innodb_zip/r/page_size.result
index 47effe06884..48b954c945b 100644
--- a/mysql-test/suite/innodb_zip/r/page_size.result
+++ b/mysql-test/suite/innodb_zip/r/page_size.result
@@ -608,4 +608,25 @@ SET GLOBAL innodb_compression_level=0;
 INSERT INTO t1 VALUES ('');
 SET GLOBAL innodb_compression_level= @save_innodb_compression_level;
 DROP TABLE t1;
+#
+# MDEV-31158 Assertion ...MTR_MEMO_X_LOCKED in btr_attach_half_pages()
+#
+SET @save_compression_level=@@GLOBAL.innodb_compression_level;
+SET GLOBAL innodb_compression_level=0;
+CREATE TEMPORARY TABLE t(a SERIAL, prefix VARBINARY(4), pad INT);
+INSERT INTO t(prefix, pad) VALUES
+(_binary 0xff,160),('',19),(_binary 0x0001,253),(_binary 0x0b11,169),
+(_binary 0x0b010001,23),(_binary 0x0b100001,251),(_binary 0x0d,163),
+(_binary 0xb3,254),(_binary 0x96,254),(_binary 0xeb,61),
+(_binary 0xf231,253),(_binary 0x1db0,253),(_binary 0x0005,101),
+(_binary 0x6370,253),(_binary 0x0b12,112),(_binary 0x0b010002,23),
+(_binary 0x0b100002,80),(_binary 0x181984,163),(_binary 0x181926,168),
+(_binary 0xe1,176),(_binary 0xe2,187),(_binary 0xe6,254),(_binary 0xbb,51),
+(_binary 0x1c,248),(_binary 0x8a,94),(_binary 0x14,254);
+CREATE TABLE u(a SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
+b VARBINARY(255), KEY(b)) ENGINE=InnoDB
+KEY_BLOCK_SIZE=1 ROW_FORMAT=COMPRESSED;
+INSERT INTO u SELECT a,CONCAT(prefix,REPEAT(chr(0),pad)) FROM t;
+DROP TABLE u, t;
+SET GLOBAL innodb_compression_level=@save_compression_level;
 # End of 10.6 tests
diff --git a/mysql-test/suite/innodb_zip/t/page_size.test b/mysql-test/suite/innodb_zip/t/page_size.test
index 16d65a139cf..3455ef8ed94 100644
--- a/mysql-test/suite/innodb_zip/t/page_size.test
+++ b/mysql-test/suite/innodb_zip/t/page_size.test
@@ -888,4 +888,28 @@ INSERT INTO t1 VALUES ('');
 SET GLOBAL innodb_compression_level= @save_innodb_compression_level;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-31158 Assertion ...MTR_MEMO_X_LOCKED in btr_attach_half_pages()
+--echo #
+--source include/have_innodb.inc
+
+SET @save_compression_level=@@GLOBAL.innodb_compression_level;
+SET GLOBAL innodb_compression_level=0;
+CREATE TEMPORARY TABLE t(a SERIAL, prefix VARBINARY(4), pad INT);
+INSERT INTO t(prefix, pad) VALUES
+(_binary 0xff,160),('',19),(_binary 0x0001,253),(_binary 0x0b11,169),
+(_binary 0x0b010001,23),(_binary 0x0b100001,251),(_binary 0x0d,163),
+(_binary 0xb3,254),(_binary 0x96,254),(_binary 0xeb,61),
+(_binary 0xf231,253),(_binary 0x1db0,253),(_binary 0x0005,101),
+(_binary 0x6370,253),(_binary 0x0b12,112),(_binary 0x0b010002,23),
+(_binary 0x0b100002,80),(_binary 0x181984,163),(_binary 0x181926,168),
+(_binary 0xe1,176),(_binary 0xe2,187),(_binary 0xe6,254),(_binary 0xbb,51),
+(_binary 0x1c,248),(_binary 0x8a,94),(_binary 0x14,254);
+CREATE TABLE u(a SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
+               b VARBINARY(255), KEY(b)) ENGINE=InnoDB
+               KEY_BLOCK_SIZE=1 ROW_FORMAT=COMPRESSED;
+INSERT INTO u SELECT a,CONCAT(prefix,REPEAT(chr(0),pad)) FROM t;
+DROP TABLE u, t;
+SET GLOBAL innodb_compression_level=@save_compression_level;
+
 --echo # End of 10.6 tests
diff --git a/mysql-test/suite/perfschema/r/digest_view.result b/mysql-test/suite/perfschema/r/digest_view.result
index d3988f99727..2d5c7ca0f42 100644
--- a/mysql-test/suite/perfschema/r/digest_view.result
+++ b/mysql-test/suite/perfschema/r/digest_view.result
@@ -191,17 +191,17 @@ SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR
 FROM performance_schema.events_statements_summary_by_digest
 ORDER BY DIGEST_TEXT;
 SCHEMA_NAME	DIGEST	DIGEST_TEXT	COUNT_STAR
-test	3bcc75e0f4658692e16837ead0c455ca	EXPLAIN SELECT * FROM `test` . `v1` 	1
-test	cc55af8221ef0bc8873e75cc704489ba	EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 	1
-test	bc0498551a492bd7c0ca92906bf86fb4	EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 	1
-test	76923336b6f4ffffd0d5854761ae1aec	EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 	1
-test	a65d479062ffbfe374b4cd823016a260	EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 	1
-test	d44b1e50ebfd05accacef4167534ad65	SELECT * FROM `test` . `v1` 	1
-test	0608cde507ef8fe35d1f68730e78f1e4	SELECT * FROM `test` . `v1` WHERE `a` = ? 	1
-test	5e241fdb866faef138cc81b26203ddff	SELECT * FROM `test` . `v1` WHERE `b` > ? 	1
-test	74fa770ce44994b3fe7b534d504f2d6d	SELECT `a` , `b` FROM `test` . `v1` 	1
-test	45ca2a985d179d98395b238cf253bbe2	SELECT `b` , `a` FROM `test` . `v1` 	1
-test	1cfa171590aa40ba23e14c92d6940ffc	TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 	1
+test	8b1406618d34996cd11d1796438c78b5	EXPLAIN SELECT * FROM `test` . `v1` 	1
+test	2c9e5d5b30d1690ba1a625afb4c42005	EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 	1
+test	2265269dbe1b17d1f309a63b8e56933f	EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 	1
+test	2df0babfc3c8ad27b4e3f99ad59bc938	EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 	1
+test	8607297e7ffe77aa19a9d60812c5a8fd	EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 	1
+test	9c94fee7865aa050201f6e67887fd0c8	SELECT * FROM `test` . `v1` 	1
+test	7c856ddf7b57d65f8124f39e8b81882e	SELECT * FROM `test` . `v1` WHERE `a` = ? 	1
+test	994b14d068c24edd8fd61b2f03663be2	SELECT * FROM `test` . `v1` WHERE `b` > ? 	1
+test	b3102e1f51878e35936d7d3fe2901839	SELECT `a` , `b` FROM `test` . `v1` 	1
+test	cb300dd6358987c5afe1a2b0022fdea0	SELECT `b` , `a` FROM `test` . `v1` 	1
+test	1b40b63f6a9dbffd146f0916fe4f5ed2	TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 	1
 DROP TABLE test.v1;
 CREATE VIEW test.v1 AS SELECT * FROM test.t1;
 EXPLAIN SELECT * from test.v1;
@@ -248,19 +248,19 @@ SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR
 FROM performance_schema.events_statements_summary_by_digest
 ORDER BY DIGEST_TEXT;
 SCHEMA_NAME	DIGEST	DIGEST_TEXT	COUNT_STAR
-test	630a0b16be33fcf549f4f467de557940	CREATE VIEW `test` . `v1` AS SELECT * FROM `test` . `t1` 	1
-test	efa438aa8b4ddbf4c19f9e4b345781b5	DROP TABLE `test` . `v1` 	1
-test	3bcc75e0f4658692e16837ead0c455ca	EXPLAIN SELECT * FROM `test` . `v1` 	2
-test	cc55af8221ef0bc8873e75cc704489ba	EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 	2
-test	bc0498551a492bd7c0ca92906bf86fb4	EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 	2
-test	76923336b6f4ffffd0d5854761ae1aec	EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 	2
-test	a65d479062ffbfe374b4cd823016a260	EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 	2
-test	d44b1e50ebfd05accacef4167534ad65	SELECT * FROM `test` . `v1` 	2
-test	0608cde507ef8fe35d1f68730e78f1e4	SELECT * FROM `test` . `v1` WHERE `a` = ? 	2
-test	5e241fdb866faef138cc81b26203ddff	SELECT * FROM `test` . `v1` WHERE `b` > ? 	2
-test	a3788cc4bc96d260cfb5c3ebdfc65c6d	SELECT SCHEMA_NAME , `DIGEST` , `DIGEST_TEXT` , `COUNT_STAR` FROM `performance_schema` . `events_statements_summary_by_digest` ORDER BY `DIGEST_TEXT` 	1
-test	74fa770ce44994b3fe7b534d504f2d6d	SELECT `a` , `b` FROM `test` . `v1` 	2
-test	45ca2a985d179d98395b238cf253bbe2	SELECT `b` , `a` FROM `test` . `v1` 	2
-test	1cfa171590aa40ba23e14c92d6940ffc	TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 	1
+test	a68fd555281a14d2809c3105e9cb2c90	CREATE VIEW `test` . `v1` AS SELECT * FROM `test` . `t1` 	1
+test	4baff8f96e4b6ec6cdbfef5b9c7a8b12	DROP TABLE `test` . `v1` 	1
+test	8b1406618d34996cd11d1796438c78b5	EXPLAIN SELECT * FROM `test` . `v1` 	2
+test	2c9e5d5b30d1690ba1a625afb4c42005	EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 	2
+test	2265269dbe1b17d1f309a63b8e56933f	EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 	2
+test	2df0babfc3c8ad27b4e3f99ad59bc938	EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 	2
+test	8607297e7ffe77aa19a9d60812c5a8fd	EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 	2
+test	9c94fee7865aa050201f6e67887fd0c8	SELECT * FROM `test` . `v1` 	2
+test	7c856ddf7b57d65f8124f39e8b81882e	SELECT * FROM `test` . `v1` WHERE `a` = ? 	2
+test	994b14d068c24edd8fd61b2f03663be2	SELECT * FROM `test` . `v1` WHERE `b` > ? 	2
+test	df38ce7f6e35972efe5a4ec57e48bf4d	SELECT SCHEMA_NAME , `DIGEST` , `DIGEST_TEXT` , `COUNT_STAR` FROM `performance_schema` . `events_statements_summary_by_digest` ORDER BY `DIGEST_TEXT` 	1
+test	b3102e1f51878e35936d7d3fe2901839	SELECT `a` , `b` FROM `test` . `v1` 	2
+test	cb300dd6358987c5afe1a2b0022fdea0	SELECT `b` , `a` FROM `test` . `v1` 	2
+test	1b40b63f6a9dbffd146f0916fe4f5ed2	TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 	1
 DROP VIEW test.v1;
 DROP TABLE test.t1;
diff --git a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
index a2d84753331..f0ceee5e214 100644
--- a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
+++ b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
@@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1
 ####################################
 SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long;
 event_name	digest	digest_text	sql_text
-statement/sql/select	70a97f8223477a66e716cfcc317193e2	SELECT ? + ? + 	SELECT ...
-statement/sql/truncate	ac4c7fc23914b333e9f40ce317816b8e	TRUNCATE TABLE 	truncat...
+statement/sql/select	ca9181d6d668396d467dd974f58a9402	SELECT ? + ? + 	SELECT ...
+statement/sql/truncate	4d2423d405bbcea8fa7bf9519c08dd8e	TRUNCATE TABLE 	truncat...
diff --git a/mysql-test/suite/rpl/r/rpl_parallel_seq.result b/mysql-test/suite/rpl/r/rpl_parallel_seq.result
new file mode 100644
index 00000000000..ae4041f470d
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_parallel_seq.result
@@ -0,0 +1,85 @@
+include/master-slave.inc
+[connection master]
+connection slave;
+include/stop_slave.inc
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+# MDEV-29621 the sequence engine binlog_row_image-full events
+#            MDL-deadlock on the parallel slave.
+connection master;
+CREATE SEQUENCE s1;
+SET @@session.binlog_row_image=FULL;
+SET @@session.debug_dbug="+d,binlog_force_commit_id";
+SET @commit_id=7;
+SET @@gtid_seq_no=100;
+SELECT NEXT VALUE FOR s1;
+NEXT VALUE FOR s1
+1
+INSERT INTO s1 VALUES(2, 1, 10, 1, 2, 1, 1, 0);
+SET @@session.debug_dbug="";
+connection slave;
+SET @@global.slave_parallel_threads=2;
+SET @@global.slave_parallel_mode=optimistic;
+SET @@global.debug_dbug="+d,hold_worker_on_schedule";
+include/start_slave.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+connection master;
+DROP SEQUENCE s1;
+connection slave;
+include/stop_slave.inc
+# Simulate buggy 10.3.36 master to prove the parallel applier
+# does not deadlock now at replaying the above master load.
+connection master;
+include/rpl_stop_server.inc [server_number=1]
+include/rpl_start_server.inc [server_number=1]
+connection slave;
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos;
+START SLAVE UNTIL MASTER_GTID_POS='0-1-102';
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+# Normal stop is expected
+include/wait_for_slave_to_stop.inc
+# MDEV-31077 ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order
+# The test proves ALTER-SEQUENCE binlogs first before the following transaction does so.
+connection slave;
+include/stop_slave.inc
+Warnings:
+Note	1255	Slave already has been stopped
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+SET @@global.gtid_strict_mode=1;
+connection master;
+RESET MASTER;
+CREATE TABLE ti (a INT) ENGINE=innodb;
+CREATE SEQUENCE s2 ENGINE=innodb;
+SET @@gtid_seq_no=100;
+ALTER SEQUENCE s2 restart with 1;
+INSERT INTO ti SET a=1;
+include/save_master_gtid.inc
+SELECT @@global.gtid_binlog_state "Master gtid state";
+Master gtid state
+0-1-101
+connection slave;
+include/start_slave.inc
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "no 100,101 yet in both";
+@@global.gtid_binlog_state	no 100,101 yet in both
+0-1-2	0-1-2
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+# Normal sync with master proves the fixes correct
+include/sync_with_master_gtid.inc
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "all through 101 have been committed";
+@@global.gtid_binlog_state	all through 101 have been committed
+0-1-101	0-1-101
+connection slave;
+include/stop_slave.inc
+SET debug_sync = RESET;
+SET @@global.slave_parallel_threads= 0;
+SET @@global.slave_parallel_mode= optimistic;
+SET @@global.debug_dbug = "";
+SET @@global.gtid_strict_mode=0;
+include/start_slave.inc
+connection master;
+DROP SEQUENCE s2;
+DROP TABLE ti;
+connection slave;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_binlog_index.test b/mysql-test/suite/rpl/t/rpl_binlog_index.test
index 95c49c3d574..6112affb3c4 100644
--- a/mysql-test/suite/rpl/t/rpl_binlog_index.test
+++ b/mysql-test/suite/rpl/t/rpl_binlog_index.test
@@ -17,10 +17,6 @@
 # BUG#12133 master.index file keeps mysqld from starting if bin log has been moved
 # BUG#42576 Relay logs in relay-log.info&localhost-relay-bin.index not processed after move
 
-source include/master-slave.inc;
-# There is no need to run this test case on all binlog format
-source include/have_binlog_format_row.inc;
-
 # Since this test relies heavily on filesystem operations (like
 # moving files around, backslashes and so forth) we avoid messing
 # around with windows access violations for not cluttering the 
@@ -28,6 +24,10 @@ source include/have_binlog_format_row.inc;
 # it is not 100% compliant.
 --source include/not_windows.inc
 
+source include/master-slave.inc;
+# There is no need to run this test case on all binlog format
+source include/have_binlog_format_row.inc;
+
 connection master;
 --let $master_datadir= `select @@datadir`
 connection slave;
diff --git a/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test b/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test
index 2cf184a3401..38537810906 100644
--- a/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test
+++ b/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test
@@ -2,10 +2,10 @@ if (`SELECT $PS_PROTOCOL != 0`)
 {
    --skip Test temporarily disabled for ps-protocol
 }
+--source include/no_valgrind_without_big.inc
 --let $rpl_topology=1->2
 --source include/rpl_init.inc
 --source include/have_innodb.inc
---source include/no_valgrind_without_big.inc
 
 --echo *** Test normal shutdown/restart of slave server configured as a GTID slave. ***
 
diff --git a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test
index 9c804d8206a..a27a50d0fc4 100644
--- a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test
+++ b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test
@@ -15,8 +15,8 @@
 #
 # (iii) master and slave tables do not differ
 #
--- source include/master-slave.inc
 -- source include/not_windows.inc
+-- source include/master-slave.inc
 
 SET SQL_LOG_BIN=0;
 CREATE DATABASE B37656;
diff --git a/mysql-test/suite/rpl/t/rpl_mdev12179.test b/mysql-test/suite/rpl/t/rpl_mdev12179.test
index c8b9e665747..aa0d05211ff 100644
--- a/mysql-test/suite/rpl/t/rpl_mdev12179.test
+++ b/mysql-test/suite/rpl/t/rpl_mdev12179.test
@@ -1,7 +1,7 @@
+--source include/no_valgrind_without_big.inc
 --source include/have_innodb.inc
 --let $rpl_topology=1->2
 --source include/rpl_init.inc
---source include/no_valgrind_without_big.inc
 
 --connection server_2
 call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase");
diff --git a/mysql-test/suite/rpl/t/rpl_mdev382.test b/mysql-test/suite/rpl/t/rpl_mdev382.test
index 093b7b92413..84e3c84982d 100644
--- a/mysql-test/suite/rpl/t/rpl_mdev382.test
+++ b/mysql-test/suite/rpl/t/rpl_mdev382.test
@@ -1,7 +1,7 @@
+--source include/not_windows.inc #unix shell escaping used for mysqlbinlog
 --source include/have_innodb.inc
 --source include/have_binlog_format_statement.inc
 --source include/master-slave.inc
---source include/not_windows.inc #unix shell escaping used for mysqlbinlog
 
 # MDEV-382: multiple SQL injections in replication code.
 
diff --git a/mysql-test/suite/rpl/t/rpl_parallel_seq.test b/mysql-test/suite/rpl/t/rpl_parallel_seq.test
new file mode 100644
index 00000000000..2a4fd96ff34
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_parallel_seq.test
@@ -0,0 +1,131 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+
+--connection slave
+--source include/stop_slave.inc
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+
+--echo # MDEV-29621 the sequence engine binlog_row_image-full events
+--echo #            MDL-deadlock on the parallel slave.
+--connection master
+CREATE SEQUENCE s1;
+SET @@session.binlog_row_image=FULL;
+SET @@session.debug_dbug="+d,binlog_force_commit_id";
+SET @commit_id=7;
+SET @@gtid_seq_no=100;
+SELECT NEXT VALUE FOR s1;
+INSERT INTO s1 VALUES(2, 1, 10, 1, 2, 1, 1, 0);
+SET @@session.debug_dbug="";
+
+--connection slave
+--let $slave_parallel_threads=`select @@global.slave_parallel_threads`
+--let $slave_parallel_mode=`select @@global.slave_parallel_mode`
+SET @@global.slave_parallel_threads=2;
+SET @@global.slave_parallel_mode=optimistic;
+SET @@global.debug_dbug="+d,hold_worker_on_schedule";
+--source include/start_slave.inc
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to start commit"
+--source include/wait_condition.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--connection master
+DROP SEQUENCE s1;
+--sync_slave_with_master
+--source include/stop_slave.inc
+
+--echo # Simulate buggy 10.3.36 master to prove the parallel applier
+--echo # does not deadlock now at replaying the above master load.
+--connection master
+--let $datadir= `SELECT @@datadir`
+
+--let $rpl_server_number= 1
+--source include/rpl_stop_server.inc
+
+--remove_file $datadir/master-bin.000001
+--copy_file $MYSQL_TEST_DIR/std_data/rpl/master-bin-seq_10.3.36.000001 $datadir/master-bin.000001
+
+--let $rpl_server_number= 1
+--source include/rpl_start_server.inc
+
+--source include/wait_until_connected_again.inc
+--save_master_pos
+
+--connection slave
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+
+--replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1
+eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos;
+
+START SLAVE UNTIL MASTER_GTID_POS='0-1-102';
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to commit"
+--source include/wait_condition.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--echo # Normal stop is expected
+--source include/wait_for_slave_to_stop.inc
+
+--echo # MDEV-31077 ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order
+--echo # The test proves ALTER-SEQUENCE binlogs first before the following transaction does so.
+
+--connection slave
+--source include/stop_slave.inc
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+--let $slave_gtid_strict_mode=`select @@global.gtid_strict_mode`
+SET @@global.gtid_strict_mode=1;
+--connection master
+RESET MASTER;
+
+# Load from master
+CREATE TABLE ti (a INT) ENGINE=innodb;
+CREATE SEQUENCE s2 ENGINE=innodb;
+
+SET @@gtid_seq_no=100;
+ALTER SEQUENCE s2 restart with 1;
+INSERT INTO ti SET a=1;
+--source include/save_master_gtid.inc
+SELECT @@global.gtid_binlog_state "Master gtid state";
+
+--connection slave
+--source include/start_slave.inc
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to commit"
+--source include/wait_condition.inc
+
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "no 100,101 yet in both";
+
+# DEBUG_DBUG extension point of hold_worker_on_schedule is reused
+# (gets deployed) in Sql_cmd_alter_sequence::execute.
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--echo # Normal sync with master proves the fixes correct
+--source include/sync_with_master_gtid.inc
+
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "all through 101 have been committed";
+
+#
+# MDEV-29621/MDEV-31077 clean up.
+#
+--connection slave
+--source include/stop_slave.inc
+
+SET debug_sync = RESET;
+--eval SET @@global.slave_parallel_threads= $slave_parallel_threads
+--eval SET @@global.slave_parallel_mode= $slave_parallel_mode
+       SET @@global.debug_dbug = "";
+--eval SET @@global.gtid_strict_mode=$slave_gtid_strict_mode
+--source include/start_slave.inc
+
+--connection master
+DROP SEQUENCE s2;
+DROP TABLE ti;
+
+--sync_slave_with_master
+
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test
index 44c04dd62d3..7decd130a43 100644
--- a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test
+++ b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test
@@ -3,10 +3,10 @@
 #  For details look into extra/rpl_tests/rpl_lower_case_table_names.test
 #
 
--- source include/master-slave.inc
--- source include/have_innodb.inc
 -- source include/not_windows.inc
+-- source include/have_innodb.inc
 -- source include/have_binlog_format_row.inc
+-- source include/master-slave.inc
 
 -- let $engine=InnoDB
 -- source include/rpl_lower_case_table_names.test
diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test
index 7a7e1c1e074..d4df9b4041b 100644
--- a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test
+++ b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test
@@ -1,7 +1,7 @@
+source include/no_valgrind_without_big.inc;
 source include/not_embedded.inc;
 source include/have_innodb.inc;
 source include/master-slave.inc;
-source include/no_valgrind_without_big.inc;
 
 let $engine_type= InnoDB;
 
diff --git a/mysql-test/suite/rpl/t/rpl_ssl.test b/mysql-test/suite/rpl/t/rpl_ssl.test
index 59a2af9f137..0420a6c8c2d 100644
--- a/mysql-test/suite/rpl/t/rpl_ssl.test
+++ b/mysql-test/suite/rpl/t/rpl_ssl.test
@@ -4,9 +4,9 @@
 # Please check all dependent tests after modifying it
 #
 
+source include/no_valgrind_without_big.inc;
 source include/have_ssl_communication.inc;
 source include/master-slave.inc;
-source include/no_valgrind_without_big.inc;
 
 # create a user for replication that requires ssl encryption
 connection master;
diff --git a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test
index 619b57994c2..3809cd89e4a 100644
--- a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test
+++ b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test
@@ -3,10 +3,10 @@
 #  For details look into extra/rpl_tests/rpl_lower_case_table_names.test
 #
 
+-- source include/not_windows.inc
+-- source include/have_innodb.inc
 -- source include/have_binlog_format_mixed_or_statement.inc
 -- source include/master-slave.inc
--- source include/have_innodb.inc
--- source include/not_windows.inc
 
 -- let $engine=InnoDB
 -- source include/rpl_lower_case_table_names.test
diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
index 80bd2d7af5f..7f2e1c6586c 100644
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
@@ -1,60 +1,60 @@
 set @@global.optimizer_switch=@@optimizer_switch;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 set global optimizer_switch=4101;
 set session optimizer_switch=2058;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set global optimizer_switch="index_merge_sort_union=on";
 set session optimizer_switch="index_merge=off";
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set session optimizer_switch="default";
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set optimizer_switch = replace(@@optimizer_switch, '=off', '=on');
 Warnings:
 Warning	1681	'engine_condition_pushdown=on' is deprecated and will be removed in a future release
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=on,hash_join_cardinality=on
 set global optimizer_switch=1.1;
 ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
 set global optimizer_switch=1e1;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 7b2dc9ae316..40da0c920be 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -665,13 +665,13 @@ COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	CORE_FILE
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BOOLEAN
-VARIABLE_COMMENT	write a core-file on crashes
+VARIABLE_COMMENT	Write core on crashes
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
 ENUM_VALUE_LIST	OFF,ON
 READ_ONLY	YES
-COMMAND_LINE_ARGUMENT	NULL
+COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	DATADIR
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	VARCHAR
@@ -2299,7 +2299,7 @@ VARIABLE_COMMENT	Fine-tune the optimizer behavior
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,default
+ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,hash_join_cardinality,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_TRACE
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index fc8169aba43..2e3173a2544 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -665,13 +665,13 @@ COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	CORE_FILE
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BOOLEAN
-VARIABLE_COMMENT	write a core-file on crashes
+VARIABLE_COMMENT	Write core on crashes
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
 ENUM_VALUE_LIST	OFF,ON
 READ_ONLY	YES
-COMMAND_LINE_ARGUMENT	NULL
+COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	DATADIR
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	VARCHAR
@@ -2459,7 +2459,7 @@ VARIABLE_COMMENT	Fine-tune the optimizer behavior
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,default
+ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,hash_join_cardinality,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_TRACE
diff --git a/mysql-test/suite/versioning/r/rpl.result b/mysql-test/suite/versioning/r/rpl.result
index a6fa6b3fca7..17372c63e99 100644
--- a/mysql-test/suite/versioning/r/rpl.result
+++ b/mysql-test/suite/versioning/r/rpl.result
@@ -164,4 +164,28 @@ update t1 set i = 0;
 connection slave;
 connection master;
 drop table t1;
+# check versioned -> versioned replication without any keys on duplicate records
+connection master;
+create table t1 (a INT) with system versioning;
+insert into t1 values (1);
+insert into t1 values (1);
+delete from t1;
+connection slave;
+include/diff_tables.inc [master:test.t1,slave:test.t1]
+connection master;
+drop table t1;
+connection slave;
+# check unversioned -> versioned replication with non-unique keys on duplicate records
+connection master;
+set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b));
+connection slave;
+set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)) with system versioning;
+connection master;
+insert into t1 values (1,1);
+insert into t1 values (1,1);
+delete from t1;
+connection slave;
+include/diff_tables.inc [master:test.t1,slave:test.t1]
+connection master;
+drop table t1;
 include/rpl_end.inc
diff --git a/mysql-test/suite/versioning/t/rpl.test b/mysql-test/suite/versioning/t/rpl.test
index b5be68feece..7d78b60e6fa 100644
--- a/mysql-test/suite/versioning/t/rpl.test
+++ b/mysql-test/suite/versioning/t/rpl.test
@@ -133,4 +133,38 @@ sync_slave_with_master;
 connection master;
 drop table t1;
 
+
+#
+#   MDEV-30430: Enabling system versioning on tables without primary key breaks replication
+#   Note that bugs are only present with row binlog format
+#
+--echo # check versioned -> versioned replication without any keys on duplicate records
+connection master;
+create table t1 (a INT) with system versioning;
+insert into t1 values (1);
+insert into t1 values (1);
+delete from t1;
+sync_slave_with_master;
+--let $diff_tables= master:test.t1,slave:test.t1
+--source include/diff_tables.inc
+connection master;
+drop table t1;
+sync_slave_with_master;
+
+--echo # check unversioned -> versioned replication with non-unique keys on duplicate records
+connection master;
+set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b));
+connection slave;
+set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)) with system versioning;
+connection master;
+insert into t1 values (1,1);
+insert into t1 values (1,1);
+delete from t1;
+sync_slave_with_master;
+--let $diff_tables= master:test.t1,slave:test.t1
+--source include/diff_tables.inc
+
+connection master;
+drop table t1;
+
 --source include/rpl_end.inc