Merge

41 files changed, 255 insertions, 4 deletions

diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index e35aca9b718..581d01058db 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -4,9 +4,11 @@ drop view if exists v1,v2,v3,v4;
 --enable_warnings
 
 set @exit_optimizer_switch=@@optimizer_switch;
+set @exit_join_cache_level=@@join_cache_level;
 set optimizer_switch='derived_merge=on,derived_with_keys=on';
 # The 'default' value within the scope of this test:
 set @save_optimizer_switch=@@optimizer_switch;
+set join_cache_level=1;
 
 create table t1(f1 int, f11 int);
 create table t2(f2 int, f22 int);
@@ -925,6 +927,8 @@ INSERT INTO t2 VALUES (4,3,'r');
 
 CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 SET SESSION optimizer_switch='derived_with_keys=off';
 EXPLAIN
 SELECT * FROM t3 
@@ -942,6 +946,7 @@ SELECT * FROM t3
 SELECT * FROM t3 
   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);
+SET optimizer_switch=@save_optimizer_switch;
 
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
@@ -1071,6 +1076,9 @@ INSERT INTO t2 VALUES (29,8,'c'), (39,7,'b');
 
 CREATE TABLE t3 (b int);
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 EXPLAIN EXTENDED
 SELECT t.b, t.c, t1.a
 FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
@@ -1105,6 +1113,8 @@ SELECT t.b, t.c, t1.a
 FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
   WHERE t.b <> 0 AND t.c = t1.a;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 DROP TABLE t1,t2,t3;
 
 --echo #
@@ -1119,6 +1129,9 @@ INSERT INTO t2 VALUES (6);
 
 CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 SET SESSION optimizer_switch = 'derived_with_keys=on';
 SET SESSION join_cache_level = 4;
 
@@ -1137,6 +1150,8 @@ SELECT * FROM t3
 
 SET SESSION join_cache_level = default;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 DROP VIEW v2;
 DROP TABLE t1,t2,t3;
 
@@ -1263,3 +1278,4 @@ drop table t1,t2,t3;
 
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
+set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index 796732fa097..0f0cbcf26d0 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -158,6 +158,10 @@ create table t3 (
 insert into t1 values (1,'yes'), (2,'no');
 insert into t2 values (1,1);
 insert into t3 values (1,1);
+
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
+
 EXPLAIN
 SELECT DISTINCT
     t1.id
@@ -195,6 +199,8 @@ WHERE
     AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
 drop table t1,t2,t3;
 
+set join_cache_level=@save_join_cache_level;
+
 #
 # Test using DISTINCT on a function that contains a group function
 # This also test the case when one doesn't use all fields in GROUP BY.
diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test
index 15d172cc7e6..5dcc1fa9dcf 100644
--- a/mysql-test/t/error_simulation.test
+++ b/mysql-test/t/error_simulation.test
@@ -78,6 +78,9 @@ INSERT INTO t2 VALUES (1, 1, 'data');
 --echo # we would need to have thousands of records and/or more columns in both
 --echo # tables so that the join buffer is filled and re-scans are triggered).
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 SET SESSION debug = '+d,only_one_Unique_may_be_created';
 
 --replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x
@@ -87,6 +90,8 @@ SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 );
 
 SET SESSION debug = DEFAULT;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 DROP TABLE t1, t2;
 
 
diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test
index 6dbbdf5f28e..8700cd48c45 100644
--- a/mysql-test/t/explain.test
+++ b/mysql-test/t/explain.test
@@ -180,6 +180,8 @@ create table t2 (dt datetime not null);
 insert into t1 values ('2001-01-01 1:1:1', '1:1:1'),
 ('2001-01-01 1:1:1', '1:1:1');
 insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1');
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 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 );
 flush tables;
@@ -188,6 +190,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' ); 
 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' );
+SET optimizer_switch=@save_optimizer_switch;
 drop tables t1, t2;
 
 --echo #
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 65b5b3936cc..de0eac10927 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -138,6 +138,8 @@ insert into t2 values('AAA', 10, 0.5);
 insert into t2 values('BBB', 20, 1.0);
 select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
 select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
 select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
@@ -146,6 +148,7 @@ select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;
 select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10;
 select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
 select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 
 #
diff --git a/mysql-test/t/func_op.test b/mysql-test/t/func_op.test
index 0a4f5034f4c..13fa40b513b 100644
--- a/mysql-test/t/func_op.test
+++ b/mysql-test/t/func_op.test
@@ -28,7 +28,10 @@ create table t1(a int);
 create table t2(a int, b int);
 insert into t1 values (1), (2), (3);
 insert into t2 values (1, 7), (3, 7);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 select t1.a, t2.a, t2.b, bit_count(t2.b) from t1 left join t2 on t1.a=t2.a;
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2;
 
 # End of 4.1 tests
diff --git a/mysql-test/t/greedy_optimizer.test b/mysql-test/t/greedy_optimizer.test
index 5131c97f122..8f969f2562a 100644
--- a/mysql-test/t/greedy_optimizer.test
+++ b/mysql-test/t/greedy_optimizer.test
@@ -10,6 +10,9 @@
 drop table if exists t1,t2,t3,t4,t5,t6,t7;
 --enable_warnings
 
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
+
 create table t1 (
   c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer,
   primary key (c11)
@@ -384,3 +387,5 @@ SET optimizer_search_depth = DEFAULT;
 DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
 
 --echo End of 5.0 tests
+
+set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index ec64aaed18f..5d7421904d2 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -338,8 +338,11 @@ drop table t1,t2;
 CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
 ));
 insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS  on S.ID1 between yS.ID1 and yS.ID2;
 select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS  on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1;
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1;
 
 #
@@ -1031,16 +1034,22 @@ EXPLAIN SELECT a FROM t1
   USE INDEX FOR JOIN (i2) 
   USE INDEX FOR JOIN (i2,i2);
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 EXPLAIN SELECT 1 FROM t1 WHERE a IN
   (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+SET optimizer_switch=@save_optimizer_switch;
 
 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; 
 EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 EXPLAIN SELECT 1 FROM t2 WHERE a IN
   (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+SET optimizer_switch=@save_optimizer_switch;
 
 SHOW VARIABLES LIKE 'old';  
 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index 391d997d8b6..c3fc1f4eab7 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -428,6 +428,8 @@ select a1,a2,b,c,min(c), max(c) from t1
 where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
 group by a1,a2,b;
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 explain 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
@@ -439,6 +441,7 @@ where exists ( select * from t2
                where t2.c in (select c from t3 where t3.c > t1.b) and
                t2.c > 'b1' )
 group by a1,a2,b;
+SET optimizer_switch=@save_optimizer_switch;
 
 # correlated subselect that references the min/max argument
 explain select a1,a2,b,c,min(c), max(c) from t1
@@ -449,6 +452,8 @@ select a1,a2,b,c,min(c), max(c) from t1
 where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
 group by a1,a2,b;
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 explain 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
@@ -460,6 +465,7 @@ where exists ( select * from t2
                where t2.c in (select c from t3 where t3.c > t1.c) and
                t2.c > 'b1' )
 group by a1,a2,b;
+SET optimizer_switch=@save_optimizer_switch;
 
 
 # A,B,C) Predicates referencing mixed classes of attributes
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 82e67904e9f..bfe5f085e93 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -5,6 +5,9 @@ drop table if exists t1,t2,t3;
 drop view if exists v1,v2;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 #
 # Test different join syntaxes
 #
@@ -1025,3 +1028,4 @@ insert into t2 values ('1:1:1');
 select t2.i from t1 left join t2 on t2.i = t1.i where t1.i = '1:1:1';
 drop table t1,t2;
 
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 5a48f7653e4..0feb4e30af0 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -33,6 +33,8 @@ SELECT COUNT(*) FROM CountryLanguage;
 
 show variables like 'join_buffer_size';
 
+set join_cache_level=1;
+
 show variables like 'join_cache_level';
 
 EXPLAIN
@@ -196,7 +198,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population
 DROP INDEX City_Population ON City;
 DROP INDEX City_Name ON City;
 
-set join_cache_level=default;
+set join_cache_level=1;
 
 set join_buffer_size=256;
 show variables like 'join_buffer_size';
@@ -2191,6 +2193,8 @@ insert into t2 values (1,1),(2,2);
 create table t3 (a int, b int);
 insert into t3 values (1,1),(2,2);
 
+set join_cache_level=1;
+
 explain select t1.* from t1,t2,t3;
 select t1.* from t1,t2,t3; 
 
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index b617331de38..3168e95f620 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -3,6 +3,18 @@
 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_join_nested_test,'outer_join_with_cache=off');
+if (`select @join_cache_level_for_join_nested_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_join_nested_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_join_nested_test;
+}
+
+
 CREATE TABLE t0 (a int, b int, c int);
 CREATE TABLE t1 (a int, b int, c int);
 CREATE TABLE t2 (a int, b int, c int);
@@ -1271,5 +1283,7 @@ SELECT * FROM t1 LEFT JOIN
 
 DROP TABLE t1,t2,t3,t4;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 --echo End of 5.0 tests
 
diff --git a/mysql-test/t/join_nested_jcl6.test b/mysql-test/t/join_nested_jcl6.test
index 809755b1fbf..0e8646bceda 100644
--- a/mysql-test/t/join_nested_jcl6.test
+++ b/mysql-test/t/join_nested_jcl6.test
@@ -11,6 +11,9 @@ set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set join_cache_level=6;
 show variables like 'join_cache_level';
 
+set @optimizer_switch_for_join_nested_test=@@optimizer_switch;
+set @join_cache_level_for_join_nested_test=@@join_cache_level;
+
 --source t/join_nested.test
 
 #
@@ -104,3 +107,5 @@ set join_cache_level=default;
 show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_join_nested_test=NULL;
+set @join_cache_level_for_join_nested_test=NULL;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index f88759c7b67..04816402205 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -8,6 +8,17 @@
 drop table if exists t0,t1,t2,t3,t4,t5;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off');
+if (`select @join_cache_level_for_join_outer_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_join_outer_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_join_outer_test;
+}
+
 CREATE TABLE t1 (
   grp int(11) default NULL,
   a bigint(20) unsigned default NULL,
@@ -1209,3 +1220,5 @@ DEALLOCATE PREPARE stmt;
 SET SESSION join_cache_level=default;
 
 DROP TABLE t1,t2,t3;
+
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/join_outer_jcl6.test b/mysql-test/t/join_outer_jcl6.test
index 025e44493af..e34cc615216 100644
--- a/mysql-test/t/join_outer_jcl6.test
+++ b/mysql-test/t/join_outer_jcl6.test
@@ -11,9 +11,14 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set join_cache_level=6;
 show variables like 'join_cache_level';
 
+set @optimizer_switch_for_join_outer_test=@@optimizer_switch;
+set @join_cache_level_for_join_outer_test=@@join_cache_level;
+
 --source t/join_outer.test
 
 set join_cache_level=default;
 show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_join_outer_test=NULL;
+set @join_cache_level_for_join_outer_test=NULL;
diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test
index 4cd4c277a7f..fe7dc7acc79 100644
--- a/mysql-test/t/maria_mrr.test
+++ b/mysql-test/t/maria_mrr.test
@@ -78,6 +78,9 @@ INSERT INTO t3 VALUES
   (88, 442, 'y'), (99, 445, 'w'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'),
   (97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k');
 
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
+
 SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
   WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
 EXPLAIN 
@@ -90,6 +93,8 @@ EXPLAIN
   SELECT COUNT(t1.v) FROM t1, t2, t3
   WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
 
+set join_cache_level=@save_join_cache_level;
+
 DROP TABLE t1,t2,t3;
 
 --echo #
diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test
index bbff6c30e56..0e306a850c5 100644
--- a/mysql-test/t/myisam_icp.test
+++ b/mysql-test/t/myisam_icp.test
@@ -245,6 +245,9 @@ CREATE TABLE t4 ( b int, c varchar(1), KEY (b));
 INSERT INTO t4 VALUES (7,'c');
 INSERT INTO t4 VALUES (7,'c');
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 --echo # Must be t1,t2,t3,t4, with t4 having Full-scan-on-NULL but not Using index condition
 explain 
 SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b
@@ -255,6 +258,8 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b
 WHERE 
   t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c);
 
+SET optimizer_switch=@save_optimizer_switch;
+
 DROP TABLE t1,t2,t3,t4;
 
 set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 0fc0d125c1a..c7a958b293a 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -157,6 +157,9 @@ INSERT INTO t2 VALUES (2,25);
 INSERT INTO t3 VALUES (1,'123 Park Place');
 INSERT INTO t3 VALUES (2,'453 Boardwalk');
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
 LEFT JOIN t2 USING(c)
@@ -179,6 +182,8 @@ LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c
 ORDER BY a;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 drop table t1,t2,t3;
 
 #
diff --git a/mysql-test/t/pool_of_threads.test b/mysql-test/t/pool_of_threads.test
index 530038cee91..5cde9a9fb4b 100644
--- a/mysql-test/t/pool_of_threads.test
+++ b/mysql-test/t/pool_of_threads.test
@@ -2,11 +2,13 @@
 # and run a number of tests
 
 -- source include/have_pool_of_threads.inc
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 # Slow test, don't run during staging part
 -- source include/not_staging.inc
 -- source include/long_test.inc
 -- source include/common-tests.inc
-
+SET optimizer_switch=@save_optimizer_switch;
 
 # Test that we cannot have more simultaneous connections than
 # --thread-pool-size on the standard port, but _can_ have additional
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 357e7d4fe8f..a3b064acb04 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -3095,10 +3095,13 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1(a INT);
 INSERT INTO t1 VALUES (1),(2);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 PREPARE stmt FROM 'EXPLAIN EXTENDED SELECT 1 FROM t1 RIGHT JOIN t1 t2 ON 1';
 EXECUTE stmt;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
+SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1;
 
 --echo #
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 0106c3d54fb..75ea88b6bde 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -13,6 +13,17 @@ drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;
 drop view if exists v1;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off');
+if (`select @join_cache_level_for_select_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_select_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_select_test;
+}
+
 CREATE TABLE t1 (
   Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
   Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -4259,3 +4270,5 @@ SELECT * FROM t1 WHERE f1 = 'zz' AND f1 <= 'aa' ;
 SELECT * FROM v1 HAVING f1 = 'zz' AND f1 <= 'aa' ;
 DROP TABLE t1;
 DROP VIEW v1;
+
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/select_jcl6.test b/mysql-test/t/select_jcl6.test
index 295efa632db..f7c1aa988c7 100644
--- a/mysql-test/t/select_jcl6.test
+++ b/mysql-test/t/select_jcl6.test
@@ -11,9 +11,14 @@ set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set join_cache_level=6;
 show variables like 'join_cache_level';
 
+set @optimizer_switch_for_select_test=@@optimizer_switch;
+set @join_cache_level_for_select_test=@@join_cache_level;
+
 --source t/select.test
 
 set join_cache_level=default;
 show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_select_test=NULL;
+set @join_cache_level_for_select_test=NULL;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 4f6999bf611..d53ba706388 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -15,7 +15,16 @@ 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,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';
+
+if (`select @join_cache_level_for_subselect_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_subselect_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_subselect_test;
+}
+ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 
 select (select 2);
 explain extended select (select 2);
@@ -4587,6 +4596,9 @@ INSERT INTO t2 VALUES (20,9),(20,9);
 create table t3 (d int, e int);
 insert into t3 values (2, 9), (3,10);
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 EXPLAIN
 SELECT t2.b , t1.c
 FROM t2 LEFT JOIN t1 ON t1.c < 3
@@ -4596,6 +4608,8 @@ SELECT t2.b , t1.c
 FROM t2 LEFT JOIN t1 ON t1.c < 3
 WHERE (t2.b, t1.c) NOT IN (SELECT * from t3);
 
+SET optimizer_switch=@save_optimizer_switch;
+
 drop table t1, t2, t3;
 
 --echo End of 5.3 tests
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index aadc08e18e0..ec6ceff8822 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -4,6 +4,14 @@ 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,semijoin_with_cache=on';
+if (`select @join_cache_level_for_subselect3_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_subselect3_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_subselect3_test;
+}
 
 #
 # 1. Subquery with GROUP/HAVING
diff --git a/mysql-test/t/subselect3_jcl6.test b/mysql-test/t/subselect3_jcl6.test
index 8d880809476..e4b1c144b93 100644
--- a/mysql-test/t/subselect3_jcl6.test
+++ b/mysql-test/t/subselect3_jcl6.test
@@ -11,9 +11,12 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set join_cache_level=6;
 show variables like 'join_cache_level';
 
+set @join_cache_level_for_subselect3_test=@@join_cache_level;
+
 --source t/subselect3.test
 
 set join_cache_level=default;
 show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch;
+set @join_cache_level_for_subselect3_test=NULL;
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 0fd89bee42b..aa3cb30c6f3 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -991,6 +991,9 @@ INSERT INTO t3 VALUES ('a'), ('b'), ('c');
 CREATE TABLE t4 (c1 varchar(1) primary key);
 INSERT INTO t4 VALUES ('k'), ('d');
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 EXPLAIN
 SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
@@ -1003,6 +1006,9 @@ SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SEL
 EXPLAIN
 SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
 SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+
+SET optimizer_switch=@save_optimizer_switch;
+
 drop table t1, t2, t3, t4;
 
 --echo #
@@ -1297,6 +1303,8 @@ INSERT INTO t2 VALUES
 (10,5,'d1d');
 
 set @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 set @@optimizer_switch = 'materialization=off';
 
 EXPLAIN
diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test
index 6bf5028b6cf..0abd20cc50e 100644
--- a/mysql-test/t/subselect_cache.test
+++ b/mysql-test/t/subselect_cache.test
@@ -4,6 +4,9 @@ drop table if exists t1,t2,t3,t4,t5;
 drop view if exists v1;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+SET optimizer_switch='semijoin_with_cache=off';
 
 set optimizer_switch='subquery_cache=on';
 
@@ -1698,5 +1701,7 @@ SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 );
 drop view v1;
 drop table t1,t2,t3,t4;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 --echo # restore default
 set @@optimizer_switch= default;
diff --git a/mysql-test/t/subselect_no_mat.test b/mysql-test/t/subselect_no_mat.test
index 0265ec91e88..ccd93af64ce 100644
--- a/mysql-test/t/subselect_no_mat.test
+++ b/mysql-test/t/subselect_no_mat.test
@@ -5,8 +5,13 @@ select @@optimizer_switch like '%materialization=on%';
 set optimizer_switch='materialization=off';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 
+set join_cache_level=1;
+set @join_cache_level_for_subselect_test=@@join_cache_level;
+
 --source t/subselect.test
 
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
+set @join_cache_level_for_subselect_test=NULL;
+
 
diff --git a/mysql-test/t/subselect_no_opts.test b/mysql-test/t/subselect_no_opts.test
index 724cbab6310..8a699fefaf7 100644
--- a/mysql-test/t/subselect_no_opts.test
+++ b/mysql-test/t/subselect_no_opts.test
@@ -4,6 +4,10 @@
 
 
 set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off,subquery_cache=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 
+
+set join_cache_level=1;
+set @join_cache_level_for_subselect_test=@@join_cache_level;
+
 --source t/subselect.test
 
 set @optimizer_switch_for_subselect_test=null; 
diff --git a/mysql-test/t/subselect_no_scache.test b/mysql-test/t/subselect_no_scache.test
index fe8ff749a59..a8ff559b82b 100644
--- a/mysql-test/t/subselect_no_scache.test
+++ b/mysql-test/t/subselect_no_scache.test
@@ -4,8 +4,12 @@
 select @@optimizer_switch like '%subquery_cache=on%';
 set optimizer_switch='subquery_cache=off';
 
+set join_cache_level=1;
+set @join_cache_level_for_subselect_test=@@join_cache_level;
+
 --source t/subselect.test
 
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
+set @join_cache_level_for_subselect_test=NULL;
 
diff --git a/mysql-test/t/subselect_no_semijoin.test b/mysql-test/t/subselect_no_semijoin.test
index c836c12ec50..46791667173 100644
--- a/mysql-test/t/subselect_no_semijoin.test
+++ b/mysql-test/t/subselect_no_semijoin.test
@@ -3,6 +3,10 @@
 #
 set @optimizer_switch_for_subselect_test='semijoin=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 
 
+set join_cache_level=1;
+set @join_cache_level_for_subselect_test=@@join_cache_level;
+
 --source t/subselect.test
 
 set @optimizer_switch_for_subselect_test=null; 
+set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 55074787700..2b64de47439 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -10,6 +10,17 @@ drop procedure if exists p1;
 set @subselect_sj_tmp= @@optimizer_switch;
 set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'outer_join_with_cache=off');
+SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'semijoin_with_cache=off');
+if (`select @join_cache_level_for_subselect_sj_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_subselect_sj_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_subselect_sj_test;
+}
+
 # The 'default' value within the scope of this test:
 set @save_optimizer_switch=@@optimizer_switch;
 
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 2ec15d4dfae..9a664ee9881 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -6,6 +6,17 @@
 set @subselect_sj2_tmp= @@optimizer_switch;
 set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'outer_join_with_cache=off');
+SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'semijoin_with_cache=off');
+if (`select @join_cache_level_for_subselect_sj2_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_subselect_sj2_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_subselect_sj2_test;
+}
+
 --disable_warnings
 drop table if exists t0, t1, t2, t3, t4, t5;
 drop view if exists v1;
@@ -46,7 +57,6 @@ create table t3 (
 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);
 select * from t3 where b in (select a from t1);
 
diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test
index 95f84a9e89e..09212ba63d0 100644
--- a/mysql-test/t/subselect_sj2_jcl6.test
+++ b/mysql-test/t/subselect_sj2_jcl6.test
@@ -11,6 +11,9 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set join_cache_level=6;
 show variables like 'join_cache_level';
 
+set @optimizer_switch_for_subselect_sj2_test=@@optimizer_switch;
+set @join_cache_level_for_subselect_sj2_test=@@join_cache_level;
+
 --source t/subselect_sj2.test
 
 --echo #
@@ -101,4 +104,6 @@ set join_cache_level=default;
 show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_subselect_sj2_test=NULL;
+set @join_cache_level_subselect_sj2_test=NULL;
 
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
index fc539ec1a01..4eeaa465b11 100644
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -13,6 +13,9 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set join_cache_level=6;
 show variables like 'join_cache_level';
 
+set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch;
+set @join_cache_level_for_subselect_sj_test=@@join_cache_level;
+
 --source t/subselect_sj.test
 
 --echo #
@@ -91,3 +94,5 @@ set join_cache_level=default;
 show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_subselect_sj_test=NULL;
+set @join_cache_level_subselect_sj_test=NULL;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index e660c63df5b..a0d24aa53ed 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -7,6 +7,8 @@ set @subselect_sj_mat_tmp= @@optimizer_switch;
 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;
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
 
 --disable_warnings
 drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i;
@@ -1497,4 +1499,5 @@ DROP TABLE t1;
 
 --echo # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
+set join_cache_level=@save_join_cache_level;
 
diff --git a/mysql-test/t/subselect_sj_nonmerged.test b/mysql-test/t/subselect_sj_nonmerged.test
index 4f50b4cbc4d..e47e72ffe97 100644
--- a/mysql-test/t/subselect_sj_nonmerged.test
+++ b/mysql-test/t/subselect_sj_nonmerged.test
@@ -55,13 +55,19 @@ explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b)
 --echo # Compare to this which really will have 50 record combinations:
 explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b, t1.b);
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 --echo # Outer joins also work:
 explain select * from t3 
 where a in (select max(t2.a) from t1 left join t2 on t1.a=t2.a group by t2.b, t1.b);
 
+SET optimizer_switch=@save_optimizer_switch;
+
 #
 # Check if joins on the outer side also work
 #
+
 create table t4 (a int, b int, filler char(20), unique key(a,b));
 insert into t4 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t0 A, t0 B; # 100 rows
 explain select * from t0, t4 where 
diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test
index 3b584ce2b38..dc32618eb8c 100644
--- a/mysql-test/t/table_elim.test
+++ b/mysql-test/t/table_elim.test
@@ -6,6 +6,9 @@ drop table if exists t0, t1, t2, t3, t4, t5, t6;
 drop view if exists v1, v2;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3);
 create table t0 as select * from t1;
@@ -518,3 +521,4 @@ EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
 drop view v1;
 DROP TABLE t1,t2,t3;
 
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 4e091e14b30..f9ee8dfd5d3 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -359,6 +359,9 @@ create table t2 (id int(10) not null, cur_date date not null);
 insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22');
 insert into t2 (id, cur_date) values (1, '2007-04-25');
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
+
 explain extended
 select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
@@ -386,6 +389,8 @@ where id in (select id from t2 as x1 where (t2.cur_date is null));
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 
+SET optimizer_switch=@save_optimizer_switch;
+
 drop table t1,t2;
 
 
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 507f0ffa09f..d3bc94961ef 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -563,7 +563,10 @@ drop table t1;
 #
 create table t1 (   RID int(11) not null default '0',   IID int(11) not null default '0',    nada varchar(50)  not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;
 insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
+SET optimizer_switch=@save_optimizer_switch;
 drop  table t1;
 
 #
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 7486ffc38f8..821bbe055e6 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -5,6 +5,9 @@ drop database if exists mysqltest;
 --enable_warnings
 use test;
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 #
 # some basic test of views and its functionality
 #
@@ -4308,3 +4311,5 @@ SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM
 
 DROP VIEW v2;
 DROP TABLE t1, t2, t3;
+
+SET optimizer_switch=@save_optimizer_switch;