Merge branch '10.1' into 10.2

Revert commit db0917f68f, because the fix for MDEV-12696
is coming from 5.5 and 10.1 in this merge.

23 files changed, 454 insertions, 34 deletions

diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result
index ab5d47b5118..6d2ebda03d0 100644
--- a/mysql-test/r/events_2.result
+++ b/mysql-test/r/events_2.result
@@ -2,10 +2,10 @@ set sql_mode="";
 drop database if exists events_test;
 create database events_test;
 use events_test;
-create event e_26 on schedule at '2027-01-01 00:00:00' disable do set @a = 5;
+create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5;
 select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event;
 db	name	body	definer	convert_tz(execute_at, 'UTC', 'SYSTEM')	on_completion
-events_test	e_26	set @a = 5	root@localhost	2027-01-01 00:00:00	DROP
+events_test	e_26	set @a = 5	root@localhost	2037-01-01 00:00:00	DROP
 drop event e_26;
 create event e_26 on schedule at NULL disable do set @a = 5;
 ERROR HY000: Incorrect AT value: 'NULL'
diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result
index 1af67cf6a4e..1e95e983253 100644
--- a/mysql-test/r/explain_json.result
+++ b/mysql-test/r/explain_json.result
@@ -825,7 +825,7 @@ EXPLAIN
             "buffer_type": "flat",
             "buffer_size": "256Kb",
             "join_type": "BNL",
-            "attached_condition": "t2.b <> outer_t1.a and trigcond(<cache>(outer_t1.a) = t1.a or t1.a is null)"
+            "attached_condition": "t2.b <> outer_t1.a"
           }
         }
       }
@@ -1573,7 +1573,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 10,
               "filtered": 100,
-              "attached_condition": "t0.a < 5 and t1.b < t0.a"
+              "attached_condition": "t1.b < t0.a"
             }
           }
         }
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index b9ee2a9136f..31335dc2af6 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -1702,7 +1702,19 @@ SELECT ASTEXT(p) FROM v1;
 ASTEXT(p)
 POINT(1 1)
 DROP VIEW v1;
-End of 5.5 tests
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression
+#
+SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1;
+AsText(g)
+NULL
+POINT(1 1)
+#
+# End 10.0 tests
+#
 SHOW CREATE TABLE information_schema.geometry_columns;
 Table	Create Table
 GEOMETRY_COLUMNS	CREATE TEMPORARY TABLE `GEOMETRY_COLUMNS` (
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 207317c1add..8a58a769ed9 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -432,7 +432,7 @@ WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	DEPENDENT SUBQUERY	it	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where
-2	DEPENDENT SUBQUERY	t2	index	NULL	PRIMARY	4	NULL	3	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; Using join buffer (flat, BNL join)
 SELECT * FROM t1
 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
 pk	i
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index ffeddc989ec..468aa3b2dc0 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1743,7 +1743,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using temporary; Using filesort
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	func	1	100.00	Using where; Using index
 2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY	PRIMARY	4	func	1	100.00	Using where; Using index
-2	DEPENDENT SUBQUERY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.pk	1	100.00	Using where; Using index
+2	DEPENDENT SUBQUERY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.pk	1	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select `test`.`t3`.`pk` + if(`test`.`t4`.`pk` is null,0,`test`.`t4`.`pk`) from `test`.`t3` left join `test`.`t4` on(`test`.`t4`.`pk` = `test`.`t3`.`pk`) where `test`.`t3`.`pk` = `test`.`t2`.`pk` + 1000 limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`pk` + 1000 and `test`.`t1`.`pk` > 1000 group by `test`.`t2`.`pk`
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 4ffca891061..bcb8fb21947 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1754,7 +1754,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using temporary; Using filesort
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	func	1	100.00	Using where; Using index
 2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY	PRIMARY	4	func	1	100.00	Using where; Using index
-2	DEPENDENT SUBQUERY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.pk	1	100.00	Using where; Using index
+2	DEPENDENT SUBQUERY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.pk	1	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select `test`.`t3`.`pk` + if(`test`.`t4`.`pk` is null,0,`test`.`t4`.`pk`) from `test`.`t3` left join `test`.`t4` on(`test`.`t4`.`pk` = `test`.`t3`.`pk`) where `test`.`t3`.`pk` = `test`.`t2`.`pk` + 1000 limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`pk` + 1000 and `test`.`t1`.`pk` > 1000 group by `test`.`t2`.`pk`
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result
index b91dd1b87ae..4f59e9cdbe3 100644
--- a/mysql-test/r/loaddata.result
+++ b/mysql-test/r/loaddata.result
@@ -340,7 +340,7 @@ FIELDS ESCAPED BY '\\'
   TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n' (c0, c2);
-ERROR HY000: Invalid column reference (v2.c0) in LOAD DATA
+ERROR HY000: Column 'c0' is not updatable
 
 LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v3
 FIELDS ESCAPED BY '\\'
diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result
index 711fea9e854..34719a27afc 100644
--- a/mysql-test/r/loadxml.result
+++ b/mysql-test/r/loadxml.result
@@ -129,8 +129,8 @@ DROP TABLE t1;
 CREATE TABLE t1 (c1 TEXT);
 CREATE VIEW v1 AS SELECT CONCAT(c1,'') AS c1, NULL AS c2 FROM t1;
 LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c1);
-ERROR HY000: Invalid column reference (v1.c1) in LOAD DATA
+ERROR HY000: Column 'c1' is not updatable
 LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c2);
-ERROR HY000: Invalid column reference (v1.c2) in LOAD DATA
+ERROR HY000: Column 'c2' is not updatable
 DROP VIEW v1;
 DROP TABLE t1;
diff --git a/mysql-test/r/log_tables-big.result b/mysql-test/r/log_tables-big.result
index 9b81127c825..1e189a7726f 100644
--- a/mysql-test/r/log_tables-big.result
+++ b/mysql-test/r/log_tables-big.result
@@ -1,29 +1,31 @@
+set @@global.log_output = 'TABLE';
 set session long_query_time=10;
 select get_lock('bug27638', 1);
 get_lock('bug27638', 1)
 1
 set session long_query_time=1;
-truncate table mysql.slow_log;
 select get_lock('bug27638', 2);
 get_lock('bug27638', 2)
 0
-select if (query_time between '00:00:01' and '00:00:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log;
+select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log
+where sql_text = 'select get_lock(\'bug27638\', 2)';
 qt	sql_text
 OK	select get_lock('bug27638', 2)
-truncate table mysql.slow_log;
 select get_lock('bug27638', 60);
 get_lock('bug27638', 60)
 0
-select if (query_time between '00:00:59' and '00:01:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log;
+select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log
+where sql_text = 'select get_lock(\'bug27638\', 60)';
 qt	sql_text
 OK	select get_lock('bug27638', 60)
-truncate table mysql.slow_log;
 select get_lock('bug27638', 101);
 get_lock('bug27638', 101)
 0
-select if (query_time between '00:01:40' and '00:01:50', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log;
+select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log
+where sql_text = 'select get_lock(\'bug27638\', 101)';
 qt	sql_text
 OK	select get_lock('bug27638', 101)
 select release_lock('bug27638');
 release_lock('bug27638')
 1
+set @@global.log_output=default;
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 458d0195a19..6a70521cd72 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -430,7 +430,7 @@ WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	DEPENDENT SUBQUERY	it	eq_ref	PRIMARY	PRIMARY	4	func	1	Using index condition
-2	DEPENDENT SUBQUERY	t2	index	NULL	PRIMARY	4	NULL	3	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; Using join buffer (flat, BNL join)
 SELECT * FROM t1
 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
 pk	i
@@ -804,7 +804,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	ALL	PRIMARY,c	NULL	NULL	NULL	64	Using where
 1	PRIMARY	t2	ref	g	g	5	test.t.c	19	Using where
 2	DEPENDENT SUBQUERY	t1	index	PRIMARY	PRIMARY	4	NULL	64	Using where; Using index
-2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index condition; Using where
+2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
 SELECT COUNT(*) FROM t1 AS t, t2
 WHERE c = g
 AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f  AND h <= t.e AND a > t.b)
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 66b501e8278..5a8c1e7a8ae 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -5330,6 +5330,69 @@ DROP TABLE t1;
 DROP TABLE t2;
 DROP DATABASE db_20772273;
 USE test;
+#
+# Bug #25717383: MYSQLDUMP MAY EXECUTE ANY ARBITRARY QUERY
+#
+CREATE DATABASE bug25717383;
+use bug25717383;
+CREATE TABLE `tab
+one` (a int);
+CREATE VIEW `view
+one` as SELECT * FROM `tab
+one`;
+CREATE PROCEDURE `proc
+one`() SELECT * from `tab
+one`;
+CREATE TEMPORARY TABLE `temp
+one` (id INT);
+CREATE TRIGGER `trig
+one` BEFORE INSERT ON `tab
+one` FOR EACH ROW SET NEW.a = 1;
+CREATE EVENT `event
+one` ON SCHEDULE AT '2030-01-01 00:00:00' DO SET @a=5;
+SHOW TABLES FROM bug25717383;
+Tables_in_bug25717383
+tab
+one
+view
+one
+SHOW TRIGGERS FROM bug25717383;
+Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
+trig
+one	INSERT	tab
+one	SET NEW.a = 1	BEFORE	#		root@localhost	utf8	utf8_general_ci	latin1_swedish_ci
+SHOW EVENTS FROM bug25717383;
+Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
+bug25717383	event
+one	root@localhost	SYSTEM	ONE TIME	#	NULL	NULL	NULL	NULL	ENABLED	1	utf8	utf8_general_ci	latin1_swedish_ci
+SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE'
+       ORDER BY ROUTINE_NAME;
+ROUTINE_NAME
+proc
+one
+SHOW TABLES FROM bug25717383;
+Tables_in_bug25717383
+tab
+one
+view
+one
+SHOW TRIGGERS FROM bug25717383;
+Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
+trig
+one	INSERT	tab
+one	SET NEW.a = 1	BEFORE	#		root@localhost	utf8	utf8_general_ci	latin1_swedish_ci
+SHOW EVENTS FROM bug25717383;
+Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
+bug25717383	event
+one	root@localhost	SYSTEM	ONE TIME	#	NULL	NULL	NULL	NULL	ENABLED	1	utf8	utf8_general_ci	latin1_swedish_ci
+SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE'
+       ORDER BY ROUTINE_NAME;
+ROUTINE_NAME
+proc
+one
+DROP DATABASE bug25717383;
 Usage: mysqldump [OPTIONS] database [tables]
 OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
 OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result
index f8f0b8e379d..237f04d6d28 100644
--- a/mysql-test/r/mysqltest.result
+++ b/mysql-test/r/mysqltest.result
@@ -684,7 +684,9 @@ drop table t1;
 y
 txt
 b is b and more is more
-txt
+txt2
+b is b or more is more
+txt3
 a is a and less is more
 sflfdt 'ABCDfF bbddff h' bs txt;
 txt
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 673b53b9c04..9ed067c0bdc 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -267,7 +267,7 @@ from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	2	100.00	Using where; Full scan on NULL key
-2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	100	100.00	Using join buffer (flat, BNL join)
 Warnings:
 Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where `test`.`t1`.`c` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b` or `test`.`t1`.`b` is null)) having trigcond(`test`.`t1`.`a` is null) and trigcond(`test`.`t1`.`b` is null)))) AS `Z` from `test`.`t2`
@@ -1242,19 +1242,19 @@ explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
-2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
-2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 explain select straight_join * from t2 X, t2 Y 
 where X.a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
-2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 create table t0 (a int, b int);
 insert into t0 values(1,1);
 explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 06a5e2c5aa4..2a1fc0df957 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -277,7 +277,7 @@ from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	2	100.00	Using where; Full scan on NULL key
-2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	100	100.00	Using join buffer (flat, BNL join)
 Warnings:
 Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where `test`.`t1`.`c` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b` or `test`.`t1`.`b` is null)) having trigcond(`test`.`t1`.`a` is null) and trigcond(`test`.`t1`.`b` is null)))) AS `Z` from `test`.`t2`
@@ -1252,19 +1252,19 @@ explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
-2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
-2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 explain select straight_join * from t2 X, t2 Y 
 where X.a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
-2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 create table t0 (a int, b int);
 insert into t0 values(1,1);
 explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 3d0dfd189a8..699a1b27b78 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -19,7 +19,7 @@ SELECT 1 FROM t1
 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
 ORDER BY count(*);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using where; Using index
+1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using index
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 3	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
 # should not crash the next statement
@@ -1409,7 +1409,7 @@ GROUP BY f9;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
 SELECT COUNT(t2.f3),
 (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
 FROM t2 JOIN t1 ON t1.f3
@@ -1425,7 +1425,7 @@ ORDER BY f9;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
 SELECT COUNT(t2.f3),
 (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
 FROM t2 JOIN t1 ON t1.f3
@@ -2435,9 +2435,11 @@ WHERE EXISTS (
 )";
 EXECUTE stmt;
 i
+4
 6
 EXECUTE stmt;
 i
+4
 6
 drop table t1, t2, t3;
 #
diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result
index 07b5557506b..d5e61d4beca 100644
--- a/mysql-test/r/subselect_exists2in.result
+++ b/mysql-test/r/subselect_exists2in.result
@@ -884,5 +884,55 @@ a
 deallocate prepare stmt;
 drop view v1;
 drop table t1,t2;
+#
+#MDEV-10053: EXIST to IN transformation turned down
+#
+CREATE TABLE t1 (
+pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk))
+ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
+set @optimizer_switch_save=@@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
+explain extended SELECT STRAIGHT_JOIN sq1.f2 
+FROM ( SELECT * FROM t1 ) AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2 
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+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	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	DEPENDENT SUBQUERY	sq2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+4	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1276	Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1
+Note	1003	select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(exists(select 1 from `test`.`t1` `sq2` semi join (`test`.`t1`) where `test`.`sq2`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`pk` = `test`.`t1`.`f1`))
+SELECT STRAIGHT_JOIN sq1.f2 
+FROM ( SELECT * FROM t1 ) AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2 
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+f2
+foo
+set optimizer_switch='exists_to_in=on';
+explain extended SELECT STRAIGHT_JOIN sq1.f2 
+FROM ( SELECT * FROM t1 ) AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2 
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+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	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	DEPENDENT SUBQUERY	sq2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+4	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1276	Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1
+Note	1003	select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`f1`,<exists>(select `test`.`sq2`.`f1` from `test`.`t1` `sq2` semi join (`test`.`t1`) where `test`.`t1`.`pk` = `test`.`t1`.`f1` and <cache>(`test`.`t1`.`f1`) = `test`.`sq2`.`f1`)))
+SELECT STRAIGHT_JOIN sq1.f2 
+FROM ( SELECT * FROM t1 ) AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2 
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+f2
+foo
+set optimizer_switch= @optimizer_switch_save;
+DROP TABLE t1;
 # End of 10.0 tests
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index 11221c797ff..01257c33361 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -312,7 +312,7 @@ EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
 2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY,d	PRIMARY	1	func	1	Using where
-3	DEPENDENT SUBQUERY	t2	index	NULL	d	2	NULL	1	Using where; Using index
+3	DEPENDENT SUBQUERY	t2	index	NULL	d	2	NULL	1	Using index
 DROP TABLE t2;
 CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB;
 INSERT INTO t2 VALUES (1, 1);
@@ -509,6 +509,20 @@ set join_cache_level = default;
 drop view v1;
 drop table t1,t2;
 #
+# MDEV-10693: cost-based choice between materialization and in-to-exists
+#             for a subquery from the expression used in ref access 
+#
+CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 INT) ENGINE=InnoDB;
+CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (3);
+SELECT * FROM t1
+WHERE NULL IN ( SELECT i2 FROM t2 
+WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 );
+i1
+DROP TABLE t1,t2,t3;
+#
 # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding
 #
 create table t1(a int) engine=innodb;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 59f9d3c1876..ede18083b85 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2192,6 +2192,117 @@ execute stmt;
 a
 0
 drop table t1;
+#
+# MDEV-12429: IN subquery used in WHERE of EXISTS subquery
+#
+CREATE TABLE t1 (
+pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
+SELECT sq1.f2    FROM t1 AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+f2
+foo
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
+EXPLAIN
+SELECT sq1.f2    FROM t1 AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	sq1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	DEPENDENT SUBQUERY	sq2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
+# this checks the result set above
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT sq1.f2    FROM t1 AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+f2
+foo
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1;
+#
+# MDEV-12145: IN subquery used in WHERE of EXISTS subquery
+#
+CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(6);
+CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8),(7),(1);
+CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
+SELECT * FROM t1
+WHERE EXISTS ( SELECT * FROM t2, t3
+WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+f1
+6
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE EXISTS ( SELECT * FROM t2, t3
+WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+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	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	DEPENDENT SUBQUERY	t2	index	i2	i2	5	NULL	3	100.00	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t3	ref	i3	i3	5	test.t2.i2	2	100.00	Using index
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	8	100.00	
+Warnings:
+Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3`))
+# this checks the result set above
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT * FROM t1
+WHERE EXISTS ( SELECT * FROM t2, t3
+WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+f1
+6
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1,t2,t3;
+#
+# MDEV-9686: IN subquery used in WHERE of a subquery from select list
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT);
+INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3);
+CREATE TABLE t2 (f2 INT);
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
+# t1.pk is always IN ( SELECT f2 FROM t2 ),
+# so the IN condition should be true for every row,
+# and thus COUNT(*) should always return 5
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+pk	f1	sq
+1	4	5
+2	3	5
+3	3	5
+4	6	5
+5	3	5
+EXPLAIN EXTENDED
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+2	DEPENDENT SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using join buffer (flat, BNL join)
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+Warnings:
+Note	1276	Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1`
+# this checks the result set above
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+pk	f1	sq
+1	4	5
+2	3	5
+3	3	5
+4	6	5
+5	3	5
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1,t2;
 # End of 5.5 tests
 #
 # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index c7b0f0d46c6..08a5d019bab 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -400,3 +400,45 @@ 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;
+#
+# MDEV-12673: cost-based choice between materialization and in-to-exists
+#
+CREATE TABLE t1 (
+pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo');
+CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo');
+SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 )
+0
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 )
+1
+EXPLAIN 
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t2	const	PRIMARY,a2	PRIMARY	4	const	1	
+2	SUBQUERY	t1	ref	a1,b1	b1	6	const	1	Using where
+DROP TABLE t1,t2;
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'foo');
+CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'foo'),(2,'bar');
+SELECT * FROM t1 WHERE i1 NOT IN ( 
+SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 
+);
+i1
+1
+EXPLAIN
+SELECT * FROM t1 WHERE i1 NOT IN ( 
+SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+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;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 91d05a31dfa..9ee5d8d2806 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -178,7 +178,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	m16	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	m17	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	m18	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
-2	DEPENDENT SUBQUERY	m19	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	m19	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
 select * from
 t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) 
 where t1.a < 5;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 09d88c9996f..d20c6251f85 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -191,7 +191,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	m16	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (incremental, BNL join)
 2	DEPENDENT SUBQUERY	m17	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (incremental, BNL join)
 2	DEPENDENT SUBQUERY	m18	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (incremental, BNL join)
-2	DEPENDENT SUBQUERY	m19	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (incremental, BNL join)
+2	DEPENDENT SUBQUERY	m19	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (incremental, BNL join)
 select * from
 t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) 
 where t1.a < 5;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index a3f3cc4213c..72f875cf8a7 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2232,6 +2232,117 @@ execute stmt;
 a
 0
 drop table t1;
+#
+# MDEV-12429: IN subquery used in WHERE of EXISTS subquery
+#
+CREATE TABLE t1 (
+pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
+SELECT sq1.f2    FROM t1 AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+f2
+foo
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
+EXPLAIN
+SELECT sq1.f2    FROM t1 AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	sq1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	DEPENDENT SUBQUERY	sq2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
+# this checks the result set above
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT sq1.f2    FROM t1 AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+f2
+foo
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1;
+#
+# MDEV-12145: IN subquery used in WHERE of EXISTS subquery
+#
+CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(6);
+CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8),(7),(1);
+CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
+SELECT * FROM t1
+WHERE EXISTS ( SELECT * FROM t2, t3
+WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+f1
+6
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE EXISTS ( SELECT * FROM t2, t3
+WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+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	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	DEPENDENT SUBQUERY	t2	index	i2	i2	5	NULL	3	100.00	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t3	ref	i3	i3	5	test.t2.i2	2	100.00	Using index
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	8	100.00	
+Warnings:
+Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3`))
+# this checks the result set above
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT * FROM t1
+WHERE EXISTS ( SELECT * FROM t2, t3
+WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+f1
+6
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1,t2,t3;
+#
+# MDEV-9686: IN subquery used in WHERE of a subquery from select list
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT);
+INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3);
+CREATE TABLE t2 (f2 INT);
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
+# t1.pk is always IN ( SELECT f2 FROM t2 ),
+# so the IN condition should be true for every row,
+# and thus COUNT(*) should always return 5
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+pk	f1	sq
+1	4	5
+2	3	5
+3	3	5
+4	6	5
+5	3	5
+EXPLAIN EXTENDED
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+2	DEPENDENT SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using join buffer (flat, BNL join)
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+Warnings:
+Note	1276	Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1`
+# this checks the result set above
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+pk	f1	sq
+1	4	5
+2	3	5
+3	3	5
+4	6	5
+5	3	5
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1,t2;
 # End of 5.5 tests
 #
 # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT
diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result
index 2385e3887c4..1764ea6a7dd 100644
--- a/mysql-test/r/symlink.result
+++ b/mysql-test/r/symlink.result
@@ -226,3 +226,14 @@ t2	CREATE TABLE `t2` (
   PRIMARY KEY (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop tables t1, t2;
+#
+# Test for bug #25514146 DB_NAME IS IGNORED WHEN CREATING TABLE
+# WITH DATA DIRECTORY
+#
+# Make sure we have no current database
+CREATE DATABASE x;
+USE x;
+DROP DATABASE x;
+CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM
+DATA DIRECTORY "MYSQLTEST_VARDIR/tmp";
+DROP TABLE test.t1;