Fixed LP bug #889750.

If the optimizer switch 'semijoin_with_cache' is set to 'off' then 
join cache cannot be used to join inner tables of a semijoin.

Also fixed a bug in the function check_join_cache_usage() that led
to wrong output of the EXPLAIN commands for some test cases.

30 files changed, 183 insertions, 52 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index b1225c31575..cb84d10e0fa 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -190,13 +190,13 @@ pla_id	test
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
-1	PRIMARY	<derived2>	ALL	NULL	$hj	7	test.m2.matintnum	9	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	9	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
-1	PRIMARY	<derived2>	ALL	NULL	$hj	7	test.m2.matintnum	9	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	9	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 drop table t1,t2;
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 9d572a0b223..ec81db7e418 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1480,7 +1480,7 @@ WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
 SELECT * FROM t3 
 WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 955120a86a2..a871e1ae761 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -200,7 +200,7 @@ flush tables;
 EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	OUTR	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
-1	PRIMARY	INNR	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	INNR	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 flush tables;
 SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
 dt
@@ -208,7 +208,7 @@ flush tables;
 EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	OUTR	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
-1	PRIMARY	INNR	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	INNR	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 flush tables;
 SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
 dt
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index f84e0246969..e71887f80ac 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2,7 +2,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
 drop view if exists v2;
 set @subselect_tmp=@@optimizer_switch;
 set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
-"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
+"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 select (select 2);
 (select 2)
@@ -2893,6 +2893,8 @@ select * from (select max(fld) from t1) as foo;
 max(fld)
 1
 drop table t1;
+set @tmp11867_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin_with_cache=off';
 CREATE TABLE t1 (one int, two int, flag char(1));
 CREATE TABLE t2 (one int, two int, flag char(1));
 INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -2977,6 +2979,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
 DROP TABLE t1,t2;
+set optimizer_switch=@tmp11867_optimizer_switch;
 CREATE TABLE t1 (a char(5), b char(5));
 INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
 SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 1279fc6b8f4..9ce3c8dfbe3 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1,6 +1,6 @@
 drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
 set @subselect3_tmp= @@optimizer_switch;
-set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on';
 create table t1 (oref int, grp int, ie int) ;
 insert into t1 (oref, grp, ie) values
 (1, 1, 1),
@@ -345,8 +345,8 @@ dd	NULL	0
 bb	NULL	NULL
 select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
 oref	a
-ff	2
 aa	1
+ff	2
 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 oref	a
 bb	2
@@ -523,8 +523,8 @@ aa	1	1	1
 dd	1	NULL	0
 select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
 oref	a	b
-ff	2	2
 aa	1	1
+ff	2	2
 select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
 oref	a	b
 bb	2	1
@@ -1117,7 +1117,7 @@ set @@optimizer_switch=@save_optimizer_switch;
 explain select * from (select a from t0) X where a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	11	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; FirstMatch(<derived2>)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join)
 2	DERIVED	t0	ALL	NULL	NULL	NULL	NULL	11	
 drop table t0, t1;
 create table t0 (a int);
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 15dd920d95d..eacf84428cb 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -9,7 +9,7 @@ Variable_name	Value
 join_cache_level	6
 drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
 set @subselect3_tmp= @@optimizer_switch;
-set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on';
 create table t1 (oref int, grp int, ie int) ;
 insert into t1 (oref, grp, ie) values
 (1, 1, 1),
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index ddf339ba1b5..0e5adde7178 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1,6 +1,7 @@
 drop table if exists t1,t2,t3,t4,t5,t6;
 set @subselect4_tmp= @@optimizer_switch;
 set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+set optimizer_switch='semijoin_with_cache=on';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 #
 # Bug #46791: Assertion failed:(table->key_read==0),function unknown
diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result
index 3dfe2bab4a6..a74b713f11f 100644
--- a/mysql-test/r/subselect_extra.result
+++ b/mysql-test/r/subselect_extra.result
@@ -1,5 +1,8 @@
 drop table if exists t1,t2,t3,t4;
 drop view if exists v1,v2,v3;
+set @subselect_extra_tmp=@@optimizer_switch;
+set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_extra_test,
+"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 # From explain.test:
 #
 # Bug#37870: Usage of uninitialized value caused failed assertion.
@@ -226,7 +229,7 @@ group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
 2	DEPENDENT SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
-2	DEPENDENT SUBQUERY	t3	index	NULL	idx_t3_1	10	NULL	192	Using where; Using index; FirstMatch(t2)
+2	DEPENDENT SUBQUERY	t3	index	NULL	idx_t3_1	10	NULL	192	Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join)
 select a1,a2,b,c,min(c), max(c) from t1
 where exists ( select * from t2
 where t2.c in (select c from t3 where t3.c > t1.b) and
@@ -257,7 +260,7 @@ group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
 2	DEPENDENT SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
-2	DEPENDENT SUBQUERY	t3	index	NULL	idx_t3_1	10	NULL	192	Using where; Using index; FirstMatch(t2)
+2	DEPENDENT SUBQUERY	t3	index	NULL	idx_t3_1	10	NULL	192	Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join)
 select a1,a2,b,c,min(c), max(c) from t1
 where exists ( select * from t2
 where t2.c in (select c from t3 where t3.c > t1.c) and
@@ -304,7 +307,7 @@ EXPLAIN SELECT 1 FROM t1 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	t1	index	PRIMARY,i2	PRIMARY	4	NULL	144	Using index
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	144	Using where; FirstMatch(t1)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	144	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 CREATE TABLE t2 (a INT, b INT, KEY(a));
 INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
 EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
@@ -317,7 +320,7 @@ 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	t1	ALL	NULL	NULL	NULL	NULL	144	Using where; FirstMatch(t2); Using join buffer (flat, BNL join)
 DROP TABLE t1, t2;
 # 
 # From derived_view.test
@@ -427,8 +430,8 @@ WHERE t3.b IN (SELECT v1.b FROM  v1, t2
 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where; Start temporary; End temporary
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
 SELECT * FROM t3 
 WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result
index c1ca1186259..f0d720d91f3 100644
--- a/mysql-test/r/subselect_extra_no_semijoin.result
+++ b/mysql-test/r/subselect_extra_no_semijoin.result
@@ -1,7 +1,10 @@
 set @subselect_extra_no_sj_tmp=@@optimizer_switch;
-set optimizer_switch='semijoin=off,firstmatch=off,loosescan=off';
+set  @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loosescan=off,semijoin_with_cache=off';
 drop table if exists t1,t2,t3,t4;
 drop view if exists v1,v2,v3;
+set @subselect_extra_tmp=@@optimizer_switch;
+set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_extra_test,
+"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 # From explain.test:
 #
 # Bug#37870: Usage of uninitialized value caused failed assertion.
@@ -478,3 +481,4 @@ DROP VIEW v2;
 DROP TABLE t1,t2;
 set optimizer_switch= @tmp_subselect_extra_derived;
 set optimizer_switch= @subselect_extra_no_sj_tmp;
+set @optimizer_switch_for_subselect_extra_test=null;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 8241d9027a5..d155a2cd3a8 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1,6 +1,6 @@
 set @subselect_mat_test_optimizer_switch_value='materialization=on,in_to_exists=off,semijoin=off';
 set @subselect_sj_mat_tmp= @@optimizer_switch;
-set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on');
+set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on');
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set @optimizer_switch_local_default= @@optimizer_switch;
 drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index b480b274c48..6b6af156a00 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7,7 +7,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
 drop view if exists v2;
 set @subselect_tmp=@@optimizer_switch;
 set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
-"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
+"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 select (select 2);
 (select 2)
@@ -2898,6 +2898,8 @@ select * from (select max(fld) from t1) as foo;
 max(fld)
 1
 drop table t1;
+set @tmp11867_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin_with_cache=off';
 CREATE TABLE t1 (one int, two int, flag char(1));
 CREATE TABLE t2 (one int, two int, flag char(1));
 INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -2982,6 +2984,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
 DROP TABLE t1,t2;
+set optimizer_switch=@tmp11867_optimizer_switch;
 CREATE TABLE t1 (a char(5), b char(5));
 INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
 SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index ceb9ee40db8..7eb45ae7d9b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -3,7 +3,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
 drop view if exists v2;
 set @subselect_tmp=@@optimizer_switch;
 set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
-"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
+"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 select (select 2);
 (select 2)
@@ -2894,6 +2894,8 @@ select * from (select max(fld) from t1) as foo;
 max(fld)
 1
 drop table t1;
+set @tmp11867_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin_with_cache=off';
 CREATE TABLE t1 (one int, two int, flag char(1));
 CREATE TABLE t2 (one int, two int, flag char(1));
 INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -2978,6 +2980,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
 DROP TABLE t1,t2;
+set optimizer_switch=@tmp11867_optimizer_switch;
 CREATE TABLE t1 (a char(5), b char(5));
 INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
 SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 43d8e4e7ad3..f7f593fb8ca 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6,7 +6,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
 drop view if exists v2;
 set @subselect_tmp=@@optimizer_switch;
 set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
-"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
+"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 select (select 2);
 (select 2)
@@ -2897,6 +2897,8 @@ select * from (select max(fld) from t1) as foo;
 max(fld)
 1
 drop table t1;
+set @tmp11867_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin_with_cache=off';
 CREATE TABLE t1 (one int, two int, flag char(1));
 CREATE TABLE t2 (one int, two int, flag char(1));
 INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -2981,6 +2983,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
 DROP TABLE t1,t2;
+set optimizer_switch=@tmp11867_optimizer_switch;
 CREATE TABLE t1 (a char(5), b char(5));
 INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
 SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 7f9e3185d2e..bb1ffa6942a 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -3,7 +3,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
 drop view if exists v2;
 set @subselect_tmp=@@optimizer_switch;
 set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
-"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
+"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 select (select 2);
 (select 2)
@@ -2894,6 +2894,8 @@ select * from (select max(fld) from t1) as foo;
 max(fld)
 1
 drop table t1;
+set @tmp11867_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin_with_cache=off';
 CREATE TABLE t1 (one int, two int, flag char(1));
 CREATE TABLE t2 (one int, two int, flag char(1));
 INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -2978,6 +2980,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
 DROP TABLE t1,t2;
+set optimizer_switch=@tmp11867_optimizer_switch;
 CREATE TABLE t1 (a char(5), b char(5));
 INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
 SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 99d8e9d2d77..c372d0fe0bf 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -726,7 +726,7 @@ FROM it1 LEFT JOIN it2 ON it2.datetime_key);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	it1	index	NULL	int_key	4	NULL	2	Using index; Start temporary
 1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
-1	PRIMARY	it2	ALL	int_key,datetime_key	NULL	NULL	NULL	20	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	it2	ALL	int_key,datetime_key	NULL	NULL	NULL	20	Using where; End temporary
 DROP TABLE ot1, it1, it2;
 # End of BUG#38075
 #
@@ -1047,8 +1047,8 @@ AND t1.val IN (SELECT t3.val FROM t3
 WHERE  t3.val LIKE 'a%' OR t3.val LIKE 'e%');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 SELECT *
 FROM t1
 WHERE t1.val IN (SELECT t2.val FROM t2
@@ -1238,7 +1238,7 @@ A.t1field IN (SELECT C.t2field FROM t2 C
 WHERE C.t2field IN (SELECT D.t2field FROM t2 D));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	index	PRIMARY	PRIMARY	4	NULL	3	Using index; Start temporary
-1	PRIMARY	B	index	NULL	PRIMARY	4	NULL	3	Using index; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	B	index	NULL	PRIMARY	4	NULL	3	Using index; End temporary
 1	PRIMARY	C	eq_ref	PRIMARY	PRIMARY	4	test.A.t1field	1	Using index
 1	PRIMARY	D	eq_ref	PRIMARY	PRIMARY	4	test.A.t1field	1	Using index
 SELECT * FROM t1 A 
@@ -1268,8 +1268,8 @@ where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	3	Start temporary
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary
 drop table t1, t2;
 # 
 # BUG#784441: Abort on semijoin with a view as the inner table
@@ -1378,7 +1378,7 @@ 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	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; 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);
 b
@@ -1432,7 +1432,7 @@ explain
 select * from t0 where a in (select t1.a 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	t0	ALL	NULL	NULL	NULL	NULL	2	Start temporary
-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	ALL	NULL	NULL	NULL	NULL	1	Using where; End temporary
 select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
 a
@@ -1491,7 +1491,7 @@ select * from t0
 where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	Start temporary
-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	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; End temporary
 select * from t0 
@@ -1509,7 +1509,7 @@ select * from t0
 where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	Start temporary
-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	ALL	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 select * from t0 
@@ -1664,8 +1664,6 @@ FROM t4
 WHERE t4.f10 != t2.f11
 );
 f10	f10	f11	f10
-0	0	b	0
-0	0	b	0
 0	0	a	0
 0	0	a	0
 0	0	b	0
@@ -1684,6 +1682,8 @@ f10	f10	f11	f10
 0	0	b	0
 0	0	a	0
 0	0	a	0
+0	0	b	0
+0	0	b	0
 drop table t1,t2,t3,t4;
 #
 # BUG#803457: Wrong result with semijoin + view + outer join in maria-5.3-subqueries-mwl90
@@ -1706,7 +1706,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3 ) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
 f1	f2	f3	f3
 2	0	0	0
@@ -1766,7 +1766,7 @@ SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
 1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using index; Using join buffer (flat, BNL join)
-1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	5	Using where; End temporary
 SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3);
 b	a
 5	6
@@ -1872,8 +1872,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	Start 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; Using join buffer (flat, BNL join)
-1	PRIMARY	SQ3_alias2	index	NULL	PRIMARY	4	NULL	20	Using index; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Using where
+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 
@@ -2018,4 +2018,36 @@ EXECUTE st1;
 b	c	b	d
 DROP TABLE t1,t2,t3;
 set optimizer_switch=@tmp878753;
+#
+# Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off
+#
+create table t1 (a int);
+insert into t1 values (7), (1), (5), (3);
+create table t2 (a int);
+insert into t2 values (4), (1), (8), (3), (9), (2);
+set @tmp_otimizer_switch= @@optimizer_switch;
+set optimizer_switch='semijoin=on';
+set optimizer_switch='firstmatch=off';
+set optimizer_switch='semijoin_with_cache=on';
+explain
+select * from t1 where t1.a in (select t2.a from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (flat, BNL join)
+select * from t1 where t1.a in (select t2.a from t2);
+a
+1
+3
+set optimizer_switch='semijoin_with_cache=off';
+explain
+select * from t1 where t1.a in (select t2.a from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
+select * from t1 where t1.a in (select t2.a from t2);
+a
+1
+3
+set optimizer_switch= @tmp_otimizer_switch;
+drop table t1,t2;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index db99d77f50c..0de04685fac 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -867,7 +867,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Start temporary
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 2d1da2ea953..26a2f93fc86 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2029,6 +2029,38 @@ EXECUTE st1;
 b	c	b	d
 DROP TABLE t1,t2,t3;
 set optimizer_switch=@tmp878753;
+#
+# Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off
+#
+create table t1 (a int);
+insert into t1 values (7), (1), (5), (3);
+create table t2 (a int);
+insert into t2 values (4), (1), (8), (3), (9), (2);
+set @tmp_otimizer_switch= @@optimizer_switch;
+set optimizer_switch='semijoin=on';
+set optimizer_switch='firstmatch=off';
+set optimizer_switch='semijoin_with_cache=on';
+explain
+select * from t1 where t1.a in (select t2.a from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (flat, BNL join)
+select * from t1 where t1.a in (select t2.a from t2);
+a
+1
+3
+set optimizer_switch='semijoin_with_cache=off';
+explain
+select * from t1 where t1.a in (select t2.a from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
+select * from t1 where t1.a in (select t2.a from t2);
+a
+1
+3
+set optimizer_switch= @tmp_otimizer_switch;
+drop table t1,t2;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 465e482fb66..350d3fe62d7 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -1,5 +1,5 @@
 set @subselect_sj_mat_tmp= @@optimizer_switch;
-set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on');
+set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on');
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set @optimizer_switch_local_default= @@optimizer_switch;
 drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 092be494302..e4c289711a1 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -539,7 +539,7 @@ select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start temporary
-1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0))
@@ -551,7 +551,7 @@ select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 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; Start temporary
-1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0))
diff --git a/mysql-test/suite/pbxt/r/derived.result b/mysql-test/suite/pbxt/r/derived.result
index 75d5fcf8b16..4fc9216f126 100644
--- a/mysql-test/suite/pbxt/r/derived.result
+++ b/mysql-test/suite/pbxt/r/derived.result
@@ -190,13 +190,13 @@ pla_id	test
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
-1	PRIMARY	<derived2>	ALL	NULL	$hj	7	test.m2.matintnum	9	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	9	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
-1	PRIMARY	<derived2>	ALL	NULL	$hj	7	test.m2.matintnum	9	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	9	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 drop table t1,t2;
diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result
index 4cbbecf284f..f1af0ecc057 100644
--- a/mysql-test/suite/pbxt/r/subselect.result
+++ b/mysql-test/suite/pbxt/r/subselect.result
@@ -1362,7 +1362,7 @@ explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index; Start temporary
 1	PRIMARY	t1	ref	a	a	5	test.t2.a	1	100.00	Using index
-1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using where; Using index; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
 insert into t1 values (3,31);
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 13856ab31cc..c310878f0f8 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -14,7 +14,7 @@ drop view if exists v2;
 
 set @subselect_tmp=@@optimizer_switch;
 set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
-                              "semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
+                              "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 
 select (select 2);
@@ -1866,6 +1866,9 @@ drop table t1;
 # Bug#11867 queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
 #
 
+set @tmp11867_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin_with_cache=off';
+
 CREATE TABLE t1 (one int, two int, flag char(1));
 CREATE TABLE t2 (one int, two int, flag char(1));
 INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
@@ -1893,6 +1896,7 @@ explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FR
 explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
 DROP TABLE t1,t2;
 
+set optimizer_switch=@tmp11867_optimizer_switch;
 
 #
 # Bug#12392 where cond with IN predicate for rows and NULL values in table
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index f3faf44289e..e0f62a29465 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -3,7 +3,7 @@ drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
 --enable_warnings
 
 set @subselect3_tmp= @@optimizer_switch;
-set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on';
 
 #
 # 1. Subquery with GROUP/HAVING
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index db0433ce48e..98d47e7f3ea 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -6,6 +6,7 @@ drop table if exists t1,t2,t3,t4,t5,t6;
 
 set @subselect4_tmp= @@optimizer_switch;
 set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+set optimizer_switch='semijoin_with_cache=on';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 
 --echo #
diff --git a/mysql-test/t/subselect_extra.test b/mysql-test/t/subselect_extra.test
index ed1c0ef7e4d..b76f3f70516 100644
--- a/mysql-test/t/subselect_extra.test
+++ b/mysql-test/t/subselect_extra.test
@@ -12,6 +12,9 @@ drop table if exists t1,t2,t3,t4;
 drop view if exists v1,v2,v3;
 --enable_warnings
 
+set @subselect_extra_tmp=@@optimizer_switch;
+set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_extra_test,
+                              "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 
 --echo # From explain.test:
 --echo #
@@ -390,4 +393,3 @@ DROP VIEW v2;
 DROP TABLE t1,t2;
 
 set optimizer_switch= @tmp_subselect_extra_derived;
-
diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test
index 65fbbfc6409..8aba3dde72b 100644
--- a/mysql-test/t/subselect_extra_no_semijoin.test
+++ b/mysql-test/t/subselect_extra_no_semijoin.test
@@ -1,7 +1,9 @@
 set @subselect_extra_no_sj_tmp=@@optimizer_switch;
-set optimizer_switch='semijoin=off,firstmatch=off,loosescan=off';
+
+set  @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loosescan=off,semijoin_with_cache=off';
 
 --source t/subselect_extra.test
 
 set optimizer_switch= @subselect_extra_no_sj_tmp;
 
+set @optimizer_switch_for_subselect_extra_test=null;
\ No newline at end of file
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 9d618a8590e..740289ffcc8 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -776,6 +776,7 @@ eval EXPLAIN EXTENDED $query;
 eval $query;
 
 DROP TABLE t1, t2;
+
 --echo # End of Bug#48213
 
 --echo # 
@@ -1852,5 +1853,32 @@ EXECUTE st1;
 DROP TABLE t1,t2,t3;
 set optimizer_switch=@tmp878753;
 
+--echo #
+--echo # Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off
+--echo #
+
+create table t1 (a int);
+insert into t1 values (7), (1), (5), (3);
+create table t2 (a int);
+insert into t2 values (4), (1), (8), (3), (9), (2);
+
+set @tmp_otimizer_switch= @@optimizer_switch;
+set optimizer_switch='semijoin=on';
+set optimizer_switch='firstmatch=off';
+
+set optimizer_switch='semijoin_with_cache=on';
+explain
+select * from t1 where t1.a in (select t2.a from t2);
+select * from t1 where t1.a in (select t2.a from t2);
+
+set optimizer_switch='semijoin_with_cache=off';
+explain
+select * from t1 where t1.a in (select t2.a from t2);
+select * from t1 where t1.a in (select t2.a from t2);
+
+set optimizer_switch= @tmp_otimizer_switch;
+
+drop table t1,t2;
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index ef4a40172e2..a72128bf5ed 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -4,7 +4,7 @@
 #
 
 set @subselect_sj_mat_tmp= @@optimizer_switch;
-set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on');
+set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on');
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set @optimizer_switch_local_default= @@optimizer_switch;
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8d83b4589d5..c25d96ddef1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9025,7 +9025,7 @@ uint check_join_cache_usage(JOIN_TAB *tab,
   if (tab->inside_loosescan_range)
     goto no_join_cache;
 
-  if (tab->is_inner_table_of_semi_join_with_first_match() &&
+  if (tab->is_inner_table_of_semijoin() &&
       !join->allowed_semijoin_with_cache)
     goto no_join_cache;
   if (tab->is_inner_table_of_outer_join() &&
@@ -9172,7 +9172,10 @@ uint check_join_cache_usage(JOIN_TAB *tab,
 
 no_join_cache:
   if (tab->type != JT_ALL && tab->is_ref_for_hash_join())
+  {
     tab->type= JT_ALL;
+    tab->ref.key_parts= 0;
+  }
   revise_cache_usage(tab); 
   return 0;
 }
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 5476ef9b46c..1d18b80d72e 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -391,6 +391,10 @@ typedef struct st_join_table {
   {
     return first_sj_inner_tab != NULL;
   }
+  bool is_inner_table_of_semijoin()
+  {
+    return emb_sj_nest != NULL;
+  }
   bool is_inner_table_of_outer_join()
   {
     return first_inner != NULL;