MDEV-20109: Optimizer ignores distinct key created for materialized...bb-10.4-mdev20109

Sj_materialization_picker::check_qep(): fix error in cost/fanout
calculations:
- for each join prefix, add #prefix_rows / TIME_FOR_COMPARE to the cost,
  like best_extension_by_limited_search does
- Remove the fanout produced by the subquery tables.

optimize_wo_join_buffering() (used by LooseScan and FirstMatch)
- also add #prefix_rows / TIME_FOR_COMPARE to the cost of each prefix.

23 files changed, 491 insertions, 357 deletions

diff --git a/mysql-test/main/analyze_stmt_privileges2.result b/mysql-test/main/analyze_stmt_privileges2.result
index cf38810b598..f269aaf540b 100644
--- a/mysql-test/main/analyze_stmt_privileges2.result
+++ b/mysql-test/main/analyze_stmt_privileges2.result
@@ -376,13 +376,13 @@ SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	0.00	100.00	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
 #------------------------------------------------------------------------
 # I/R/U/D/S on the inner view
@@ -491,14 +491,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 #------------------------------------------------------------------------
 # I/R/U/D/S on the outer view
 # Expectation: Can run everything
@@ -598,14 +598,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	12.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	12.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 
 #========================================================================
 # Test: Grant INSERT on the table
@@ -1591,14 +1591,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 #------------------------------------------------------------------------
 # I/R/U/D/S on the outer view
 # Expectation: Can run everything: SELECT access to the column `a` 
@@ -1708,14 +1708,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 
 #========================================================================
 # Test: Grant SELECT, INSERT, UPDATE, DELETE on the table
@@ -1940,14 +1940,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 #------------------------------------------------------------------------
 # I/R/U/D/S on the outer view
 # Expectation: Can run everything
@@ -2048,14 +2048,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	8.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 
 #########################################################################
 # Inner view permission tests
@@ -2697,14 +2697,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	14	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	14	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	14	14.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	14	14.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 #------------------------------------------------------------------------
 # I/R/U/D/S on the outer view
 # Expectation: Can run everything
@@ -2804,14 +2804,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	18.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	18.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 
 #========================================================================
 # Test: Grant INSERT on the inner view
@@ -3987,14 +3987,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	35	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	35	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	35	35.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	35	35.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 #------------------------------------------------------------------------
 # I/R/U/D/S on the outer view
 # Expectation: Can run everything
@@ -4094,14 +4094,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	39	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	39	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	39	39.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	39	39.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 
 #########################################################################
 # Outer view permission tests
@@ -4614,14 +4614,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	39	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	39	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	39	39.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	39	39.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 
 #========================================================================
 # Test: Grant INSERT on the outer view
@@ -5221,14 +5221,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 a	b
 EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	44	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	44	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	3.00	100.00	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	44	44.00	100.00	0.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	44	44.00	100.00	0.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	NULL	100.00	NULL	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	NULL	100.00	NULL	
 disconnect con1;
 connection default;
 DROP USER 'privtest'@localhost;
diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index 2556fd4b06b..12a7e2ea789 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -1159,9 +1159,9 @@ with cte as
 union
 (select a from t1 where a < 2);
 a
-7
-5
 4
+5
+7
 1
 prepare stmt from "with cte as
 (select a from t1 where a between 4 and 7 group by a)
@@ -1170,15 +1170,15 @@ union
 (select a from t1 where a < 2)";
 execute stmt;
 a
-7
-5
 4
+5
+7
 1
 execute stmt;
 a
-7
-5
 4
+5
+7
 1
 deallocate prepare stmt;
 with cte as
@@ -1188,9 +1188,9 @@ union
 (select a from cte where exists( select a from t1 where cte.a=t1.a ));
 a
 1
-7
-5
 4
+5
+7
 prepare stmt from "with cte as
 (select a from t1 where a between 4 and 7 group by a)
 (select a from t1 where a < 2)
@@ -1199,15 +1199,15 @@ union
 execute stmt;
 a
 1
-7
-5
 4
+5
+7
 execute stmt;
 a
 1
-7
-5
 4
+5
+7
 deallocate prepare stmt;
 with cte as
 (select a from t1 where a between 4 and 7)
diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result
index 2eaec8a08b4..44ebce66b22 100644
--- a/mysql-test/main/group_by.result
+++ b/mysql-test/main/group_by.result
@@ -1608,7 +1608,8 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN
 (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	Using index
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	144	Using where; FirstMatch(t2)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	144	
 SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1, t2;
 CREATE TABLE t1(
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result
index 484ee626b98..7e57c916d04 100644
--- a/mysql-test/main/index_merge_myisam.result
+++ b/mysql-test/main/index_merge_myisam.result
@@ -558,8 +558,9 @@ where exists (select 1 from t2, t3
 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	a,b	NULL	NULL	NULL	1002	Range checked for each record (index map: 0x3); FirstMatch(t1)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	MATERIALIZED	t3	ALL	a,b	NULL	NULL	NULL	1002	Range checked for each record (index map: 0x3)
 select * from t1 
 where exists (select 1 from t2, t3 
 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result
index b4572174fcb..2ec3a9ae951 100644
--- a/mysql-test/main/innodb_ext_key.result
+++ b/mysql-test/main/innodb_ext_key.result
@@ -732,8 +732,8 @@ SELECT a FROM t1 AS t, t2
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	index	a,b	b	7	NULL	10	Using index
-1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t)
+1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index; Start temporary
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; End temporary; Using join buffer (flat, BNL join)
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t.a	1	Using index
 SELECT a FROM t1 AS t, t2 
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
@@ -745,8 +745,8 @@ SELECT a FROM t1 AS t, t2
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	index	a,b	b	7	NULL	10	Using index
-1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t)
+1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index; Start temporary
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; End temporary; Using join buffer (flat, BNL join)
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t.a	1	Using index
 SELECT a FROM t1 AS t, t2 
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 518da2888cc..5dfe3241862 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -4308,11 +4308,11 @@ explain select * from t1 t_outer_1,t2 t_outer_2  where t_outer_1.a in (select t_
 t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t_outer_1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t_inner_1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	t_inner_2	ALL	NULL	NULL	NULL	NULL	9	FirstMatch(t_outer_1); Using join buffer (incremental, BNL join)
+1	PRIMARY	t_inner_1	ALL	NULL	NULL	NULL	NULL	3	Using where; Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t_inner_2	ALL	NULL	NULL	NULL	NULL	9	End temporary; Using join buffer (incremental, BNL join)
+1	PRIMARY	t_inner_4	ALL	NULL	NULL	NULL	NULL	3	Start temporary; Using join buffer (incremental, BNL join)
 1	PRIMARY	t_outer_2	ALL	NULL	NULL	NULL	NULL	9	Using join buffer (incremental, BNL join)
-1	PRIMARY	t_inner_4	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (incremental, BNL join)
-1	PRIMARY	t_inner_3	ALL	NULL	NULL	NULL	NULL	9	Using where; FirstMatch(t_outer_2); Using join buffer (incremental, BNL join)
+1	PRIMARY	t_inner_3	ALL	NULL	NULL	NULL	NULL	9	Using where; End temporary; Using join buffer (incremental, BNL join)
 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
 explain select * from t1 t_outer_1,t2 t_outer_2  where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and
@@ -4580,7 +4580,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                               ]
                             },
                             "rows_for_plan": 27,
-                            "cost_for_plan": 34.174,
+                            "cost_for_plan": 44.641,
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -4601,7 +4601,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                   ]
                                 },
                                 "rows_for_plan": 81,
-                                "cost_for_plan": 52.379,
+                                "cost_for_plan": 62.846,
                                 "rest_of_plan": [
                                   {
                                     "plan_prefix": [
@@ -4623,7 +4623,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                       ]
                                     },
                                     "rows_for_plan": 729,
-                                    "cost_for_plan": 200.19
+                                    "cost_for_plan": 210.66
                                   }
                                 ]
                               },
@@ -4646,7 +4646,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                   ]
                                 },
                                 "rows_for_plan": 243,
-                                "cost_for_plan": 84.79,
+                                "cost_for_plan": 95.256,
                                 "pruned_by_heuristic": true
                               }
                             ]
@@ -4665,7 +4665,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                               ]
                             },
                             "rows_for_plan": 9,
-                            "cost_for_plan": 30.564,
+                            "cost_for_plan": 41.031,
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -4686,7 +4686,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                   ]
                                 },
                                 "rows_for_plan": 81,
-                                "cost_for_plan": 48.779,
+                                "cost_for_plan": 59.246,
                                 "rest_of_plan": [
                                   {
                                     "plan_prefix": [
@@ -4708,8 +4708,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                       ]
                                     },
                                     "rows_for_plan": 729,
-                                    "cost_for_plan": 196.59,
-                                    "pruned_by_cost": true
+                                    "cost_for_plan": 207.06
                                   }
                                 ]
                               },
@@ -4732,7 +4731,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                   ]
                                 },
                                 "rows_for_plan": 81,
-                                "cost_for_plan": 48.779,
+                                "cost_for_plan": 59.246,
                                 "pruned_by_heuristic": true
                               }
                             ]
@@ -4751,7 +4750,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                               ]
                             },
                             "rows_for_plan": 27,
-                            "cost_for_plan": 34.174,
+                            "cost_for_plan": 44.641,
                             "pruned_by_heuristic": true
                           }
                         ]
@@ -4787,7 +4786,76 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 729,
                             "cost_for_plan": 172.44,
-                            "pruned_by_cost": true
+                            "rest_of_plan": [
+                              {
+                                "plan_prefix": [
+                                  "t_outer_1",
+                                  "t_inner_1",
+                                  "t_outer_2",
+                                  "t_inner_2"
+                                ],
+                                "table": "t_inner_4",
+                                "best_access_path": {
+                                  "considered_access_paths": [
+                                    {
+                                      "access_type": "scan",
+                                      "resulting_rows": 3,
+                                      "cost": 2.0051,
+                                      "chosen": true
+                                    }
+                                  ]
+                                },
+                                "rows_for_plan": 81,
+                                "cost_for_plan": 304.05,
+                                "rest_of_plan": [
+                                  {
+                                    "plan_prefix": [
+                                      "t_outer_1",
+                                      "t_inner_1",
+                                      "t_outer_2",
+                                      "t_inner_2",
+                                      "t_inner_4"
+                                    ],
+                                    "table": "t_inner_3",
+                                    "best_access_path": {
+                                      "considered_access_paths": [
+                                        {
+                                          "access_type": "scan",
+                                          "resulting_rows": 9,
+                                          "cost": 2.0154,
+                                          "chosen": true
+                                        }
+                                      ]
+                                    },
+                                    "rows_for_plan": 729,
+                                    "cost_for_plan": 451.86,
+                                    "pruned_by_cost": true
+                                  }
+                                ]
+                              },
+                              {
+                                "plan_prefix": [
+                                  "t_outer_1",
+                                  "t_inner_1",
+                                  "t_outer_2",
+                                  "t_inner_2"
+                                ],
+                                "table": "t_inner_3",
+                                "best_access_path": {
+                                  "considered_access_paths": [
+                                    {
+                                      "access_type": "scan",
+                                      "resulting_rows": 9,
+                                      "cost": 2.0154,
+                                      "chosen": true
+                                    }
+                                  ]
+                                },
+                                "rows_for_plan": 243,
+                                "cost_for_plan": 336.46,
+                                "pruned_by_cost": true
+                              }
+                            ]
                           },
                           {
                             "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"],
@@ -4866,7 +4934,52 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 729,
                             "cost_for_plan": 172.44,
-                            "pruned_by_heuristic": true
+                            "rest_of_plan": [
+                              {
+                                "plan_prefix": [
+                                  "t_outer_1",
+                                  "t_inner_1",
+                                  "t_outer_2",
+                                  "t_inner_3"
+                                ],
+                                "table": "t_inner_4",
+                                "best_access_path": {
+                                  "considered_access_paths": [
+                                    {
+                                      "access_type": "scan",
+                                      "resulting_rows": 3,
+                                      "cost": 2.0051,
+                                      "chosen": true
+                                    }
+                                  ]
+                                },
+                                "rows_for_plan": 2187,
+                                "cost_for_plan": 611.85,
+                                "pruned_by_cost": true
+                              },
+                              {
+                                "plan_prefix": [
+                                  "t_outer_1",
+                                  "t_inner_1",
+                                  "t_outer_2",
+                                  "t_inner_3"
+                                ],
+                                "table": "t_inner_2",
+                                "best_access_path": {
+                                  "considered_access_paths": [
+                                    {
+                                      "access_type": "scan",
+                                      "resulting_rows": 9,
+                                      "cost": 2.0154,
+                                      "chosen": true
+                                    }
+                                  ]
+                                },
+                                "rows_for_plan": 6561,
+                                "cost_for_plan": 1486.7,
+                                "pruned_by_cost": true
+                              }
+                            ]
                           }
                         ]
                       },
@@ -5303,70 +5416,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
             ]
           },
           {
-            "fix_semijoin_strategies_for_picked_join_order": [
-              {
-                "semi_join_strategy": "firstmatch",
-                "join_order": [
-                  {
-                    "table": "t_inner_4"
-                  },
-                  "best_access_path": {
-                    "considered_access_paths": [
-                      {
-                        "access_type": "scan",
-                        "resulting_rows": 3,
-                        "cost": 162.42,
-                        "chosen": true
-                      }
-                    ]
-                  },
-                  {
-                    "table": "t_inner_3"
-                  },
-                  "best_access_path": {
-                    "considered_access_paths": [
-                      {
-                        "access_type": "scan",
-                        "resulting_rows": 9,
-                        "cost": 489.74,
-                        "chosen": true
-                      }
-                    ]
-                  }
-                ]
-              },
-              {
-                "semi_join_strategy": "firstmatch",
-                "join_order": [
-                  {
-                    "table": "t_inner_1"
-                  },
-                  "best_access_path": {
-                    "considered_access_paths": [
-                      {
-                        "access_type": "scan",
-                        "resulting_rows": 3,
-                        "cost": 18.046,
-                        "chosen": true
-                      }
-                    ]
-                  },
-                  {
-                    "table": "t_inner_2"
-                  },
-                  "best_access_path": {
-                    "considered_access_paths": [
-                      {
-                        "access_type": "scan",
-                        "resulting_rows": 9,
-                        "cost": 54.415,
-                        "chosen": true
-                      }
-                    ]
-                  }
-                ]
-              }
-            ]
+            "fix_semijoin_strategies_for_picked_join_order": []
           },
           {
             "attaching_conditions_to_tables": {
@@ -5386,11 +5436,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                   "attached": null
                 },
                 {
-                  "table": "t_outer_2",
+                  "table": "t_inner_4",
                   "attached": null
                 },
                 {
-                  "table": "t_inner_4",
+                  "table": "t_outer_2",
                   "attached": null
                 },
                 {
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
index d503dab4d99..5329a9f64be 100644
--- a/mysql-test/main/opt_tvc.result
+++ b/mysql-test/main/opt_tvc.result
@@ -46,12 +46,12 @@ a	b
 2	5
 explain extended select * from t1 where a in (1,2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 explain extended select * from t1
 where a in
 (
@@ -59,12 +59,12 @@ select *
 from (values (1),(2)) as tvc_0
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 # AND-condition with IN-predicates in WHERE-part
 select * from t1
 where a in (1,2) and
@@ -90,15 +90,15 @@ explain extended select * from t1
 where a in (1,2) and
 b in (1,5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`_col_1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1
 explain extended select * from t1
 where a in
 (
@@ -111,15 +111,15 @@ select *
 from (values (1),(5)) as tvc_1
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1
 # subquery with IN-predicate
 select * from t1
 where a in
@@ -206,12 +206,12 @@ from t1
 where a in (1,2)
 ) as dr_table;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 explain extended select * from
 (
 select *
@@ -224,12 +224,12 @@ as tvc_0
 )
 ) as dr_table;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 # non-recursive CTE with IN-predicate
 with tvc_0 as
 (
@@ -265,12 +265,12 @@ where a in (1,2)
 )
 select * from tvc_0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1`
+Note	1003	with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 explain extended select * from
 (
 select *
@@ -283,12 +283,12 @@ as tvc_0
 )
 ) as dr_table;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 # VIEW with IN-predicate
 create view v1 as
 select *
@@ -316,20 +316,20 @@ a	b
 2	5
 explain extended select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 explain extended select * from v2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 drop view v1,v2;
 # subselect defined by derived table with IN-predicate
 select * from t1
@@ -519,12 +519,12 @@ a	b
 1	2
 explain extended select * from t1 where (a,b) in ((1,2),(3,4));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` and `test`.`t1`.`b` = `tvc_0`.`_col_2`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where 1
 set @@in_predicate_conversion_threshold= 2;
 # trasformation works for the one IN predicate and doesn't work for the other
 set @@in_predicate_conversion_threshold= 5;
@@ -538,12 +538,12 @@ explain extended select * from t2
 where (a,b) in ((1,2),(8,9)) and
 (a,c) in ((1,3),(8,0),(5,1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`_col_1` and `test`.`t2`.`c` = `tvc_0`.`_col_2` and (`tvc_0`.`_col_1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9)))
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where (`test`.`t2`.`a`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9)))
 set @@in_predicate_conversion_threshold= 2;
 #
 # mdev-14281: conversion of NOT IN predicate into subquery predicate
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index fc0049db997..5e446a171fe 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -5703,8 +5703,8 @@ EXPLAIN
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
@@ -5714,8 +5714,8 @@ EXPLAIN
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	index	idx	idx	5	NULL	3	Using index
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result
index ca151daf146..3c33182b3ef 100644
--- a/mysql-test/main/subselect3.result
+++ b/mysql-test/main/subselect3.result
@@ -1139,8 +1139,8 @@ create table t3 (a int);
 insert into t3 select A.a + 10*B.a from t0 A, t0 B;
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; FirstMatch(t3)
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
@@ -1276,12 +1276,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
 create table t2 as select * from t1;
 explain select * from t2 where a in (select b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	a	a	5	const	8	Using index; LooseScan
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; FirstMatch(t2)
 explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	a	a	5	const	8	Using index; LooseScan
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; FirstMatch(t2)
 drop table t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
 create table t1 (a int, b int);
@@ -1339,9 +1339,9 @@ insert into t2 select * from t2;
 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(t1)
+1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (flat, BNL join)
 drop table t0,t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
 
diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result
index adb3ec80394..69f720a95b7 100644
--- a/mysql-test/main/subselect3_jcl6.result
+++ b/mysql-test/main/subselect3_jcl6.result
@@ -1149,8 +1149,8 @@ create table t3 (a int);
 insert into t3 select A.a + 10*B.a from t0 A, t0 B;
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; FirstMatch(t3)
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
@@ -1286,12 +1286,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
 create table t2 as select * from t1;
 explain select * from t2 where a in (select b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	a	a	5	const	8	Using index; LooseScan
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; FirstMatch(t2)
 explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	a	a	5	const	8	Using index; LooseScan
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; FirstMatch(t2)
 drop table t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
 create table t1 (a int, b int);
@@ -1348,10 +1348,10 @@ create table t2 as select a as a, a as b from t0 where a < 3;
 insert into t2 select * from t2;
 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (incremental, BNL join)
-1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(t1); Using join buffer (incremental, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	X	hash_ALL	NULL	#hash#$hj	5	test.t1.a	6	Using where; Start temporary; Using join buffer (flat, BNLH join)
+1	PRIMARY	Y	hash_ALL	NULL	#hash#$hj	5	test.t1.b	6	Using where; Using join buffer (incremental, BNLH join)
+1	PRIMARY	Z	hash_ALL	NULL	#hash#$hj	5	test.t1.c	6	Using where; End temporary; Using join buffer (incremental, BNLH join)
 drop table t0,t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
 
diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result
index 4e03d9663ef..ee680d40b75 100644
--- a/mysql-test/main/subselect_mat.result
+++ b/mysql-test/main/subselect_mat.result
@@ -1510,8 +1510,8 @@ set @@optimizer_switch=@optimizer_switch_local_default;
 SET @@optimizer_switch='semijoin=on,materialization=on';
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	7	func,func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 COUNT(*)
@@ -2437,8 +2437,8 @@ INSERT INTO t2 VALUES  (11,11),(12,12),(13,13);
 explain
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	4	test.t2.i1	9	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 1
@@ -2450,8 +2450,8 @@ alter table t1 add key(id);
 explain
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
+1	PRIMARY	t1	index	id	id	4	NULL	9	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 1
@@ -2507,8 +2507,8 @@ INSERT INTO t2 VALUES  (11,11),(12,12),(13,13);
 CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2;
 explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
+1	PRIMARY	t1	index	id	id	4	NULL	9	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 1
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index 4ebf04b1f55..ecd973da09d 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -5683,9 +5683,9 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
 FROM it2,it3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using join buffer (flat, BNL join)
-1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where
-1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(ot4)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (flat, BNL join)
 DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 #
 # Bug#729039: NULL keys used to evaluate subquery
@@ -5712,8 +5712,8 @@ EXPLAIN
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	idx	idx	5	NULL	3	Using index; LooseScan
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	2	Using index; FirstMatch(t1)
 DROP TABLE t1,t2;
 #
 # BUG#752992: Wrong results for a subquery with 'semijoin=on'
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index 71d61e797f3..318ad02d473 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -5709,8 +5709,8 @@ EXPLAIN
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
@@ -5720,8 +5720,8 @@ EXPLAIN
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	index	idx	idx	5	NULL	3	Using index
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result
index 98143246673..3bd08e303b8 100644
--- a/mysql-test/main/subselect_sj.result
+++ b/mysql-test/main/subselect_sj.result
@@ -729,8 +729,8 @@ SELECT int_key FROM ot1
 WHERE int_nokey IN (SELECT it2.int_key
 FROM it1 LEFT JOIN it2 ON it2.datetime_key);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	10	
-1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it1	index	NULL	int_key	4	NULL	2	Using index
 2	MATERIALIZED	it2	ALL	int_key,datetime_key	NULL	NULL	NULL	20	Using where
 DROP TABLE ot1, it1, it2;
@@ -972,11 +972,11 @@ SELECT `varchar_key`  , `varchar_nokey`
 FROM t1  
 WHERE `varchar_nokey`  < 'n' XOR `pk`  )   ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	15	100.00	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	18	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	18	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
 2	MATERIALIZED	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and `test`.`t2`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`)
+Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`)
 SELECT varchar_nokey
 FROM t2  
 WHERE ( `varchar_nokey`  , `varchar_nokey`  )  IN (  
@@ -1246,8 +1246,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1);
 EXPLAIN
 SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	k	k	10	NULL	4	Using where; Using index; LooseScan
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	index	k	k	10	NULL	4	Using where; Using index; Start temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (flat, BNL join)
 SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0);
 i
 1
@@ -1757,8 +1757,8 @@ insert into t3 values('three'),( 'four');
 insert into t3 values('three'),( 'four');
 explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func	1	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a);
@@ -1991,12 +1991,13 @@ CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ;
 explain extended
 SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4);
 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	Using where
-1	PRIMARY	t4	ref	f2	f2	5	test.t2.f3	2	100.00	Using index; FirstMatch(t2)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t4	index	f2	f2	5	NULL	2	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f2` = `test`.`t2`.`f3` and `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2`
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2`
 SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4);
 f1	f2	f3	f3
 2	0	0	0
@@ -2495,8 +2496,8 @@ WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ref	a	a	5	const	1	Using index
-1	PRIMARY	t2	ref	a	a	5	func	1	Using index
+1	PRIMARY	t2	ref	a	a	5	const	1	Using index
+1	PRIMARY	t1	ref	a	a	5	func	1	Using index
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	0	
 SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
@@ -2706,8 +2707,8 @@ a
 19
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; FirstMatch(t3)
 drop table t0,t1,t3;
 set optimizer_switch= @tmp_923246;
 #
@@ -2923,8 +2924,8 @@ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	alias1	index_merge	PRIMARY,col_int_key,col_varchar_key	PRIMARY,col_varchar_key	4,4	NULL	2	Using sort_union(PRIMARY,col_varchar_key); Using where
-1	PRIMARY	alias2	ALL	col_int_key	NULL	NULL	NULL	12	Range checked for each record (index map: 0x2); FirstMatch(t2)
+1	PRIMARY	alias1	index_merge	PRIMARY,col_int_key,col_varchar_key	PRIMARY,col_varchar_key	4,4	NULL	2	Using sort_union(PRIMARY,col_varchar_key); Using where; Start temporary
+1	PRIMARY	alias2	ALL	col_int_key	NULL	NULL	NULL	12	Range checked for each record (index map: 0x2); End temporary
 SELECT *
 FROM t2
 WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
@@ -3264,4 +3265,27 @@ create table t2 (a2 varchar(25)) ;
 insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2);
 drop table t1,t2;
 # End of 5.5 test
+# 
+# MDEV-20109: Optimizer ignores distinct key created for materialized 
+#             semi-join subquery when searching for best execution plan
+# 
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(a int);
+insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
+create table t3 (a int);
+create table t4 (a int);
+insert into t3 select A.a +1000*B.a from t2 A, t1 B;
+insert into t4 select floor(rand()*1000) from t2 limit 500;
+# The following must not use this query plan that does a cross join:
+# |    1 | PRIMARY      | <subquery2> | ALL  | distinct_key  | ... | 500   |                                                 |
+# |    1 | PRIMARY      | t3          | ALL  | NULL          | ... | 10000 | Using where; Using join buffer (flat, BNL join) |
+#
+# Instead, it should use eq_ref on the materialized table.
+explain select * from t3 where a in (select a from t4 group by a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10000	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	500	
+drop table t1, t2, t3, t4;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test
index b693f7b5b93..a2d6c7e427c 100644
--- a/mysql-test/main/subselect_sj.test
+++ b/mysql-test/main/subselect_sj.test
@@ -2940,5 +2940,30 @@ drop table t1,t2;
 
 --echo # End of 5.5 test
 
+--echo # 
+--echo # MDEV-20109: Optimizer ignores distinct key created for materialized 
+--echo #             semi-join subquery when searching for best execution plan
+--echo # 
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+ 
+create table t2(a int);
+insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
+ 
+create table t3 (a int);
+create table t4 (a int);
+insert into t3 select A.a +1000*B.a from t2 A, t1 B;
+insert into t4 select floor(rand()*1000) from t2 limit 500;
+
+--echo # The following must not use this query plan that does a cross join:
+--echo # |    1 | PRIMARY      | <subquery2> | ALL  | distinct_key  | ... | 500   |                                                 |
+--echo # |    1 | PRIMARY      | t3          | ALL  | NULL          | ... | 10000 | Using where; Using join buffer (flat, BNL join) |
+--echo #
+--echo # Instead, it should use eq_ref on the materialized table.
+
+explain select * from t3 where a in (select a from t4 group by a);
+
+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/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result
index a127c18280e..31453801220 100644
--- a/mysql-test/main/subselect_sj2.result
+++ b/mysql-test/main/subselect_sj2.result
@@ -131,8 +131,9 @@ set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
 explain select * 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	3	Using where
-1	PRIMARY	t2	ref	b	b	5	test.t1.a	1	Using index; FirstMatch(t1)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	index	b	b	5	NULL	20	Using index
 select * from t1;
 a	b
 1	1
@@ -159,8 +160,8 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
@@ -227,8 +228,8 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
@@ -730,8 +731,9 @@ alter table t3 add primary key(id), add key(a);
 The following must use loose index scan over t3, key a:
 explain select count(a) from t2 where a in ( SELECT  a FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	1000	Using where; Using index
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	30	Using index; FirstMatch(t2)
+1	PRIMARY	t2	index	a	a	5	NULL	1000	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t3	index	a	a	5	NULL	30000	Using index
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
 1000
@@ -757,9 +759,10 @@ c2 in (select 1 from t3, t2) and
 c1 in (select convert(c6,char(1)) from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t2)
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch((sj-nest))
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	
 drop table t2, t3;
 #
 # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3
diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result
index 56c11e8c9af..3321ba221f3 100644
--- a/mysql-test/main/subselect_sj2_jcl6.result
+++ b/mysql-test/main/subselect_sj2_jcl6.result
@@ -171,9 +171,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	hash_ALL	NULL	#hash#$hj	5	test.it.a	32	Using where; Using join buffer (flat, BNLH join)
-2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	Using where
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -239,9 +239,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	hash_ALL	NULL	#hash#$hj	5	test.it.a	52	Using where; Using join buffer (flat, BNLH join)
-2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	Using where
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -1410,9 +1410,10 @@ SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t3); Using join buffer (incremental, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t1	ref	b	b	4	test.t3.b	1	Using index
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	
+2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 b	c
@@ -1438,9 +1439,10 @@ EXPLAIN
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t2)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+2	MATERIALIZED	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 pk	a	b
 1	6	8
diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result
index 73f682755da..7245e74b242 100644
--- a/mysql-test/main/subselect_sj2_mat.result
+++ b/mysql-test/main/subselect_sj2_mat.result
@@ -133,8 +133,9 @@ set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
 explain select * 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	3	Using where
-1	PRIMARY	t2	ref	b	b	5	test.t1.a	1	Using index; FirstMatch(t1)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	index	b	b	5	NULL	20	Using index
 select * from t1;
 a	b
 1	1
@@ -161,8 +162,8 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
@@ -229,8 +230,8 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
@@ -732,8 +733,9 @@ alter table t3 add primary key(id), add key(a);
 The following must use loose index scan over t3, key a:
 explain select count(a) from t2 where a in ( SELECT  a FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	1000	Using where; Using index
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	30	Using index; FirstMatch(t2)
+1	PRIMARY	t2	index	a	a	5	NULL	1000	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t3	index	a	a	5	NULL	30000	Using index
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
 1000
@@ -759,9 +761,10 @@ c2 in (select 1 from t3, t2) and
 c1 in (select convert(c6,char(1)) from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t2)
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch((sj-nest))
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	
 drop table t2, t3;
 #
 # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3
@@ -1638,12 +1641,12 @@ set optimizer_switch='materialization=on,semijoin=on';
 EXPLAIN EXTENDED 
 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	5	100.00	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 2	MATERIALIZED	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	3	100.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
-Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2`
+Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2`
 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
 i1
 7
@@ -1651,12 +1654,12 @@ EXPLAIN EXTENDED
 SELECT * FROM t1 
 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	5	100.00	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 2	MATERIALIZED	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	3	100.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
-Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0
+Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0
 SELECT * FROM t1 
 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2);
 i1
@@ -1748,8 +1751,8 @@ OR
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	index	PRIMARY	PRIMARY	4	NULL	13	Using where; Using index
-2	MATERIALIZED	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	8	
-2	MATERIALIZED	A	ALL	PRIMARY	NULL	NULL	NULL	13	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	A	ALL	PRIMARY	NULL	NULL	NULL	13	
+2	MATERIALIZED	<subquery3>	eq_ref	distinct_key	distinct_key	67	func	1	
 3	MATERIALIZED	B	range	PRIMARY	PRIMARY	4	NULL	8	Using where
 SELECT SQL_NO_CACHE t.id
 FROM t1 t
@@ -1914,18 +1917,16 @@ AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	12	
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2_2.id_product	1	Using where; Using index
-1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
+1	PRIMARY	t2_3	ref	id_t2,id_product	id_product	5	test.t3.id_product	44	Using index condition; Using where; Start temporary; End temporary
+1	PRIMARY	t2_5	ref	id_t2,id_product	id_product	5	test.t3.id_product	44	Using index condition; Using where; Start temporary; End temporary
 1	PRIMARY	t5	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery6>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	8	test.t3.id_product,const	1	Using where; Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
-1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
+1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
 1	PRIMARY	t1	index	NULL	PRIMARY	8	NULL	73	Using where; Using index; Using join buffer (flat, BNL join)
 3	MATERIALIZED	t2_2	ref	id_t2,id_product	id_t2	5	const	12	Using where
-5	MATERIALIZED	t2_4	range	id_t2,id_product	id_t2	5	NULL	18	Using index condition; Using where
-6	MATERIALIZED	t2_5	range	id_t2,id_product	id_t2	5	NULL	31	Using index condition; Using where
 2	MATERIALIZED	t2_1	ref	id_t2,id_product	id_t2	5	const	51	
-4	MATERIALIZED	t2_3	range	id_t2,id_product	id_t2	5	NULL	33	Using index condition; Using where
+5	MATERIALIZED	t2_4	range	id_t2,id_product	id_t2	5	NULL	18	Using index condition; Using where
 set optimizer_switch='rowid_filter=default';
 drop table t1,t2,t3,t4,t5;
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index acfafde6d7e..711ae32a931 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -742,8 +742,8 @@ SELECT int_key FROM ot1
 WHERE int_nokey IN (SELECT it2.int_key
 FROM it1 LEFT JOIN it2 ON it2.datetime_key);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	10	
-1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it1	index	NULL	int_key	4	NULL	2	Using index
 2	MATERIALIZED	it2	ALL	int_key,datetime_key	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
 DROP TABLE ot1, it1, it2;
@@ -985,11 +985,11 @@ SELECT `varchar_key`  , `varchar_nokey`
 FROM t1  
 WHERE `varchar_nokey`  < 'n' XOR `pk`  )   ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	15	100.00	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	18	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	18	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
 2	MATERIALIZED	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and `test`.`t2`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`)
+Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`)
 SELECT varchar_nokey
 FROM t2  
 WHERE ( `varchar_nokey`  , `varchar_nokey`  )  IN (  
@@ -1259,8 +1259,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1);
 EXPLAIN
 SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	k	k	10	NULL	4	Using where; Using index; LooseScan
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	index	k	k	10	NULL	4	Using where; Using index; Start temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (flat, BNL join)
 SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0);
 i
 1
@@ -1770,8 +1770,8 @@ insert into t3 values('three'),( 'four');
 insert into t3 values('three'),( 'four');
 explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func	1	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a);
@@ -2509,8 +2509,8 @@ WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ref	a	a	5	const	1	Using index
-1	PRIMARY	t2	ref	a	a	5	func	1	Using index
+1	PRIMARY	t2	ref	a	a	5	const	1	Using index
+1	PRIMARY	t1	ref	a	a	5	func	1	Using index
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	0	
 SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
@@ -2720,8 +2720,8 @@ a
 19
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; FirstMatch(t3)
 drop table t0,t1,t3;
 set optimizer_switch= @tmp_923246;
 #
@@ -2937,8 +2937,8 @@ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	alias1	index_merge	PRIMARY,col_int_key,col_varchar_key	PRIMARY,col_varchar_key	4,4	NULL	2	Using sort_union(PRIMARY,col_varchar_key); Using where
-1	PRIMARY	alias2	ALL	col_int_key	NULL	NULL	NULL	12	Range checked for each record (index map: 0x2); FirstMatch(t2)
+1	PRIMARY	alias1	index_merge	PRIMARY,col_int_key,col_varchar_key	PRIMARY,col_varchar_key	4,4	NULL	2	Using sort_union(PRIMARY,col_varchar_key); Using where; Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	alias2	ALL	col_int_key	NULL	NULL	NULL	12	Range checked for each record (index map: 0x2); End temporary
 SELECT *
 FROM t2
 WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
@@ -3278,6 +3278,29 @@ create table t2 (a2 varchar(25)) ;
 insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2);
 drop table t1,t2;
 # End of 5.5 test
+# 
+# MDEV-20109: Optimizer ignores distinct key created for materialized 
+#             semi-join subquery when searching for best execution plan
+# 
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(a int);
+insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
+create table t3 (a int);
+create table t4 (a int);
+insert into t3 select A.a +1000*B.a from t2 A, t1 B;
+insert into t4 select floor(rand()*1000) from t2 limit 500;
+# The following must not use this query plan that does a cross join:
+# |    1 | PRIMARY      | <subquery2> | ALL  | distinct_key  | ... | 500   |                                                 |
+# |    1 | PRIMARY      | t3          | ALL  | NULL          | ... | 10000 | Using where; Using join buffer (flat, BNL join) |
+#
+# Instead, it should use eq_ref on the materialized table.
+explain select * from t3 where a in (select a from t4 group by a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10000	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	500	
+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
@@ -3485,8 +3508,8 @@ EXPLAIN
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
 GROUP BY a HAVING a != 'z';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t	range	idx_a	idx_a	4	NULL	3	Using where; Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	PRIMARY	t	ref	idx_a	idx_a	4	test.t2.b	2	Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	MATERIALIZED	t1	ref	idx_a	idx_a	4	test.t2.b	2	Using index
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
@@ -3499,8 +3522,8 @@ EXPLAIN
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
 GROUP BY a HAVING a != 'z';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t	range	idx_a	idx_a	4	NULL	3	Using where; Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	PRIMARY	t	ref	idx_a	idx_a	4	test.t2.b	2	Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	MATERIALIZED	t1	ref	idx_a	idx_a	4	test.t2.b	2	Using index
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result
index 203dbee6374..432f6c648fc 100644
--- a/mysql-test/main/subselect_sj_mat.result
+++ b/mysql-test/main/subselect_sj_mat.result
@@ -491,15 +491,15 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
 a1 = c1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	4	100.00	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	4	99.22	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
 4	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	99.22	Using where
 4	MATERIALIZED	t2i	ref	it2i1,it2i2,it2i3	it2i3	18	test.t3.c1,test.t3.c2	1	100.00	Using index
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	99.22	Using where
 3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	5	99.22	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and `test`.`t3`.`c2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0'
+Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0'
 select * from t1, t3
 where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
 (c1, c2) in (select c1, c2 from t3
@@ -1132,11 +1132,11 @@ insert into t3 values (30);
 explain extended
 select a from t1 where a in (select c from t2 where d >= 20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	6	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t2`.`c` and `test`.`t2`.`d` >= 20
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20
 select a from t1 where a in (select c from t2 where d >= 20);
 a
 2
@@ -1543,8 +1543,8 @@ set @@optimizer_switch=@optimizer_switch_local_default;
 SET @@optimizer_switch='semijoin=on,materialization=on';
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	7	func,func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 COUNT(*)
@@ -2473,8 +2473,8 @@ INSERT INTO t2 VALUES  (11,11),(12,12),(13,13);
 explain
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	4	test.t2.i1	9	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 1
@@ -2486,8 +2486,8 @@ alter table t1 add key(id);
 explain
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
+1	PRIMARY	t1	index	id	id	4	NULL	9	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 1
@@ -2543,8 +2543,8 @@ INSERT INTO t2 VALUES  (11,11),(12,12),(13,13);
 CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2;
 explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
+1	PRIMARY	t1	index	id	id	4	NULL	9	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 1
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 51198ea47d6..a9b085fe31e 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -743,21 +743,21 @@ a	b
 explain extended select * from t1
 where a in (values (1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1
 explain extended select * from t1
 where a in (select * from (values (1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1
 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place
 select * from t1
 where a in (values (1) union select 2);
@@ -978,21 +978,21 @@ a	b
 explain extended select * from t1 
 where a = any (values (1),(2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 explain extended select * from t1 
 where a = any (select * from (values (1),(2)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
 select * from t1 
 where a = any (values (1) union select 2);
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 599642b3a26..5daf4a4b04c 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3082,11 +3082,14 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
                        disable_jbuf, prefix_rec_count, &curpos, &dummy);
       prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read);
       prefix_cost= COST_ADD(prefix_cost, curpos.read_time);
+      prefix_cost= COST_ADD(prefix_cost,
+                            prefix_rec_count / (double) TIME_FOR_COMPARE);
+      //TODO: take into account join condition selectivity here
     }
 
     *strategy= SJ_OPT_MATERIALIZE_SCAN;
     *read_time=    prefix_cost;
-    *record_count= prefix_rec_count;
+    *record_count= prefix_rec_count / mat_info->rows;
     *handled_fanout= mat_nest->sj_inner_tables;
     return TRUE;
   }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index e79e96bf210..546f1c420d5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -16703,7 +16703,8 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab,
     reopt_remaining_tables &= ~rs->table->map;
     rec_count= COST_MULT(rec_count, pos.records_read);
     cost= COST_ADD(cost, pos.read_time);
-
+    cost= COST_ADD(cost, rec_count / (double) TIME_FOR_COMPARE);
+    //TODO: take into account join condition selectivity here
 
     if (!rs->emb_sj_nest)
       *outer_rec_count= COST_MULT(*outer_rec_count, pos.records_read);