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.

1 files changed, 48 insertions, 16 deletions

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;