Made the optimizer switch flags 'outer_join_with_cache', 'semijoin_with_cache'

set to 'on' by default.

94 files changed, 390 insertions, 23 deletions

diff --git a/mysql-test/include/common-tests.inc b/mysql-test/include/common-tests.inc
index 2273d7d688d..4b61826c347 100644
--- a/mysql-test/include/common-tests.inc
+++ b/mysql-test/include/common-tests.inc
@@ -1536,6 +1536,10 @@ select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 25
 #
 # Test of left join.
 #
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 insert into t2 (fld1, companynr) values (999999,99);
 
 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
@@ -1568,6 +1572,8 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 #
 # Joins with forms.
 #
diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result
index 4c1c49bf3e0..3a3586fdb32 100644
--- a/mysql-test/r/compress.result
+++ b/mysql-test/r/compress.result
@@ -1353,6 +1353,8 @@ fld1	fld1
 250503	250505
 250504	250505
 250505	250505
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 insert into t2 (fld1, companynr) values (999999,99);
 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 companynr	companyname
@@ -1428,6 +1430,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+SET optimizer_switch=@save_optimizer_switch;
 select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 companynr	companynr
 37	36
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 040b3b266d9..5281dd4bae4 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1472,6 +1472,8 @@ INSERT INTO t1 VALUES (1,NULL,NULL), (5,'r','r'), (7,'y','y');
 CREATE TABLE t2 (a int NOT NULL , b int, c varchar(1));
 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 
@@ -1502,6 +1504,7 @@ 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);
 a	b	c
 20	r	r
+SET optimizer_switch=@save_optimizer_switch;
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 #
@@ -1639,6 +1642,8 @@ INSERT INTO t1 VALUES ('c'), ('a');
 CREATE TABLE t2 (a int, b int, c varchar(1));
 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
@@ -1700,6 +1705,7 @@ 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;
 b	c	a
 8	c	c
+SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2,t3;
 #
 # Bug #880724: materialized const view as inner table of outer join
@@ -1709,6 +1715,8 @@ INSERT INTO t1 VALUES (9,NULL), (6,'r'), (7,'c');
 CREATE TABLE t2 (a int);
 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;
 EXPLAIN
@@ -1739,6 +1747,7 @@ a	b
 5	r
 9	y
 SET SESSION join_cache_level = default;
+SET optimizer_switch=@save_optimizer_switch;
 DROP VIEW v2;
 DROP TABLE t1,t2,t3;
 #
diff --git a/mysql-test/r/error_simulation.result b/mysql-test/r/error_simulation.result
index f76d9a8c547..6d9ec36bb1d 100644
--- a/mysql-test/r/error_simulation.result
+++ b/mysql-test/r/error_simulation.result
@@ -62,6 +62,8 @@ INSERT INTO t2 VALUES (1, 1, 'data');
 # re-scanned for every record in the outer table. if we used inner join,
 # we would need to have thousands of records and/or more columns in both
 # 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';
 EXPLAIN
 SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 );
@@ -81,6 +83,7 @@ a	a	b	filler
 8	1	1	data
 9	1	1	data
 SET SESSION debug = DEFAULT;
+SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1, t2;
 #
 # Bug#11747970 34660: CRASH WHEN FEDERATED TABLE LOSES CONNECTION DURING INSERT ... SELECT
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 2493b04488f..f3d2a567227 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -196,6 +196,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 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -214,6 +216,7 @@ SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR
 dt
 2001-01-01 01:01:01
 2001-01-01 01:01:01
+SET optimizer_switch=@save_optimizer_switch;
 drop tables t1, t2;
 #
 # Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 5b193afafb1..d5ea7311cc4 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -215,6 +215,8 @@ a1	a2	a1	a2
 10	bbb	BBB	20
 20	zzz	AAA	10
 20	zzz	BBB	20
+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;
 max(t1.a1)	max(t2.a1)
 NULL	NULL
@@ -245,6 +247,7 @@ NULL
 select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
 max(t1.a2)	max(t2.a1)
 zzz	BBB
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 CREATE TABLE t1 (a int, b int);
 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
diff --git a/mysql-test/r/func_op.result b/mysql-test/r/func_op.result
index 636163e6b29..ce755e86b4c 100644
--- a/mysql-test/r/func_op.result
+++ b/mysql-test/r/func_op.result
@@ -40,9 +40,12 @@ 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;
 a	a	b	bit_count(t2.b)
 1	1	7	3
 2	NULL	NULL	NULL
 3	3	7	3
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2;
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 7073a6cdfbc..6ac3257ca6c 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -450,6 +450,8 @@ 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;
 xID	xID1
 1	1
@@ -468,6 +470,7 @@ xID	xID1	Level
 2	2	**
 3	134	***
 4	185	****
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1;
 CREATE TABLE t1 (
 pid int(11) unsigned NOT NULL default '0',
@@ -1539,12 +1542,15 @@ USE INDEX FOR JOIN (i2)
 USE INDEX FOR JOIN (i2,i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	i2	9	NULL	144	Using index
+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));
 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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	144	
+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;
@@ -1553,11 +1559,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+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));
 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)
+SET optimizer_switch=@save_optimizer_switch;
 SHOW VARIABLES LIKE 'old';
 Variable_name	Value
 old	OFF
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 09e7cf1b5a5..4c863d25257 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -1424,6 +1424,8 @@ c	a	b	h312	e312	h312
 c	b	b	p322	m322	p322
 d	a	b	h412	e412	h412
 d	b	b	p422	m422	p422
+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
@@ -1455,6 +1457,7 @@ d	a	a	d411	a411	d411
 d	a	b	h412	e412	h412
 d	b	a	l421	i421	l421
 d	b	b	p422	m422	p422
+SET optimizer_switch=@save_optimizer_switch;
 explain 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;
@@ -1481,6 +1484,8 @@ d	a	a	a411	a411	d411
 d	a	b	e412	e412	h412
 d	b	a	i421	i421	l421
 d	b	b	m422	m422	p422
+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
@@ -1512,6 +1517,7 @@ d	a	a	a411	a411	d411
 d	a	b	e412	e412	h412
 d	b	a	i421	i421	l421
 d	b	b	m422	m422	o422
+SET optimizer_switch=@save_optimizer_switch;
 explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	17	Using where; Using index for group-by
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index d7cd1491796..4496bcf36d2 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1,5 +1,7 @@
 drop table if exists t1,t2,t3;
 drop view if exists v1,v2;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 CREATE TABLE t1 (S1 INT);
 CREATE TABLE t2 (S1 INT);
 INSERT INTO t1 VALUES (1);
@@ -1347,3 +1349,4 @@ select t2.i from t1 left join t2 on t2.i = t1.i where t1.i = '1:1:1';
 i
 01:01:01
 drop table t1,t2;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index fb4ebb3c4b7..87f0478f284 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1,4 +1,6 @@
 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_join_nested_test,'outer_join_with_cache=off');
 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);
@@ -1842,4 +1844,5 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`b` AS `b` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`b` = `test`.`t1`.`a`))) left join `test`.`t4` on((`test`.`t4`.`b` = `test`.`t3`.`a`))) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where isnull(`test`.`t3`.`a`)
 DROP TABLE t1,t2,t3,t4;
+SET optimizer_switch=@save_optimizer_switch;
 End of 5.0 tests
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index ff5a31b599b..53107b1941f 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -7,7 +7,10 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	6
+set @optimizer_switch_for_join_nested_test=@@optimizer_switch;
 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_join_nested_test,'outer_join_with_cache=off');
 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);
@@ -1851,6 +1854,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`b` AS `b` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`b` = `test`.`t1`.`a`))) left join `test`.`t4` on(((`test`.`t4`.`b` = `test`.`t3`.`a`) and (`test`.`t3`.`a` is not null)))) on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where isnull(`test`.`t3`.`a`)
 DROP TABLE t1,t2,t3,t4;
+SET optimizer_switch=@save_optimizer_switch;
 End of 5.0 tests
 CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
 CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
@@ -1962,3 +1966,4 @@ show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	1
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_join_nested_test=NULL;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index ae8ae88dc8d..02ad3a1a16a 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1,4 +1,6 @@
 drop table if exists t0,t1,t2,t3,t4,t5;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off');
 CREATE TABLE t1 (
 grp int(11) default NULL,
 a bigint(20) unsigned default NULL,
@@ -1650,3 +1652,4 @@ b	b	a	b
 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/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 11b5edf5a13..ec6e773fbae 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -7,7 +7,10 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	6
+set @optimizer_switch_for_join_outer_test=@@optimizer_switch;
 drop table if exists t0,t1,t2,t3,t4,t5;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off');
 CREATE TABLE t1 (
 grp int(11) default NULL,
 a bigint(20) unsigned default NULL,
@@ -1659,8 +1662,10 @@ b	b	a	b
 DEALLOCATE PREPARE stmt;
 SET SESSION join_cache_level=default;
 DROP TABLE t1,t2,t3;
+SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	1
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_join_outer_test=NULL;
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 6d8b28ae23d..753db6d6fc3 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -755,6 +755,8 @@ INSERT INTO t3 VALUES ('c');
 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';
 # 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
@@ -771,5 +773,6 @@ t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.
 c	c	b
 c	NULL	NULL
 c	NULL	NULL
+SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2,t3,t4;
 set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/r/optimizer_switch.result b/mysql-test/r/optimizer_switch.result
index 1606547e137..7169ee60650 100644
--- a/mysql-test/r/optimizer_switch.result
+++ b/mysql-test/r/optimizer_switch.result
@@ -4,19 +4,19 @@
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='index_merge=off,index_merge_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='index_merge_union=on';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,index_merge_sort_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch=4;
 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4'
 set optimizer_switch=NULL;
@@ -43,60 +43,60 @@ set optimizer_switch=default;
 set optimizer_switch='index_merge=off,index_merge_union=off,default';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set @@global.optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 #
 # Check index_merge's @@optimizer_switch flags
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 
 BUG#37120 optimizer_switch allowable values not according to specification
 
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,materialization=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,semijoin=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,semijoin=off,materialization=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,materialization=off,semijoin=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,semijoin=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch='default,materialization=off,loosescan=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
 set optimizer_switch=default;
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index c05cba43401..e73ff62366c 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -179,6 +179,8 @@ INSERT INTO t2 VALUES (1,50);
 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)
@@ -217,6 +219,7 @@ a	b	if(b = 1,i,if(b = 2,v,''))
 2	1	25
 3	2	123 Park Place
 4	2	453 Boardwalk
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 create table t1 (ID int not null primary key, TransactionID int not null);
 insert into t1 (ID, TransactionID) values  (1,  87), (2,  89), (3,  92), (4,  94), (5,  486), (6,  490), (7,  753), (9,  828), (10, 832), (11, 834), (12, 840);
diff --git a/mysql-test/r/pool_of_threads.result b/mysql-test/r/pool_of_threads.result
index 74ea7ba12eb..1a6ab80293f 100644
--- a/mysql-test/r/pool_of_threads.result
+++ b/mysql-test/r/pool_of_threads.result
@@ -1,3 +1,5 @@
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 drop table if exists t1,t2,t3,t4;
 CREATE TABLE t1 (
 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
@@ -1347,6 +1349,8 @@ fld1	fld1
 250503	250505
 250504	250505
 250505	250505
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 insert into t2 (fld1, companynr) values (999999,99);
 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 companynr	companyname
@@ -1422,6 +1426,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+SET optimizer_switch=@save_optimizer_switch;
 select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 companynr	companynr
 37	36
@@ -2151,6 +2156,7 @@ Privat (Private Nutzung)	Mobilfunk
 Warnings:
 Warning	1052	Column 'kundentyp' in group statement is ambiguous
 drop table t1;
+SET optimizer_switch=@save_optimizer_switch;
 SELECT sleep(5);
 SELECT sleep(5);
 # -- Success: more than --thread-pool-size normal connections not possible
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index becea752810..08944da6182 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -3012,6 +3012,8 @@ DROP TABLE t1;
 #
 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;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -3026,6 +3028,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select 1 AS `1` from `test`.`t1` `t2` left join `test`.`t1` on(1) where 1
 DEALLOCATE PREPARE stmt;
+SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1;
 #
 # Bug#54488 crash when using explain and prepared statements with subqueries
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 5f8ff00d79f..a3647c10350 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -1,6 +1,8 @@
 drop table if exists t1,t2,t3,t4,t11;
 drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;
 drop view if exists v1;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off');
 CREATE TABLE t1 (
 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
 Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -5096,3 +5098,4 @@ Warning	1292	Incorrect datetime value: 'zz'
 Warning	1292	Incorrect datetime value: 'aa'
 DROP TABLE t1;
 DROP VIEW v1;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index e0be3247282..f5104ce254d 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -7,9 +7,12 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	6
+set @optimizer_switch_for_select_test=@@optimizer_switch;
 drop table if exists t1,t2,t3,t4,t11;
 drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;
 drop view if exists v1;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off');
 CREATE TABLE t1 (
 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
 Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -5105,8 +5108,10 @@ Warning	1292	Incorrect datetime value: 'zz'
 Warning	1292	Incorrect datetime value: 'aa'
 DROP TABLE t1;
 DROP VIEW v1;
+SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	1
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_select_test=NULL;
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 5f8ff00d79f..a3647c10350 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -1,6 +1,8 @@
 drop table if exists t1,t2,t3,t4,t11;
 drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;
 drop view if exists v1;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off');
 CREATE TABLE t1 (
 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
 Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -5096,3 +5098,4 @@ Warning	1292	Incorrect datetime value: 'zz'
 Warning	1292	Incorrect datetime value: 'aa'
 DROP TABLE t1;
 DROP VIEW v1;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result
index 1a52571c2d7..eae5bb5c2c9 100644
--- a/mysql-test/r/ssl.result
+++ b/mysql-test/r/ssl.result
@@ -1350,6 +1350,8 @@ fld1	fld1
 250503	250505
 250504	250505
 250505	250505
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 insert into t2 (fld1, companynr) values (999999,99);
 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 companynr	companyname
@@ -1425,6 +1427,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+SET optimizer_switch=@save_optimizer_switch;
 select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 companynr	companynr
 37	36
diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result
index d66b0a3a7f5..d39d09322c8 100644
--- a/mysql-test/r/ssl_compress.result
+++ b/mysql-test/r/ssl_compress.result
@@ -1353,6 +1353,8 @@ fld1	fld1
 250503	250505
 250504	250505
 250505	250505
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 insert into t2 (fld1, companynr) values (999999,99);
 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 companynr	companyname
@@ -1428,6 +1430,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+SET optimizer_switch=@save_optimizer_switch;
 select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 companynr	companynr
 37	36
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 414e31f238b..2eaf4062659 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5468,6 +5468,8 @@ CREATE TABLE t2 (a int, b int) ;
 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
@@ -5482,6 +5484,7 @@ WHERE (t2.b, t1.c) NOT IN (SELECT * from t3);
 b	c
 9	NULL
 9	NULL
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 End of 5.3 tests
 #
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index f830be38321..a1501099d72 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1226,6 +1226,8 @@ CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
 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);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1261,6 +1263,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
 c1	c1
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3, t4;
 #
 # LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache()
@@ -1620,6 +1623,7 @@ CREATE TABLE t2 (f1b int, f2b int not null, f3b varchar(3) not null, PRIMARY KEY
 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
 SELECT alias2.f1 , alias2.f2
diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result
index 9bedf154cfd..1dd1655c2a6 100644
--- a/mysql-test/r/subselect_cache.result
+++ b/mysql-test/r/subselect_cache.result
@@ -1,5 +1,8 @@
 drop table if exists t1,t2,t3,t4,t5;
 drop view if exists v1;
+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';
 create table t1 (a int, b int);
 insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5);
@@ -3382,5 +3385,6 @@ pk	b
 29	3
 drop view v1;
 drop table t1,t2,t3,t4;
+SET optimizer_switch=@save_optimizer_switch;
 # restore default
 set @@optimizer_switch= default;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 10b4f5a6af7..2acca5e0af9 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5467,6 +5467,8 @@ CREATE TABLE t2 (a int, b int) ;
 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
@@ -5481,6 +5483,7 @@ WHERE (t2.b, t1.c) NOT IN (SELECT * from t3);
 b	c
 9	NULL
 9	NULL
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 End of 5.3 tests
 #
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index c809dcdfda8..b5e0f16c388 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -5463,6 +5463,8 @@ CREATE TABLE t2 (a int, b int) ;
 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
@@ -5477,6 +5479,7 @@ WHERE (t2.b, t1.c) NOT IN (SELECT * from t3);
 b	c
 9	NULL
 9	NULL
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 End of 5.3 tests
 #
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 6dabc727214..aaa162528fc 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -5472,6 +5472,8 @@ CREATE TABLE t2 (a int, b int) ;
 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
@@ -5486,6 +5488,7 @@ WHERE (t2.b, t1.c) NOT IN (SELECT * from t3);
 b	c
 9	NULL
 9	NULL
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 End of 5.3 tests
 #
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index cb2704127c4..7923d4cc68b 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -5463,6 +5463,8 @@ CREATE TABLE t2 (a int, b int) ;
 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
@@ -5477,6 +5479,7 @@ WHERE (t2.b, t1.c) NOT IN (SELECT * from t3);
 b	c
 9	NULL
 9	NULL
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 End of 5.3 tests
 #
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index a7b3cffb8d2..6bea2c8c17b 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -4,6 +4,8 @@ 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');
 set @save_optimizer_switch=@@optimizer_switch;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index cd147e55d1d..f9d70fd8943 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1,6 +1,8 @@
 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');
 drop table if exists t0, t1, t2, t3, t4, t5;
 drop view if exists v1;
 create table t0 (a int);
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 02d2fb01d93..eb5b86a3dc6 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -7,9 +7,12 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	6
+set @optimizer_switch_for_subselect_sj2_test=@@optimizer_switch;
 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');
 drop table if exists t0, t1, t2, t3, t4, t5;
 drop view if exists v1;
 create table t0 (a int);
@@ -981,3 +984,4 @@ show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	1
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_subselect_sj2_test=NULL;
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 6f3e1de4113..021eedab5b8 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -3,6 +3,8 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 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');
 drop table if exists t0, t1, t2, t3, t4, t5;
 drop view if exists v1;
 create table t0 (a int);
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 8de5af4cf56..26f6ef11878 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -9,12 +9,15 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	6
+set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch;
 drop table if exists t0, t1, t2, t3, t4, t5, t10, t11, t12;
 drop view if exists v1, v2, v3, v4;
 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');
 set @save_optimizer_switch=@@optimizer_switch;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -2242,3 +2245,4 @@ show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	1
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
+set @optimizer_switch_for_subselect_sj_test=NULL;
diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result
index fe1f9c35626..8df74536de7 100644
--- a/mysql-test/r/subselect_sj_nonmerged.result
+++ b/mysql-test/r/subselect_sj_nonmerged.result
@@ -51,6 +51,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	8	<subquery2>.max(t2.a)	1	Using where; Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 # 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);
@@ -59,6 +61,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	8	<subquery2>.max(t2.a)	1	Using where; Using index
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using temporary
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+SET optimizer_switch=@save_optimizer_switch;
 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;
 explain select * from t0, t4 where 
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 4b4ea83fcce..9b2656c17e4 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -1,5 +1,7 @@
 drop table if exists t0, t1, t2, t3, t4, t5, t6;
 drop view if exists v1, v2;
+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;
@@ -586,3 +588,4 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using index
 drop view v1;
 DROP TABLE t1,t2,t3;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 966343fa80f..1a111fe591a 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -510,6 +510,8 @@ create table t1 (id int(10) not null, cur_date datetime not null);
 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));
@@ -558,6 +560,7 @@ Note	1003	select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` f
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id	cur_date
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 SELECT 
 CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) n1, 
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 338ce242d48..13941d3f84d 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -918,6 +918,8 @@ Slow_queries	1
 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);
 NAME	PHONE	NAME	PHONE
 a	111	NULL	NULL
@@ -925,6 +927,7 @@ b	222	NULL	NULL
 d	444	d	454
 NULL	NULL	f	666
 NULL	NULL	g	777
+SET optimizer_switch=@save_optimizer_switch;
 drop  table t1;
 create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
 insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index f9e5c875726..644f7f7289e 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2,6 +2,8 @@ drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6;
 drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
 drop database if exists mysqltest;
 use test;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 create view v1 (c,d) as select a,b from t1;
 ERROR 42S02: Table 'test.t1' doesn't exist
 create temporary table t1 (a int, b int);
@@ -4372,3 +4374,4 @@ NULL	NULL	1	0
 NULL	NULL	1	0
 DROP VIEW v2;
 DROP TABLE t1, t2, t3;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/suite/pbxt/r/func_group.result b/mysql-test/suite/pbxt/r/func_group.result
index c3474e2f3ad..8817afa6462 100644
--- a/mysql-test/suite/pbxt/r/func_group.result
+++ b/mysql-test/suite/pbxt/r/func_group.result
@@ -215,6 +215,8 @@ a1	a2	a1	a2
 10	bbb	BBB	20
 20	zzz	AAA	10
 20	zzz	BBB	20
+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;
 max(t1.a1)	max(t2.a1)
 NULL	NULL
@@ -245,6 +247,7 @@ NULL
 select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
 max(t1.a2)	max(t2.a1)
 zzz	BBB
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 CREATE TABLE t1 (a int, b int);
 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
diff --git a/mysql-test/suite/pbxt/r/func_op.result b/mysql-test/suite/pbxt/r/func_op.result
index 636163e6b29..ce755e86b4c 100644
--- a/mysql-test/suite/pbxt/r/func_op.result
+++ b/mysql-test/suite/pbxt/r/func_op.result
@@ -40,9 +40,12 @@ 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;
 a	a	b	bit_count(t2.b)
 1	1	7	3
 2	NULL	NULL	NULL
 3	3	7	3
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2;
diff --git a/mysql-test/suite/pbxt/r/group_by.result b/mysql-test/suite/pbxt/r/group_by.result
index c7f87f2dc35..06cb168b3aa 100644
--- a/mysql-test/suite/pbxt/r/group_by.result
+++ b/mysql-test/suite/pbxt/r/group_by.result
@@ -450,6 +450,8 @@ 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;
 xID	xID1
 1	1
@@ -468,6 +470,7 @@ xID	xID1	Level
 2	2	**
 3	134	***
 4	185	****
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1;
 CREATE TABLE t1 (
 pid int(11) unsigned NOT NULL default '0',
diff --git a/mysql-test/suite/pbxt/r/join.result b/mysql-test/suite/pbxt/r/join.result
index 570d04bfbfc..5035f5da9bd 100644
--- a/mysql-test/suite/pbxt/r/join.result
+++ b/mysql-test/suite/pbxt/r/join.result
@@ -1,4 +1,6 @@
 drop table if exists t1,t2,t3;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 CREATE TABLE t1 (S1 INT);
 CREATE TABLE t2 (S1 INT);
 INSERT INTO t1 VALUES (1);
@@ -819,4 +821,5 @@ Field	Type	Null	Key	Default	Extra
 Name	varchar(50)	YES		NULL	
 DROP VIEW v1;
 DROP TABLE t1,t2,tv1,tv2;
+SET optimizer_switch=@save_optimizer_switch;
 End of 5.0 tests.
diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result
index 1a195cb13a8..199e56ee150 100644
--- a/mysql-test/suite/pbxt/r/join_nested.result
+++ b/mysql-test/suite/pbxt/r/join_nested.result
@@ -1,4 +1,6 @@
 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 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);
@@ -1614,3 +1616,4 @@ WHERE t1.id='5';
 id	ct	pc	nm
 5	NULL	NULL	NULL
 DROP TABLE t1,t2,t3,t4;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/suite/pbxt/r/join_outer.result b/mysql-test/suite/pbxt/r/join_outer.result
index e6ae055d811..fa41e005e07 100644
--- a/mysql-test/suite/pbxt/r/join_outer.result
+++ b/mysql-test/suite/pbxt/r/join_outer.result
@@ -1,4 +1,6 @@
 drop table if exists t0,t1,t2,t3,t4,t5;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 CREATE TABLE t1 (
 grp int(11) default NULL,
 a bigint(20) unsigned default NULL,
@@ -1194,3 +1196,4 @@ a	b
 3	3
 4	NULL
 DROP TABLE t1,t2;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result
index 51e5d95edea..869df25504a 100644
--- a/mysql-test/suite/pbxt/r/select.result
+++ b/mysql-test/suite/pbxt/r/select.result
@@ -1,6 +1,8 @@
 drop table if exists t1,t2,t3,t4,t11;
 drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa;
 drop view if exists v1;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 CREATE TABLE t1 (
 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
 Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -3639,3 +3641,4 @@ INSERT into t1 values (1), (2), (3);
 SELECT * FROM t1 LIMIT 2, -1;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1
 DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result
index c6913d32fe8..d83b216a391 100644
--- a/mysql-test/suite/pbxt/r/subselect.result
+++ b/mysql-test/suite/pbxt/r/subselect.result
@@ -1336,6 +1336,8 @@ create table t3 (a int, b int, index a (a));
 insert into t1 values (1,10), (2,20), (3,30), (4,40);
 insert into t2 values (2), (3), (4), (5);
 insert into t3 values (10,3), (20,4), (30,5);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 select * from t2 where t2.a in (select a from t1);
 a
 2
@@ -1384,6 +1386,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ref	a	a	5	test.t2.a	1	100.00	Using where; Using index; FirstMatch(t2)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 create table t1 (a int, b int);
 create table t2 (a int, b int);
@@ -2764,6 +2767,7 @@ max(fld)
 drop table t1;
 set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+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');
diff --git a/mysql-test/suite/pbxt/r/union.result b/mysql-test/suite/pbxt/r/union.result
index 82327b6d2f8..424604fb4bb 100644
--- a/mysql-test/suite/pbxt/r/union.result
+++ b/mysql-test/suite/pbxt/r/union.result
@@ -876,6 +876,8 @@ Slow_queries	0
 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);
 NAME	PHONE	NAME	PHONE
 a	111	NULL	NULL
@@ -883,6 +885,7 @@ b	222	NULL	NULL
 d	444	d	454
 NULL	NULL	f	666
 NULL	NULL	g	777
+SET optimizer_switch=@save_optimizer_switch;
 drop  table t1;
 create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
 insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
diff --git a/mysql-test/suite/pbxt/t/func_group.test b/mysql-test/suite/pbxt/t/func_group.test
index ca303030749..f3b466c463d 100644
--- a/mysql-test/suite/pbxt/t/func_group.test
+++ b/mysql-test/suite/pbxt/t/func_group.test
@@ -136,6 +136,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;
@@ -144,6 +146,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/suite/pbxt/t/func_op.test b/mysql-test/suite/pbxt/t/func_op.test
index 5ac127ad25f..4a6474df946 100644
--- a/mysql-test/suite/pbxt/t/func_op.test
+++ b/mysql-test/suite/pbxt/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;
 
 --disable_query_log
diff --git a/mysql-test/suite/pbxt/t/group_by.test b/mysql-test/suite/pbxt/t/group_by.test
index c1909668b55..b29519369c7 100644
--- a/mysql-test/suite/pbxt/t/group_by.test
+++ b/mysql-test/suite/pbxt/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;
 
 #
diff --git a/mysql-test/suite/pbxt/t/join.test b/mysql-test/suite/pbxt/t/join.test
index 02c17d8bcaa..fe73c67a6a3 100644
--- a/mysql-test/suite/pbxt/t/join.test
+++ b/mysql-test/suite/pbxt/t/join.test
@@ -4,6 +4,9 @@
 drop table if exists t1,t2,t3;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 #
 # Test different join syntaxes
 #
@@ -648,6 +651,8 @@ DESCRIBE tv2;
 DROP VIEW v1;
 DROP TABLE t1,t2,tv1,tv2;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/join_nested.test b/mysql-test/suite/pbxt/t/join_nested.test
index 98ffcbc9a8a..25c8a5a782e 100644
--- a/mysql-test/suite/pbxt/t/join_nested.test
+++ b/mysql-test/suite/pbxt/t/join_nested.test
@@ -3,6 +3,9 @@
 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='outer_join_with_cache=off';
+
 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);
@@ -1063,6 +1066,9 @@ SELECT t1.*, t4.nm
 
 DROP TABLE t1,t2,t3,t4;
 
+
+SET optimizer_switch=@save_optimizer_switch;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/join_outer.test b/mysql-test/suite/pbxt/t/join_outer.test
index a9635de7081..4c5006c6b0a 100644
--- a/mysql-test/suite/pbxt/t/join_outer.test
+++ b/mysql-test/suite/pbxt/t/join_outer.test
@@ -6,6 +6,9 @@
 drop table if exists t0,t1,t2,t3,t4,t5;
 --enable_warnings
 
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 CREATE TABLE t1 (
   grp int(11) default NULL,
   a bigint(20) unsigned default NULL,
@@ -809,6 +812,8 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
 
 DROP TABLE t1,t2;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/select.test b/mysql-test/suite/pbxt/t/select.test
index 7074f4ac364..3a1cdcf5451 100644
--- a/mysql-test/suite/pbxt/t/select.test
+++ b/mysql-test/suite/pbxt/t/select.test
@@ -13,6 +13,9 @@ 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='outer_join_with_cache=off';
+
 CREATE TABLE t1 (
   Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
   Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -3130,6 +3133,9 @@ SELECT * FROM t1 LIMIT 2, -1;
 
 DROP TABLE t1;
 
+
+SET optimizer_switch=@save_optimizer_switch;
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/subselect.test b/mysql-test/suite/pbxt/t/subselect.test
index fa838363403..5f0835e0b03 100644
--- a/mysql-test/suite/pbxt/t/subselect.test
+++ b/mysql-test/suite/pbxt/t/subselect.test
@@ -832,6 +832,8 @@ commit;
 enable_query_log;
 insert into t2 values (2), (3), (4), (5);
 insert into t3 values (10,3), (20,4), (30,5);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='semijoin_with_cache=off';
 select * from t2 where t2.a in (select a from t1);
 explain extended select * from t2 where t2.a in (select a from t1);
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -842,6 +844,7 @@ insert into t1 values (3,31);
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 
 #
@@ -1795,6 +1798,7 @@ drop table t1;
 
 set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+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));
@@ -3232,6 +3236,7 @@ INSERT INTO t1 VALUES ('a');
 SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
 DROP TABLE t1;
 
+
 --disable_query_log
 drop database pbxt;
 --enable_query_log
diff --git a/mysql-test/suite/pbxt/t/union.test b/mysql-test/suite/pbxt/t/union.test
index c216b3caceb..5a719f00cb3 100644
--- a/mysql-test/suite/pbxt/t/union.test
+++ b/mysql-test/suite/pbxt/t/union.test
@@ -481,7 +481,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/suite/vcol/inc/vcol_view.inc b/mysql-test/suite/vcol/inc/vcol_view.inc
index 64149a7bb31..abbeda60b75 100644
--- a/mysql-test/suite/vcol/inc/vcol_view.inc
+++ b/mysql-test/suite/vcol/inc/vcol_view.inc
@@ -106,6 +106,10 @@ drop view v1;
 #
 # outer join based on VIEW with WHERE clause
 #
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
+
 create table t1 (a int, 
 		 b int as (-a),
 		 c int as (-a) persistent,
@@ -119,6 +123,8 @@ select t1.a, v1.x, v1.y, v1.z from t1 left join v1 on (t1.c= v1.z);
 drop view v1;
 drop table t1;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 #
 # VIEW built over UNION
 #
diff --git a/mysql-test/suite/vcol/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result
index 88681ca305f..94c311fb8b9 100644
--- a/mysql-test/suite/vcol/r/vcol_view_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result
@@ -154,6 +154,8 @@ insert into v1 (a,e) values (60,15);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
 drop table t1;
 drop view v1;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 create table t1 (a int, 
 b int as (-a),
 c int as (-a) persistent,
@@ -174,6 +176,7 @@ a	x	y	z
 3	3	-3	-3
 drop view v1;
 drop table t1;
+SET optimizer_switch=@save_optimizer_switch;
 create table t1 (a1 int,
 b1 int as (-a1),
 c1 int as (-a1) persistent);
diff --git a/mysql-test/suite/vcol/r/vcol_view_myisam.result b/mysql-test/suite/vcol/r/vcol_view_myisam.result
index 72e0bdb16a4..b96e003e1cc 100644
--- a/mysql-test/suite/vcol/r/vcol_view_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result
@@ -154,6 +154,8 @@ insert into v1 (a,e) values (60,15);
 ERROR HY000: The target table v1 of the INSERT is not insertable-into
 drop table t1;
 drop view v1;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='outer_join_with_cache=off';
 create table t1 (a int, 
 b int as (-a),
 c int as (-a) persistent,
@@ -174,6 +176,7 @@ a	x	y	z
 3	3	-3	-3
 drop view v1;
 drop table t1;
+SET optimizer_switch=@save_optimizer_switch;
 create table t1 (a1 int,
 b1 int as (-a1),
 c1 int as (-a1) persistent);
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index e35aca9b718..953a4c8fcef 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -925,6 +925,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 +944,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 +1074,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 +1111,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 +1127,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 +1148,8 @@ SELECT * FROM t3
 
 SET SESSION join_cache_level = default;
 
+SET optimizer_switch=@save_optimizer_switch;
+
 DROP VIEW v2;
 DROP TABLE t1,t2,t3;
 
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/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_nested.test b/mysql-test/t/join_nested.test
index b617331de38..fa7b59e84c3 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -3,6 +3,9 @@
 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');
+
 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 +1274,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..f250702da7e 100644
--- a/mysql-test/t/join_nested_jcl6.test
+++ b/mysql-test/t/join_nested_jcl6.test
@@ -11,6 +11,8 @@ 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;
+
 --source t/join_nested.test
 
 #
@@ -104,3 +106,4 @@ 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;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index f88759c7b67..3bd5532ada0 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -8,6 +8,10 @@
 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');
+
+
 CREATE TABLE t1 (
   grp int(11) default NULL,
   a bigint(20) unsigned default NULL,
@@ -1209,3 +1213,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..ba0a6686a51 100644
--- a/mysql-test/t/join_outer_jcl6.test
+++ b/mysql-test/t/join_outer_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 @optimizer_switch_for_join_outer_test=@@optimizer_switch;
+
 --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;
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..9c037fa9106 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -13,6 +13,9 @@ 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');
+
 CREATE TABLE t1 (
   Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
   Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -4259,3 +4262,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..29c86679515 100644
--- a/mysql-test/t/select_jcl6.test
+++ b/mysql-test/t/select_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 @optimizer_switch_for_select_test=@@optimizer_switch;
+
 --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;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 4f6999bf611..13bbc01af17 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -4587,6 +4587,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 +4599,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/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_sj.test b/mysql-test/t/subselect_sj.test
index 4f8ed7f1643..3b419dc0d09 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -10,6 +10,9 @@ 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');
+
 # 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..5229546c08e 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -6,6 +6,9 @@
 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');
+
 --disable_warnings
 drop table if exists t0, t1, t2, t3, t4, t5;
 drop view if exists v1;
@@ -46,7 +49,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..0b20c39c183 100644
--- a/mysql-test/t/subselect_sj2_jcl6.test
+++ b/mysql-test/t/subselect_sj2_jcl6.test
@@ -11,6 +11,8 @@ 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;
+
 --source t/subselect_sj2.test
 
 --echo #
@@ -101,4 +103,5 @@ 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;
 
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
index fc539ec1a01..bfb867830c4 100644
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -13,6 +13,8 @@ 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;
+
 --source t/subselect_sj.test
 
 --echo #
@@ -91,3 +93,4 @@ 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;
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;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index d263c8aba4f..fbd0d8549f7 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -607,10 +607,12 @@ protected:
                                     OPTIMIZER_SWITCH_MATERIALIZATION | \
                                     OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\
                                     OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\
+                                    OPTIMIZER_SWITCH_OUTER_JOIN_WITH_CACHE | \
+                                    OPTIMIZER_SWITCH_SEMIJOIN_WITH_CACHE | \
                                     OPTIMIZER_SWITCH_JOIN_CACHE_INCREMENTAL | \
                                     OPTIMIZER_SWITCH_JOIN_CACHE_HASHED | \
                                     OPTIMIZER_SWITCH_JOIN_CACHE_BKA | \
-                                    OPTIMIZER_SWITCH_SUBQUERY_CACHE |\
+                                    OPTIMIZER_SWITCH_SUBQUERY_CACHE | \
                                     OPTIMIZER_SWITCH_SEMIJOIN | \
                                     OPTIMIZER_SWITCH_FIRSTMATCH | \
                                     OPTIMIZER_SWITCH_LOOSE_SCAN )
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 0767ca561df..606af24e0db 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -500,8 +500,8 @@ static const char *optimizer_switch_str="index_merge=on,index_merge_union=on,"
                                         "mrr=off,"
                                         "mrr_cost_based=off,"
                                         "mrr_sort_keys=off,"
-                                        "outer_join_with_cache=off,"
-                                        "semijoin_with_cache=off,"
+                                        "outer_join_with_cache=on,"
+                                        "semijoin_with_cache=on,"
                                         "join_cache_incremental=on,"
                                         "join_cache_hashed=on,"
                                         "join_cache_bka=on,"