Merge 10.1 into 10.2

30 files changed, 701 insertions, 749 deletions

diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result
index 6ac23c6ce80..686da923345 100644
--- a/mysql-test/r/analyze_format_json.result
+++ b/mysql-test/r/analyze_format_json.result
@@ -613,22 +613,24 @@ ANALYZE
     },
     "block-nl-join": {
       "table": {
-        "table_name": "<subquery2>",
+        "table_name": "t2",
         "access_type": "ALL",
-        "possible_keys": ["distinct_key"],
         "r_loops": 1,
         "rows": 2,
         "r_rows": 2,
         "r_total_time_ms": "REPLACED",
         "filtered": 100,
-        "r_filtered": 100
+        "r_filtered": 0,
+        "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))"
       },
       "buffer_type": "flat",
       "buffer_size": "256Kb",
       "join_type": "BNL",
-      "r_filtered": 100,
-      "materialized": {
-        "unique": 1,
+      "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))",
+      "r_filtered": null
+    },
+    "subqueries": [
+      {
         "query_block": {
           "select_id": 2,
           "r_loops": 1,
@@ -645,24 +647,7 @@ ANALYZE
           }
         }
       }
-    },
-    "block-nl-join": {
-      "table": {
-        "table_name": "t2",
-        "access_type": "ALL",
-        "r_loops": 1,
-        "rows": 2,
-        "r_rows": 2,
-        "r_total_time_ms": "REPLACED",
-        "filtered": 100,
-        "r_filtered": 100
-      },
-      "buffer_type": "incremental",
-      "buffer_size": "256Kb",
-      "join_type": "BNL",
-      "attached_condition": "t2.b = `<subquery2>`.a",
-      "r_filtered": 0
-    }
+    ]
   }
 }
 drop table t1,t2;
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index 50f99ec4bde..de681edf683 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -507,6 +507,7 @@ select t.a, count(*) from t1,t where t1.a=t.a  group by t.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	35	func	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
 explain
@@ -522,6 +523,7 @@ where t1.a=t.a  group by t.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	35	func	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
 # with clause in the specification of a derived table
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 7a451a312ca..5e6bcd0db37 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2916,5 +2916,64 @@ Handler_read_rnd_deleted	0
 Handler_read_rnd_next	27
 deallocate prepare stmt1;
 drop table t1,t2;
+#
+# Bug mdev-12670: mergeable derived / view with subqueries
+#                 subject to semi-join optimizations
+# (actually this is a 5.3 bug.)               
+#
+create table t1 (a int) engine=myisam;
+insert into t1  values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+explain select a from t1 where a in (select b from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	140	Using index; FirstMatch(t1)
+explain select * from (select a from t1 where a in (select b from t2)) t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	140	Using index; FirstMatch(t1)
+create view v1 as select a from t1 where a in (select b from t2);
+explain select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	140	Using index; FirstMatch(t1)
+drop view v1;
+drop table t1,t2;
+#
+# Bug mdev-12812: mergeable derived / view with subqueries
+#                 NOT subject to semi-join optimizations
+#
+CREATE TABLE t1 (c1 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('foo');
+CREATE TABLE t2 (c2 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('bar'),('qux'),('foo');
+SELECT STRAIGHT_JOIN * 
+FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq;
+c1
+foo
+foo
+EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * 
+FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq;
+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
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	select straight_join `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t2`.`c2` from `test`.`t2` where <cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`))
+DROP TABLE t1, t2;
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index 035d6f7ac1c..2b5bca2fb47 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -425,7 +425,7 @@ c1
 bb
 cc
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 18 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete
+Warning	1931	Query execution was interrupted. The query examined at least 17 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete
 select * from v1 LIMIT ROWS EXAMINED 11;
 c1
 bb
@@ -438,7 +438,8 @@ from (select * from t1
 where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0)) as tmp
 LIMIT ROWS EXAMINED 11;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	2	func	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
 select *
 from (select * from t1
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 08a5d019bab..17da3a18b4e 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -379,6 +379,7 @@ drop table t3, t4, t5;
 #
 # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch
 #
+set @optimizer_switch_save= @@optimizer_switch;
 create table t1 (c1 char(2) not null, c2 char(2));
 create table t2 (c3 char(2),          c4 char(2));
 insert into t1 values ('a1', 'b1');
@@ -400,6 +401,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
 c1	c2
 drop table t1, t2;
+set optimizer_switch= @optimizer_switch_save;
 #
 # MDEV-12673: cost-based choice between materialization and in-to-exists
 #
@@ -442,3 +444,44 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t3	const	PRIMARY	PRIMARY	4	const	1	
 2	DEPENDENT SUBQUERY	t2	index	NULL	i2	11	NULL	2	Using where; Using index
 DROP TABLE t1,t2,t3;
+#
+# MDEV-7599: in-to-exists chosen after min/max optimization
+#
+set @optimizer_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,6),(2,4), (8,9);
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+b	c
+EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	MATERIALIZED	t1	index	NULL	a	5	NULL	2	100.00	Using index
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,`test`.`t2`.`b` in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t2`.`b` ), <primary_index_lookup>(`test`.`t2`.`b` in <temporary table> on distinct_key where `test`.`t2`.`b` = `<subquery2>`.`MIN(a)`))))
+set optimizer_switch= 'materialization=off';
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+b	c
+EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	t1	index	NULL	a	5	NULL	2	100.00	Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t2`.`b` having trigcond(<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`))))))
+set optimizer_switch= @optimizer_switch_save;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('baz'),('qux');
+CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('abc'),('def');
+SELECT * FROM t1
+WHERE f1 = ALL( SELECT MAX(t2a.f2)
+FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3
+ON (f3 = t2b.f2) );
+f1
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 9ee5d8d2806..c39593e5213 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -1652,9 +1652,9 @@ CREATE VIEW v1 AS SELECT 1;
 EXPLAIN
 SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	<derived3>	system	NULL	NULL	NULL	NULL	1	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
 a	a
@@ -3072,4 +3072,97 @@ project_number
 aaa
 drop table t1, t2, t3;
 set optimizer_switch= @tmp_mdev6859;
+#
+# MDEV-12675: subquery subject to semi-join optimizations  
+#             in ON expression of INNER JOIN
+#
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1  values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	256	Using index; FirstMatch(t1)
+1	PRIMARY	t2	range	idx	idx	5	NULL	2	Using where; Using index; Using join buffer (flat, BNL join)
+explain 
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	256	Using index; FirstMatch(t1)
+1	PRIMARY	t2	range	idx	idx	5	NULL	2	Using where; Using index; Using join buffer (flat, BNL join)
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
+#
+# MDEV-12817: subquery NOT subject to semi-join optimizations  
+#             in ON expression of INNER JOIN
+#
+CREATE TABLE t1 (c1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3),(4);
+CREATE TABLE t3 (c3 int) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (5),(6);
+CREATE TABLE t4 (c4 int) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (7),(8);
+SELECT c1 
+FROM t1 
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+c1
+1
+2
+EXPLAIN EXTENDED SELECT c1 
+FROM t1 
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+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	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(select `test`.`t4`.`c4` from `test`.`t4` where 1 = `test`.`t4`.`c4`)))) where 1
+# mdev-12820
+SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+c1	c2	c4
+1	NULL	NULL
+2	NULL	NULL
+EXPLAIN EXTENDED SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+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	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(`test`.`t2`.`c2` = `test`.`t1`.`c1` and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where <cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))) where 1
+DROP TABLE t1,t2,t3,t4;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index a06742241cf..f0bf539b2fb 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1625,3 +1625,26 @@ i1
 DROP TABLE t1,t2,t3;
 set join_cache_level= @save_join_cache_level;
 set optimizer_switch=@save_optimizer_switch;
+#
+# mdev-7791: materialization of a semi-join subquery +
+#            RAND() in WHERE
+#            (materialized table is accessed last)
+#
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=on';
+create table t1(i int);
+insert into t1 values (1), (2), (3), (7), (9), (10);
+create table t2(i int);
+insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+select * from t1 where (rand() < 0) and i in (select i from t2);
+i
+explain extended
+select * from t1 where (rand() < 0) and i in (select i from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	10	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where rand() < 0
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index d20c6251f85..9d5ee4ba263 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -1665,9 +1665,9 @@ CREATE VIEW v1 AS SELECT 1;
 EXPLAIN
 SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	<derived3>	system	NULL	NULL	NULL	NULL	1	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
 a	a
@@ -3086,6 +3086,99 @@ project_number
 aaa
 drop table t1, t2, t3;
 set optimizer_switch= @tmp_mdev6859;
+#
+# MDEV-12675: subquery subject to semi-join optimizations  
+#             in ON expression of INNER JOIN
+#
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1  values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	256	Using index; FirstMatch(t1)
+1	PRIMARY	t2	range	idx	idx	5	NULL	2	Using where; Using index; Using join buffer (flat, BNL join)
+explain 
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	256	Using index; FirstMatch(t1)
+1	PRIMARY	t2	range	idx	idx	5	NULL	2	Using where; Using index; Using join buffer (flat, BNL join)
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
+#
+# MDEV-12817: subquery NOT subject to semi-join optimizations  
+#             in ON expression of INNER JOIN
+#
+CREATE TABLE t1 (c1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3),(4);
+CREATE TABLE t3 (c3 int) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (5),(6);
+CREATE TABLE t4 (c4 int) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (7),(8);
+SELECT c1 
+FROM t1 
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+c1
+1
+2
+EXPLAIN EXTENDED SELECT c1 
+FROM t1 
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+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	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(select `test`.`t4`.`c4` from `test`.`t4` where 1 = `test`.`t4`.`c4`)))) where 1
+# mdev-12820
+SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+c1	c2	c4
+1	NULL	NULL
+2	NULL	NULL
+EXPLAIN EXTENDED SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+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	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (incremental, BNL join)
+3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(`test`.`t2`.`c2` = `test`.`t1`.`c1` and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where <cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))) where 1
+DROP TABLE t1,t2,t3,t4;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 2c9567e3076..77fea060d32 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -2183,7 +2183,6 @@ HAVING i = 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
-4	DEPENDENT SUBQUERY	t2	unique_subquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
 Warnings:
-Note	1003	select 1 AS `1`,2 AS `2` union all select 1 AS `i`,count(0) AS `COUNT(*)` from dual where <in_optimizer>(1,<exists>(<primary_index_lookup>(<cache>(1) in t2 on PRIMARY))) group by 1 having 0
+Note	1003	select 1 AS `1`,2 AS `2` union all select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 1 group by 1 having 0
 DROP TABLE t1,t2;
diff --git a/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result b/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result
index feaede20f2a..a642ad20183 100644
--- a/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result
+++ b/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result
@@ -62,5 +62,4 @@ FOUND 1 /public/ in t7.ibd
 FOUND 1 /public/ in t8.ibd
 # t9 page compressed expecting NOT FOUND
 NOT FOUND /public/ in t9.ibd
-use test;
 drop database enctests;
diff --git a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test
index fdbd6c8da7c..574e0c3becc 100644
--- a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test
+++ b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test
@@ -8,9 +8,6 @@ SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_
 
 --disable_query_log
 --disable_warnings
-let $innodb_compression_algorithm_orig=`SELECT @@innodb_compression_algorithm`;
-let $innodb_file_format_orig = `SELECT @@innodb_file_format`;
-let $innodb_file_per_table_orig = `SELECT @@innodb_file_per_table`;
 let $encryption = `SELECT @@innodb_encrypt_tables`;
 SET GLOBAL innodb_file_format = `Barracuda`;
 SET GLOBAL innodb_file_per_table = ON;
@@ -88,15 +85,4 @@ SET GLOBAL innodb_encrypt_tables=ON;
 
 -- source include/start_mysqld.inc
 
-use test;
 drop database enctests;
-# reset system
-
---disable_query_log
---disable_warnings
-EVAL SET GLOBAL innodb_compression_algorithm = $innodb_compression_algorithm_orig;
-EVAL SET GLOBAL innodb_file_per_table = $innodb_file_per_table_orig;
-EVAL SET GLOBAL innodb_file_format = $innodb_file_format_orig;
-set global innodb_compression_algorithm = DEFAULT;
---enable_warnings
---enable_query_log
diff --git a/mysql-test/suite/innodb/r/innodb-page_compression_snappy.result b/mysql-test/suite/innodb/r/innodb-page_compression_snappy.result
index 442885755fd..83a17f678e4 100644
--- a/mysql-test/suite/innodb/r/innodb-page_compression_snappy.result
+++ b/mysql-test/suite/innodb/r/innodb-page_compression_snappy.result
@@ -1,430 +1,92 @@
-call mtr.add_suppression("InnoDB: Compression failed for space.*");
-set global innodb_compression_algorithm = 6;
-create table innodb_compressed(c1 int, b char(200)) engine=innodb row_format=compressed key_block_size=8;
-show warnings;
-Level	Code	Message
-create table innodb_normal (c1 int, b char(200)) engine=innodb;
-show warnings;
-Level	Code	Message
-create table innodb_page_compressed1 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=1;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed1;
-Table	Create Table
-innodb_page_compressed1	CREATE TABLE `innodb_page_compressed1` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=1
-create table innodb_page_compressed2 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=2;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed2;
-Table	Create Table
-innodb_page_compressed2	CREATE TABLE `innodb_page_compressed2` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=2
-create table innodb_page_compressed3 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=3;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed3;
-Table	Create Table
-innodb_page_compressed3	CREATE TABLE `innodb_page_compressed3` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=3
-create table innodb_page_compressed4 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=4;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed4;
-Table	Create Table
-innodb_page_compressed4	CREATE TABLE `innodb_page_compressed4` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=4
-create table innodb_page_compressed5 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=5;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed5;
-Table	Create Table
-innodb_page_compressed5	CREATE TABLE `innodb_page_compressed5` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=5
-create table innodb_page_compressed6 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=6;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed6;
-Table	Create Table
-innodb_page_compressed6	CREATE TABLE `innodb_page_compressed6` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=6
-create table innodb_page_compressed7 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=7;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed7;
-Table	Create Table
-innodb_page_compressed7	CREATE TABLE `innodb_page_compressed7` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=7
-create table innodb_page_compressed8 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=8;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed8;
-Table	Create Table
-innodb_page_compressed8	CREATE TABLE `innodb_page_compressed8` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=8
-create table innodb_page_compressed9 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=9;
-show warnings;
-Level	Code	Message
-show create table innodb_page_compressed9;
-Table	Create Table
-innodb_page_compressed9	CREATE TABLE `innodb_page_compressed9` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=9
-create procedure innodb_insert_proc (repeat_count int)
-begin
-declare current_num int;
-set current_num = 0;
-while current_num < repeat_count do
-insert into innodb_normal values(current_num,'aaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbccccccccccccccccccccccc');
-set current_num = current_num + 1;
-end while;
-end//
-commit;
-set autocommit=0;
-call innodb_insert_proc(5000);
-commit;
-set autocommit=1;
-select count(*) from innodb_normal;
-count(*)
-5000
-insert into innodb_compressed select * from innodb_normal;
-insert into innodb_page_compressed1 select * from innodb_normal;
-insert into innodb_page_compressed2 select * from innodb_normal;
-insert into innodb_page_compressed3 select * from innodb_normal;
-insert into innodb_page_compressed4 select * from innodb_normal;
-insert into innodb_page_compressed5 select * from innodb_normal;
-insert into innodb_page_compressed6 select * from innodb_normal;
-insert into innodb_page_compressed7 select * from innodb_normal;
-insert into innodb_page_compressed8 select * from innodb_normal;
-insert into innodb_page_compressed9 select * from innodb_normal;
-commit;
-select count(*) from innodb_compressed;
-count(*)
-5000
+call mtr.add_suppression("InnoDB: Compression failed for space [0-9]+ name test/innodb_page_compressed[0-9] len [0-9]+ err 2 write_size [0-9]+.");
+set global innodb_compression_algorithm = snappy;
+set global innodb_file_format = `Barracuda`;
+set global innodb_file_per_table = on;
+create table innodb_normal (c1 int not null auto_increment primary key, b char(200)) engine=innodb;
+create table innodb_page_compressed1 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=1;
+create table innodb_page_compressed2 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=2;
+create table innodb_page_compressed3 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=3;
+create table innodb_page_compressed4 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=4;
+create table innodb_page_compressed5 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=5;
+create table innodb_page_compressed6 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=6;
+create table innodb_page_compressed7 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=7;
+create table innodb_page_compressed8 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=8;
+create table innodb_page_compressed9 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=9;
 select count(*) from innodb_page_compressed1;
 count(*)
-5000
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-count(*)
-5000
-alter table innodb_normal page_compressed=1 page_compression_level=8;
-show warnings;
-Level	Code	Message
-show create table innodb_normal;
-Table	Create Table
-innodb_normal	CREATE TABLE `innodb_normal` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=8
-alter table innodb_compressed row_format=default page_compressed=1 page_compression_level=8 key_block_size=0;
-show warnings;
-Level	Code	Message
-show create table innodb_compressed;
-Table	Create Table
-innodb_compressed	CREATE TABLE `innodb_compressed` (
-  `c1` int(11) DEFAULT NULL,
-  `b` char(200) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `page_compression_level`=8
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-select count(*) from innodb_compressed;
-count(*)
-5000
+10000
+select count(*) from innodb_page_compressed3;
+count(*)
+10000
+select count(*) from innodb_page_compressed4;
+count(*)
+10000
+select count(*) from innodb_page_compressed5;
+count(*)
+10000
+select count(*) from innodb_page_compressed6;
+count(*)
+10000
+select count(*) from innodb_page_compressed6;
+count(*)
+10000
+select count(*) from innodb_page_compressed7;
+count(*)
+10000
+select count(*) from innodb_page_compressed8;
+count(*)
+10000
+select count(*) from innodb_page_compressed9;
+count(*)
+10000
+# innodb_normal expected FOUND
+FOUND 24084 /AaAaAaAa/ in innodb_normal.ibd
+# innodb_page_compressed1 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed1.ibd
+# innodb_page_compressed2 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed2.ibd
+# innodb_page_compressed3 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed3.ibd
+# innodb_page_compressed4 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed4.ibd
+# innodb_page_compressed5 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed5.ibd
+# innodb_page_compressed6 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed6.ibd
+# innodb_page_compressed7 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed7.ibd
+# innodb_page_compressed8 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed8.ibd
+# innodb_page_compressed9 page compressed expected NOT FOUND
+NOT FOUND /AaAaAaAa/ in innodb_page_compressed9.ibd
 select count(*) from innodb_page_compressed1;
 count(*)
-5000
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-count(*)
-5000
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-select count(*) from innodb_compressed;
-count(*)
-5000
-select count(*) from innodb_page_compressed1;
-count(*)
-5000
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-count(*)
-5000
-set global innodb_compression_algorithm = 1;
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-commit;
-select count(*) from innodb_compressed;
-count(*)
-5000
-select count(*) from innodb_page_compressed1;
-count(*)
-5000
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-count(*)
-5000
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-select count(*) from innodb_compressed;
-count(*)
-5000
-select count(*) from innodb_page_compressed1;
-count(*)
-5000
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-count(*)
-5000
-set global innodb_compression_algorithm = 0;
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-commit;
-select count(*) from innodb_compressed;
-count(*)
-5000
-select count(*) from innodb_page_compressed1;
-count(*)
-5000
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-count(*)
-5000
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-select count(*) from innodb_compressed;
-count(*)
-5000
-select count(*) from innodb_page_compressed1;
-count(*)
-5000
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-count(*)
-5000
-select count(*) from innodb_page_compressed2 where c1 < 500000;
+10000
+select count(*) from innodb_page_compressed3;
 count(*)
-5000
-select count(*) from innodb_page_compressed3 where c1 < 500000;
+10000
+select count(*) from innodb_page_compressed4;
 count(*)
-5000
-select count(*) from innodb_page_compressed4 where c1 < 500000;
+10000
+select count(*) from innodb_page_compressed5;
 count(*)
-5000
-select count(*) from innodb_page_compressed5 where c1 < 500000;
+10000
+select count(*) from innodb_page_compressed6;
 count(*)
-5000
-select count(*) from innodb_page_compressed6 where c1 < 500000;
+10000
+select count(*) from innodb_page_compressed6;
 count(*)
-5000
-select count(*) from innodb_page_compressed7 where c1 < 500000;
+10000
+select count(*) from innodb_page_compressed7;
 count(*)
-5000
-select count(*) from innodb_page_compressed8 where c1 < 500000;
+10000
+select count(*) from innodb_page_compressed8;
 count(*)
-5000
-select count(*) from innodb_page_compressed9 where c1 < 500000;
+10000
+select count(*) from innodb_page_compressed9;
 count(*)
-5000
-drop procedure innodb_insert_proc;
+10000
 drop table innodb_normal;
-drop table innodb_compressed;
 drop table innodb_page_compressed1;
 drop table innodb_page_compressed2;
 drop table innodb_page_compressed3;
@@ -434,3 +96,4 @@ drop table innodb_page_compressed6;
 drop table innodb_page_compressed7;
 drop table innodb_page_compressed8;
 drop table innodb_page_compressed9;
+#done
diff --git a/mysql-test/suite/innodb/t/innodb-page_compression_snappy.test b/mysql-test/suite/innodb/t/innodb-page_compression_snappy.test
index b90d15f1fa3..532ec294d28 100644
--- a/mysql-test/suite/innodb/t/innodb-page_compression_snappy.test
+++ b/mysql-test/suite/innodb/t/innodb-page_compression_snappy.test
@@ -1,244 +1,13 @@
 -- source include/have_innodb.inc
 -- source include/have_innodb_snappy.inc
+--source include/not_embedded.inc
 
-call mtr.add_suppression("InnoDB: Compression failed for space.*");
-
-let $innodb_compression_algorithm_orig=`select @@innodb_compression_algorithm`;
+call mtr.add_suppression("InnoDB: Compression failed for space [0-9]+ name test/innodb_page_compressed[0-9] len [0-9]+ err 2 write_size [0-9]+.");
 
 # snappy
-set global innodb_compression_algorithm = 6;
-
-create table innodb_compressed(c1 int, b char(200)) engine=innodb row_format=compressed key_block_size=8;
-show warnings;
-create table innodb_normal (c1 int, b char(200)) engine=innodb;
-show warnings;
-create table innodb_page_compressed1 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=1;
-show warnings;
-show create table innodb_page_compressed1;
-create table innodb_page_compressed2 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=2;
-show warnings;
-show create table innodb_page_compressed2;
-create table innodb_page_compressed3 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=3;
-show warnings;
-show create table innodb_page_compressed3;
-create table innodb_page_compressed4 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=4;
-show warnings;
-show create table innodb_page_compressed4;
-create table innodb_page_compressed5 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=5;
-show warnings;
-show create table innodb_page_compressed5;
-create table innodb_page_compressed6 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=6;
-show warnings;
-show create table innodb_page_compressed6;
-create table innodb_page_compressed7 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=7;
-show warnings;
-show create table innodb_page_compressed7;
-create table innodb_page_compressed8 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=8;
-show warnings;
-show create table innodb_page_compressed8;
-create table innodb_page_compressed9 (c1 int, b char(200)) engine=innodb page_compressed=1 page_compression_level=9;
-show warnings;
-show create table innodb_page_compressed9;
-delimiter //;
-create procedure innodb_insert_proc (repeat_count int)
-begin
-  declare current_num int;
-  set current_num = 0;
-  while current_num < repeat_count do
-    insert into innodb_normal values(current_num,'aaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbccccccccccccccccccccccc');
-    set current_num = current_num + 1;
-  end while;
-end//
-delimiter ;//
-commit;
-
-set autocommit=0;
-call innodb_insert_proc(5000);
-commit;
-set autocommit=1;
-select count(*) from innodb_normal;
-insert into innodb_compressed select * from innodb_normal;
-insert into innodb_page_compressed1 select * from innodb_normal;
-insert into innodb_page_compressed2 select * from innodb_normal;
-insert into innodb_page_compressed3 select * from innodb_normal;
-insert into innodb_page_compressed4 select * from innodb_normal;
-insert into innodb_page_compressed5 select * from innodb_normal;
-insert into innodb_page_compressed6 select * from innodb_normal;
-insert into innodb_page_compressed7 select * from innodb_normal;
-insert into innodb_page_compressed8 select * from innodb_normal;
-insert into innodb_page_compressed9 select * from innodb_normal;
-commit;
-select count(*) from innodb_compressed;
-select count(*) from innodb_page_compressed1;
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-
-alter table innodb_normal page_compressed=1 page_compression_level=8;
-show warnings;
-show create table innodb_normal;
-alter table innodb_compressed row_format=default page_compressed=1 page_compression_level=8 key_block_size=0;
-show warnings;
-show create table innodb_compressed;
-
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-select count(*) from innodb_compressed;
-select count(*) from innodb_page_compressed1;
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-
---source include/restart_mysqld.inc
-
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-select count(*) from innodb_compressed;
-select count(*) from innodb_page_compressed1;
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-
-# zlib
-set global innodb_compression_algorithm = 1;
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-commit;
-select count(*) from innodb_compressed;
-select count(*) from innodb_page_compressed1;
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-
---source include/restart_mysqld.inc
-
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-select count(*) from innodb_compressed;
-select count(*) from innodb_page_compressed1;
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-
-# none
-set global innodb_compression_algorithm = 0;
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-commit;
-select count(*) from innodb_compressed;
-select count(*) from innodb_page_compressed1;
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-select count(*) from innodb_page_compressed9 where c1 < 500000;
-
---source include/restart_mysqld.inc
-
-update innodb_page_compressed1 set c1 = c1 + 1;
-update innodb_page_compressed2 set c1 = c1 + 1;
-update innodb_page_compressed3 set c1 = c1 + 1;
-update innodb_page_compressed4 set c1 = c1 + 1;
-update innodb_page_compressed5 set c1 = c1 + 1;
-update innodb_page_compressed6 set c1 = c1 + 1;
-update innodb_page_compressed7 set c1 = c1 + 1;
-update innodb_page_compressed8 set c1 = c1 + 1;
-update innodb_page_compressed9 set c1 = c1 + 1;
-select count(*) from innodb_compressed;
-select count(*) from innodb_page_compressed1;
-select count(*) from innodb_page_compressed1 where c1 < 500000;
-select count(*) from innodb_page_compressed2 where c1 < 500000;
-select count(*) from innodb_page_compressed3 where c1 < 500000;
-select count(*) from innodb_page_compressed4 where c1 < 500000;
-select count(*) from innodb_page_compressed5 where c1 < 500000;
-select count(*) from innodb_page_compressed6 where c1 < 500000;
-select count(*) from innodb_page_compressed7 where c1 < 500000;
-select count(*) from innodb_page_compressed8 where c1 < 500000;
-select count(*) from innodb_page_compressed9 where c1 < 500000;
+set global innodb_compression_algorithm = snappy;
 
-drop procedure innodb_insert_proc;
-drop table innodb_normal;
-drop table innodb_compressed;
-drop table innodb_page_compressed1;
-drop table innodb_page_compressed2;
-drop table innodb_page_compressed3;
-drop table innodb_page_compressed4;
-drop table innodb_page_compressed5;
-drop table innodb_page_compressed6;
-drop table innodb_page_compressed7;
-drop table innodb_page_compressed8;
-drop table innodb_page_compressed9;
+# All page compression test use the same
+--source include/innodb-page-compression.inc
 
-# reset system
---disable_query_log
-EVAL SET GLOBAL innodb_compression_algorithm = $innodb_compression_algorithm_orig;
---enable_query_log
+-- echo #done
diff --git a/mysql-test/suite/mariabackup/suite.pm b/mysql-test/suite/mariabackup/suite.pm
index 8eecd4e8018..26d5c06cdad 100644
--- a/mysql-test/suite/mariabackup/suite.pm
+++ b/mysql-test/suite/mariabackup/suite.pm
@@ -21,11 +21,6 @@ $ENV{XBSTREAM}= ::mtr_exe_maybe_exists(
       "$::bindir/extra/mariabackup/$::opt_vs_config/mbstream",
       "$::path_client_bindir/mbstream");
 
-my $tar_version = `tar --version 2>&1`;
-$ENV{HAVE_TAR} = $! ? 0: 1;
-my $mariabackup_help=`$mariabackup_exe --help 2>&1`;
-$ENV{HAVE_XTRABACKUP_TAR_SUPPORT} =  (index($mariabackup_help,"'tar'") == -1) ? 0 : 1;
-
 $ENV{INNOBACKUPEX}= "$mariabackup_exe --innobackupex";
 
 sub skip_combinations {
diff --git a/mysql-test/suite/mariabackup/tar.result b/mysql-test/suite/mariabackup/tar.result
deleted file mode 100644
index bbb546d7add..00000000000
--- a/mysql-test/suite/mariabackup/tar.result
+++ /dev/null
@@ -1,12 +0,0 @@
-CREATE TABLE t(i INT) ENGINE INNODB;
-INSERT INTO t VALUES(1);
-# xtrabackup backup
-# xtrabackup prepare
-# shutdown server
-# remove datadir
-# xtrabackup move back
-# restart server
-SELECT * FROM t;
-i
-1
-DROP TABLE t;
diff --git a/mysql-test/suite/mariabackup/tar.test b/mysql-test/suite/mariabackup/tar.test
deleted file mode 100644
index 3938d597e05..00000000000
--- a/mysql-test/suite/mariabackup/tar.test
+++ /dev/null
@@ -1,30 +0,0 @@
-if (`select $HAVE_TAR = 0`) 
-{
-  --skip No tar
-}
-if (`select $HAVE_XTRABACKUP_TAR_SUPPORT = 0`)
-{
-  --skip Compiled without libarchive
-}
-
-
-CREATE TABLE t(i INT) ENGINE INNODB;
-INSERT INTO t VALUES(1);
-
-echo # xtrabackup backup;
-let $targetdir=$MYSQLTEST_VARDIR/tmp/backup;
-let $streamfile=$MYSQLTEST_VARDIR/tmp/backup.tar;
-mkdir $targetdir;
-
-
-exec $XTRABACKUP "--defaults-file=$MYSQLTEST_VARDIR/my.cnf" --backup --stream=tar > $streamfile 2>$targetdir/backup_stream.log;
---disable_result_log
-exec tar -C $targetdir -x < $streamfile;
-echo # xtrabackup prepare;
-exec $XTRABACKUP --prepare --target-dir=$targetdir;
-
--- source include/restart_and_restore.inc
---enable_result_log
-SELECT * FROM t;
-DROP TABLE t;
-rmdir $targetdir;
diff --git a/mysql-test/suite/mariabackup/xbstream.test b/mysql-test/suite/mariabackup/xbstream.test
index 06e5685276c..f2b4704a87e 100644
--- a/mysql-test/suite/mariabackup/xbstream.test
+++ b/mysql-test/suite/mariabackup/xbstream.test
@@ -9,7 +9,7 @@ echo # xtrabackup backup to stream;
 exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf  --backup  --stream=xbstream > $streamfile 2>$targetdir/backup_stream.log;
 echo # xbstream extract;
 --disable_result_log
-exec $XBSTREAM -x -C $targetdir --parallel=16 < $streamfile;
+exec $XBSTREAM -x -C $targetdir  < $streamfile;
 
 echo # xtrabackup prepare;
 exec $XTRABACKUP --prepare --target-dir=$targetdir;
diff --git a/mysql-test/suite/perfschema/r/start_server_1_digest.result b/mysql-test/suite/perfschema/r/start_server_1_digest.result
new file mode 100644
index 00000000000..cf07022d344
--- /dev/null
+++ b/mysql-test/suite/perfschema/r/start_server_1_digest.result
@@ -0,0 +1,7 @@
+SELECT "Digest table has a size 1 and is full already." as use_case;
+use_case
+Digest table has a size 1 and is full already.
+select SCHEMA_NAME, DIGEST, DIGEST_TEXT
+from performance_schema.events_statements_summary_by_digest;
+SCHEMA_NAME	DIGEST	DIGEST_TEXT
+NULL	NULL	NULL
diff --git a/mysql-test/suite/perfschema/t/start_server_1_digest-master.opt b/mysql-test/suite/perfschema/t/start_server_1_digest-master.opt
new file mode 100644
index 00000000000..e59be3eb02f
--- /dev/null
+++ b/mysql-test/suite/perfschema/t/start_server_1_digest-master.opt
@@ -0,0 +1 @@
+--performance-schema-digests-size=1
diff --git a/mysql-test/suite/perfschema/t/start_server_1_digest.test b/mysql-test/suite/perfschema/t/start_server_1_digest.test
new file mode 100644
index 00000000000..998d9a5eebe
--- /dev/null
+++ b/mysql-test/suite/perfschema/t/start_server_1_digest.test
@@ -0,0 +1,15 @@
+# -----------------------------------------------------------------------
+# Tests for the performance schema statement Digests.
+# -----------------------------------------------------------------------
+
+--source include/not_embedded.inc
+--source include/have_perfschema.inc
+--source include/no_protocol.inc
+
+SELECT "Digest table has a size 1 and is full already." as use_case;
+
+select SCHEMA_NAME, DIGEST, DIGEST_TEXT
+  from performance_schema.events_statements_summary_by_digest;
+
+
+
diff --git a/mysql-test/suite/rpl/r/rpl_mdev-11092.result b/mysql-test/suite/rpl/r/rpl_mdev-11092.result
new file mode 100644
index 00000000000..90b809477b2
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_mdev-11092.result
@@ -0,0 +1,21 @@
+include/master-slave.inc
+[connection master]
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+call mtr.add_suppression("Slave SQL: The incident LOST_EVENTS occured on the master. .*");
+SET GLOBAL max_binlog_cache_size = 4096;
+SET GLOBAL binlog_cache_size = 4096;
+SET GLOBAL max_binlog_stmt_cache_size = 4096;
+SET GLOBAL binlog_stmt_cache_size = 4096;
+disconnect master;
+connect  master,127.0.0.1,root,,test,$MASTER_MYPORT,;
+CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MYISAM;
+connection master;
+ERROR HY000: Writing one row to the row-based binary log failed
+include/wait_for_slave_sql_error_and_skip.inc [errno=1590]
+connection master;
+SET GLOBAL max_binlog_cache_size= ORIGINAL_VALUE;
+SET GLOBAL binlog_cache_size= ORIGINAL_VALUE;
+SET GLOBAL max_binlog_stmt_cache_size= ORIGINAL_VALUE;
+SET GLOBAL binlog_stmt_cache_size= ORIGINAL_VALUE;
+DROP TABLE t1;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_mdev-11092.opt b/mysql-test/suite/rpl/t/rpl_mdev-11092.opt
new file mode 100644
index 00000000000..7f1d270d29f
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_mdev-11092.opt
@@ -0,0 +1 @@
+--binlog_checksum=1 --binlog-annotate-row-events=1
diff --git a/mysql-test/suite/rpl/t/rpl_mdev-11092.test b/mysql-test/suite/rpl/t/rpl_mdev-11092.test
new file mode 100644
index 00000000000..c8b2b7f2ad1
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_mdev-11092.test
@@ -0,0 +1,53 @@
+--source include/have_innodb.inc
+--source include/master-slave.inc
+--source include/not_embedded.inc
+--source include/not_windows.inc
+--source include/have_binlog_format_row.inc
+
+########################################################################################
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+call mtr.add_suppression("Slave SQL: The incident LOST_EVENTS occured on the master. .*");
+
+let $old_max_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_cache_size", Value, 1);
+let $old_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_cache_size", Value, 1);
+let $old_max_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_stmt_cache_size", Value, 1);
+let $old_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_stmt_cache_size", Value, 1);
+
+SET GLOBAL max_binlog_cache_size = 4096;
+SET GLOBAL binlog_cache_size = 4096;
+SET GLOBAL max_binlog_stmt_cache_size = 4096;
+SET GLOBAL binlog_stmt_cache_size = 4096;
+disconnect master;
+connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
+
+CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MYISAM;
+
+let $data = `select concat('"', repeat('a',2000), '"')`;
+
+connection master;
+
+--disable_query_log
+--error ER_BINLOG_ROW_LOGGING_FAILED
+eval INSERT INTO t1 (a, data) VALUES (2,
+       CONCAT($data, $data, $data, $data, $data, $data));
+--enable_query_log
+
+# Incident event
+# 1590=ER_SLAVE_INCIDENT
+--let $slave_sql_errno= 1590
+--source include/wait_for_slave_sql_error_and_skip.inc
+
+connection master;
+
+--replace_result $old_max_binlog_cache_size ORIGINAL_VALUE
+--eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size
+--replace_result $old_binlog_cache_size ORIGINAL_VALUE
+--eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size
+--replace_result $old_max_binlog_stmt_cache_size ORIGINAL_VALUE
+--eval SET GLOBAL max_binlog_stmt_cache_size= $old_max_binlog_stmt_cache_size
+--replace_result $old_binlog_stmt_cache_size ORIGINAL_VALUE
+--eval SET GLOBAL binlog_stmt_cache_size= $old_binlog_stmt_cache_size
+
+DROP TABLE t1;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff-disabled b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff-disabled
index 858df585a7b..f5ce4d77c6b 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff-disabled
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff-disabled
@@ -1218,8 +1218,8 @@
  COMMAND_LINE_ARGUMENT	OPTIONAL
  VARIABLE_NAME	INNODB_VERSION
  SESSION_VALUE	NULL
--GLOBAL_VALUE	5.6.35
-+GLOBAL_VALUE	5.6.35-80.0
+-GLOBAL_VALUE	5.6.36
++GLOBAL_VALUE	5.6.36-82.0
  GLOBAL_VALUE_ORIGIN	COMPILE-TIME
  DEFAULT_VALUE	NULL
  VARIABLE_SCOPE	GLOBAL
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff-disabled b/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff-disabled
index d801270c6b6..9199653b460 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff-disabled
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff-disabled
@@ -661,8 +661,8 @@
  COMMAND_LINE_ARGUMENT	OPTIONAL
  VARIABLE_NAME	INNODB_VERSION
  SESSION_VALUE	NULL
--GLOBAL_VALUE	5.6.35
-+GLOBAL_VALUE	5.6.35-80.0
+-GLOBAL_VALUE	5.6.36
++GLOBAL_VALUE	5.6.36-82.0
  GLOBAL_VALUE_ORIGIN	COMPILE-TIME
  DEFAULT_VALUE	NULL
  VARIABLE_SCOPE	GLOBAL
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 042f35f04a8..1cd91a3a61b 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -3074,7 +3074,7 @@ READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	INNODB_VERSION
 SESSION_VALUE	NULL
-GLOBAL_VALUE	5.7.14
+GLOBAL_VALUE	5.7.18
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
 DEFAULT_VALUE	NULL
 VARIABLE_SCOPE	GLOBAL
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 3a18e9a086e..9b0cf9dca7d 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1899,6 +1899,56 @@ deallocate prepare stmt1;
 
 drop table t1,t2;
 
+--echo #
+--echo # Bug mdev-12670: mergeable derived / view with subqueries
+--echo #                 subject to semi-join optimizations
+--echo # (actually this is a 5.3 bug.)               
+--echo #
+
+create table t1 (a int) engine=myisam;
+insert into t1  values (5),(3),(2),(7),(2),(5),(1); 
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+
+explain select a from t1 where a in (select b from t2); 
+explain select * from (select a from t1 where a in (select b from t2)) t;
+create view v1 as select a from t1 where a in (select b from t2);
+explain select * from v1;
+
+drop view v1;
+drop table t1,t2;
+
+--echo #
+--echo # Bug mdev-12812: mergeable derived / view with subqueries
+--echo #                 NOT subject to semi-join optimizations
+--echo #
+
+CREATE TABLE t1 (c1 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('foo');
+
+CREATE TABLE t2 (c2 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('bar'),('qux'),('foo');
+
+let $q=
+SELECT STRAIGHT_JOIN * 
+  FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq;
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+
+DROP TABLE t1, t2;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index 316ac707bef..9e3ac603ec6 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -406,6 +406,8 @@ drop table t3, t4, t5;
 --echo # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch
 --echo #
 
+set @optimizer_switch_save= @@optimizer_switch;
+
 create table t1 (c1 char(2) not null, c2 char(2));
 create table t2 (c3 char(2),          c4 char(2));
 
@@ -425,6 +427,8 @@ select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
 
 drop table t1, t2;
 
+set optimizer_switch= @optimizer_switch_save;
+
 --echo #
 --echo # MDEV-12673: cost-based choice between materialization and in-to-exists
 --echo #
@@ -463,3 +467,43 @@ SELECT * FROM t1 WHERE i1 NOT IN (
 );
 
 DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-7599: in-to-exists chosen after min/max optimization
+--echo #
+
+set @optimizer_switch_save= @@optimizer_switch;
+
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,6),(2,4), (8,9);
+
+let $q=
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+set optimizer_switch= 'materialization=off';
+eval $q;
+eval EXPLAIN EXTENDED $q;
+set optimizer_switch= @optimizer_switch_save;
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('bar');
+
+CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('baz'),('qux');
+
+CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('abc'),('def');
+
+SELECT * FROM t1
+  WHERE f1 = ALL( SELECT MAX(t2a.f2)
+                  FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3
+                  ON (f3 = t2b.f2) );
+
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index b6d6e0a5172..f90f1e2e927 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2773,5 +2773,77 @@ WHERE ( SELECT z.country
 drop table t1, t2, t3;
 set optimizer_switch= @tmp_mdev6859;
 
+--echo #
+--echo # MDEV-12675: subquery subject to semi-join optimizations  
+--echo #             in ON expression of INNER JOIN
+--echo #
+
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1  values (5),(3),(2),(7),(2),(5),(1); 
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+explain 
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
+
+--echo #
+--echo # MDEV-12817: subquery NOT subject to semi-join optimizations  
+--echo #             in ON expression of INNER JOIN
+--echo #
+
+CREATE TABLE t1 (c1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (c2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3),(4);
+ 
+CREATE TABLE t3 (c3 int) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (5),(6);
+ 
+CREATE TABLE t4 (c4 int) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (7),(8);
+
+let $q1= 
+SELECT c1 
+FROM t1 
+LEFT JOIN
+( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) )
+ON (c1 = c3);
+
+eval $q1;
+eval EXPLAIN EXTENDED $q1;
+
+let $q2= 
+SELECT *
+FROM t1
+LEFT JOIN
+( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 )
+ON (c1 = c2);
+
+--echo # mdev-12820
+eval $q2;
+eval EXPLAIN EXTENDED $q2;
+
+DROP TABLE t1,t2,t3,t4;
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test
index 61d9b09edff..0f2892ae2dc 100644
--- a/mysql-test/t/subselect_sj2_mat.test
+++ b/mysql-test/t/subselect_sj2_mat.test
@@ -263,3 +263,23 @@ DROP TABLE t1,t2,t3;
 set join_cache_level= @save_join_cache_level;
 set optimizer_switch=@save_optimizer_switch;
 
+--echo #
+--echo # mdev-7791: materialization of a semi-join subquery +
+--echo #            RAND() in WHERE
+--echo #            (materialized table is accessed last)
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=on';
+
+create table t1(i int);
+insert into t1 values (1), (2), (3), (7), (9), (10);
+create table t2(i int);
+insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+select * from t1 where (rand() < 0) and i in (select i from t2);
+explain extended
+select * from t1 where (rand() < 0) and i in (select i from t2);
+
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch;