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.

7 files changed, 42 insertions, 5 deletions

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;