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

set to 'on' by default.

9 files changed, 39 insertions, 0 deletions

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;
 
 #