BUG#913030: Optimizer chooses a suboptimal excution plan for Q18 from DBT-3

- When doing join optimization, pre-sort the tables so that they mimic the execution
  order we've had with 'semijoin=off'. 
- That way, we will not get regressions when there are two query plans (the old and the
  new) that have indentical costs but different execution times (because of factors that
  the optimizer was not able to take into account).
  

13 files changed, 120 insertions, 116 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index f8db2c8cf72..380e6dd0e46 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5258,8 +5258,8 @@ SET join_cache_level=0;
 EXPLAIN
 SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
 3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
@@ -5477,8 +5477,8 @@ EXPLAIN
 SELECT * FROM t1 WHERE t1.i IN 
 (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
 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	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 SELECT * FROM t1 WHERE t1.i IN 
@@ -5491,8 +5491,8 @@ EXPLAIN
 SELECT * FROM t1 WHERE t1.i IN 
 (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
 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	2	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
 SELECT * FROM t1 WHERE t1.i IN 
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 4eec1729fe3..95d82396bf0 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5196,8 +5196,8 @@ 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	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	24	Using where
-1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	it2	ALL	NULL	NULL	NULL	NULL	4	
 2	MATERIALIZED	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
 DROP TABLE IF EXISTS ot1, ot4, it2, it3;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 3f83ac59fed..f4ff52babdc 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1146,9 +1146,9 @@ insert into t4 select a from t3;
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using index condition; Using where; LooseScan
-1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t1)
-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 where
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t3)
 drop table t1, t3, t4;
 set @@optimizer_switch=@save_optimizer_switch;
 create table t1 (a int) as select * from t0 where a < 5;
@@ -1157,11 +1157,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off';
 set @@max_heap_table_size= 16384;
 explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Start temporary
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
-1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (flat, BNL join)
 flush status;
 select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 count(*)
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 77b8b5faebc..c0bf5302e64 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -1156,9 +1156,9 @@ insert into t4 select a from t3;
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using index condition; Using where; LooseScan
-1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t1)
-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 where
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t3)
 drop table t1, t3, t4;
 set @@optimizer_switch=@save_optimizer_switch;
 create table t1 (a int) as select * from t0 where a < 5;
@@ -1167,11 +1167,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off';
 set @@max_heap_table_size= 16384;
 explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
-1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (incremental, BNL join)
-1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (incremental, BNL join)
-1	PRIMARY	D	hash_ALL	NULL	#hash#$hj	5	test.E.a	10	Using where; End temporary; Using join buffer (incremental, BNLH join)
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary; Using join buffer (incremental, BNL join)
+1	PRIMARY	D	hash_ALL	NULL	#hash#$hj	5	test.E.a	10	Using where; Using join buffer (incremental, BNLH join)
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (incremental, BNL join)
 flush status;
 select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 count(*)
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 0962894bc19..46c7b48a918 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5198,9 +5198,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	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)
+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)
 DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 #
 # Bug#729039: NULL keys used to evaluate subquery
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index d97b95ad314..cccf910842b 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -5202,8 +5202,8 @@ 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	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	24	Using where
-1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	it2	ALL	NULL	NULL	NULL	NULL	4	
 2	MATERIALIZED	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
 DROP TABLE IF EXISTS ot1, ot4, it2, it3;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index d17af2ae092..8a2f1f5eaeb 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -760,16 +760,16 @@ explain extended
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; FirstMatch(t1)
 3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
 show warnings;
 Level	Code	Message
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 a
@@ -2158,10 +2158,10 @@ INSERT INTO t5 VALUES (7,0),(9,0);
 explain
 SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t5	index	a	a	10	NULL	2	Using where; Using index; LooseScan
+1	PRIMARY	t5	index	a	a	10	NULL	2	Using index; Start temporary
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t5.b	2	Using where; FirstMatch(t5)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; End temporary; Using join buffer (flat, BNL join)
 SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
 a
 0
@@ -2240,11 +2240,11 @@ alias1.c IN (SELECT SQ3_alias1.b
 FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2)
 LIMIT 100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	SQ3_alias2	index	NULL	PRIMARY	4	NULL	20	Using index; End temporary
 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
-1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Using where; Start temporary
-1	PRIMARY	SQ3_alias2	index	NULL	PRIMARY	4	NULL	20	Using index; End temporary
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	20	
 create table t3 as
 SELECT 
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 8a9946c404e..b54d4e8db56 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -49,9 +49,9 @@ a	b
 19	14
 explain select * from t2 where b in (select a from t1);
 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	t2	ref	b	b	5	test.t1.a	2	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -69,9 +69,9 @@ insert into t3 select a,a, a,a,a from t0;
 insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 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	t3	ref	b	b	5	test.t1.a	1	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
@@ -95,15 +95,19 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 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	t3	ref	b	b	5	test.t0.a	1	
-2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	56	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	
 set @save_ecp= @@engine_condition_pushdown;
 set engine_condition_pushdown=0;
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 a	b	pk1	pk2
 0	0	0	0
 1	1	1	1
+10	10	10	10
+11	11	11	11
+12	12	12	12
+13	13	13	13
 2	2	2	2
 3	3	3	3
 4	4	4	4
@@ -112,10 +116,6 @@ a	b	pk1	pk2
 7	7	7	7
 8	8	8	8
 9	9	9	9
-10	10	10	10
-11	11	11	11
-12	12	12	12
-13	13	13	13
 set engine_condition_pushdown=@save_ecp;
 set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
@@ -748,8 +748,8 @@ 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	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
 drop table t2, t3;
 #
@@ -931,9 +931,10 @@ SELECT d FROM t2, t1
 WHERE a = d AND ( pk < 2 OR d = 'z' )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index; LooseScan
-1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index; FirstMatch(t2)
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
 1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	
+2	MATERIALIZED	t2	index_merge	PRIMARY,d	d,PRIMARY	4,4	NULL	2	Using sort_union(d,PRIMARY); Using where
+2	MATERIALIZED	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
 WHERE a = d AND ( pk < 2 OR d = 'z' )
@@ -993,8 +994,8 @@ WHERE
 alias2.b = alias1.a AND 
 (alias1.b >= alias1.a OR alias2.b = 'z'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
-1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	38	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
 2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
 SELECT * FROM t2 
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index c2cfbb44d86..172a4a40f4c 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -60,9 +60,9 @@ a	b
 19	14
 explain select * from t2 where b in (select a from t1);
 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	t2	ref	b	b	5	test.t1.a	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -80,9 +80,9 @@ insert into t3 select a,a, a,a,a from t0;
 insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 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	t3	ref	b	b	5	test.t1.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
@@ -106,15 +106,19 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 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	t3	ref	b	b	5	test.t0.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	56	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	
 set @save_ecp= @@engine_condition_pushdown;
 set engine_condition_pushdown=0;
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 a	b	pk1	pk2
 0	0	0	0
 1	1	1	1
+10	10	10	10
+11	11	11	11
+12	12	12	12
+13	13	13	13
 2	2	2	2
 3	3	3	3
 4	4	4	4
@@ -123,10 +127,6 @@ a	b	pk1	pk2
 7	7	7	7
 8	8	8	8
 9	9	9	9
-10	10	10	10
-11	11	11	11
-12	12	12	12
-13	13	13	13
 set engine_condition_pushdown=@save_ecp;
 set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
@@ -762,9 +762,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	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2); Using join buffer (incremental, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch((sj-nest)); Using join buffer (incremental, BNL join)
+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
@@ -945,9 +946,10 @@ SELECT d FROM t2, t1
 WHERE a = d AND ( pk < 2 OR d = 'z' )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index; LooseScan
-1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index; FirstMatch(t2)
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
 1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+2	MATERIALIZED	t2	index_merge	PRIMARY,d	d,PRIMARY	4,4	NULL	2	Using sort_union(d,PRIMARY); Using where
+2	MATERIALIZED	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
 WHERE a = d AND ( pk < 2 OR d = 'z' )
@@ -1007,8 +1009,8 @@ WHERE
 alias2.b = alias1.a AND 
 (alias1.b >= alias1.a OR alias2.b = 'z'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
-1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	38	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
 2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT * FROM t2 
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 378bf1d8844..8acdbab9a12 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -51,9 +51,9 @@ a	b
 19	14
 explain select * from t2 where b in (select a from t1);
 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	t2	ref	b	b	5	test.t1.a	2	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -71,9 +71,9 @@ insert into t3 select a,a, a,a,a from t0;
 insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 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	t3	ref	b	b	5	test.t1.a	1	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
@@ -97,15 +97,19 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 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	t3	ref	b	b	5	test.t0.a	1	
-2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	46	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	
 set @save_ecp= @@engine_condition_pushdown;
 set engine_condition_pushdown=0;
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 a	b	pk1	pk2
 0	0	0	0
 1	1	1	1
+10	10	10	10
+11	11	11	11
+12	12	12	12
+13	13	13	13
 2	2	2	2
 3	3	3	3
 4	4	4	4
@@ -114,10 +118,6 @@ a	b	pk1	pk2
 7	7	7	7
 8	8	8	8
 9	9	9	9
-10	10	10	10
-11	11	11	11
-12	12	12	12
-13	13	13	13
 set engine_condition_pushdown=@save_ecp;
 set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
@@ -750,8 +750,8 @@ 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	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
 drop table t2, t3;
 #
@@ -933,9 +933,10 @@ SELECT d FROM t2, t1
 WHERE a = d AND ( pk < 2 OR d = 'z' )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index; LooseScan
-1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index; FirstMatch(t2)
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
 1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	
+2	MATERIALIZED	t2	index_merge	PRIMARY,d	d,PRIMARY	4,4	NULL	2	Using sort_union(d,PRIMARY); Using where
+2	MATERIALIZED	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
 WHERE a = d AND ( pk < 2 OR d = 'z' )
@@ -995,8 +996,8 @@ WHERE
 alias2.b = alias1.a AND 
 (alias1.b >= alias1.a OR alias2.b = 'z'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
-1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	38	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
 2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
 SELECT * FROM t2 
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 0ac1c51ee3d..704cde2afd2 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -773,16 +773,16 @@ explain extended
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
 show warnings;
 Level	Code	Message
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 a
@@ -2172,10 +2172,10 @@ INSERT INTO t5 VALUES (7,0),(9,0);
 explain
 SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t5	index	a	a	10	NULL	2	Using where; Using index; LooseScan
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t5.b	2	Using where; FirstMatch(t5)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t5	index	a	a	10	NULL	2	Using index; Start temporary
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	10	Using where; Using join buffer (incremental, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; End temporary; Using join buffer (incremental, BNL join)
 SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
 a
 0
@@ -2254,11 +2254,11 @@ alias1.c IN (SELECT SQ3_alias1.b
 FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2)
 LIMIT 100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	
-1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (incremental, BNL join)
-1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Using where; Start temporary; Using join buffer (incremental, BNL join)
+1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	SQ3_alias2	index	NULL	PRIMARY	4	NULL	20	Using index; End temporary; Using join buffer (incremental, BNL join)
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (incremental, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (incremental, BNL join)
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	20	
 create table t3 as
 SELECT 
@@ -2882,8 +2882,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	<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
+1	PRIMARY	t	index	idx_a	idx_a	4	NULL	3	Using index; Using temporary; Using filesort
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 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)
@@ -2896,8 +2896,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	<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
+1	PRIMARY	t	index	idx_a	idx_a	4	NULL	3	Using index; Using temporary; Using filesort
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 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/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index e60851775c0..1f96932309d 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -505,15 +505,15 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
 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	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
 1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	it2i3	18	test.t1.a1,test.t1.a2	2	100.00	Using index; Start temporary
 1	PRIMARY	t3c	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
 4	MATERIALIZED	t3b	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 3	DEPENDENT SUBQUERY	t3a	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))))
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))))
 explain extended
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -1087,11 +1087,11 @@ create index it1a on t1(a);
 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	ref	it1a	it1a	4	test.t2.c	2	100.00	Using index
+1	PRIMARY	t1	index	it1a	it1a	4	NULL	7	100.00	Using index
+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
diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result
index 2a3768c8c50..c7e04225ffe 100644
--- a/mysql-test/r/subselect_sj_nonmerged.result
+++ b/mysql-test/r/subselect_sj_nonmerged.result
@@ -77,8 +77,8 @@ explain select * from t4 where
 t4.a in (select max(t2.a) from t1, t2 group by t2.b) and 
 t4.b in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	5	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 1	PRIMARY	t4	ref	a	a	10	<subquery2>.max(t2.a),<subquery3>.max(t2.a)	12	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)