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

set to 'on' by default.

18 files changed, 67 insertions, 0 deletions

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