Change the default @@optimizer_switch setting from

  semijoin=on,firstmatch=on,loosescan=on
to
  semijoin=off,firstmatch=off,loosescan=off
Adjust the testcases:
- Modify subselect*.test and join_cache.test so that all tests
  use the same execution paths as before (i.e. optimizations that
  are being tested are enabled)
- Let all other test files run with the new default settings (i.e.
  with new optimizations disabled)
- Copy subquery testcases from these files into t/subselect_extra.test
  which will run them with new optimizations enabled.

1 files changed, 6 insertions, 5 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index e88461173df..3d5091bf36b 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1,5 +1,6 @@
 drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
-set @save_optimizer_switch=@@optimizer_switch;
+set @subselect3_tmp= @@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 create table t1 (oref int, grp int, ie int) ;
 insert into t1 (oref, grp, ie) values
 (1, 1, 1),
@@ -698,7 +699,6 @@ a	MAX(b)	test
 2	3	h
 3	4	i
 DROP TABLE t1, t2;
-set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
 CREATE TABLE t1 (a int);
 CREATE TABLE t2 (b int, PRIMARY KEY(b));
@@ -1084,7 +1084,8 @@ a
 17
 18
 19
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch='materialization=off';
 explain select * from t1 where 2 in (select a from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	11	Using where; FirstMatch
@@ -1399,7 +1400,7 @@ drop table t1,t2,t3;
 #
 # BUG#47367 Crash in Name_resolution_context::process_error
 #
-SET SESSION optimizer_switch = 'default,semijoin=off';
+SET SESSION optimizer_switch = 'semijoin=off';
 CREATE TABLE t1 (f1 INTEGER);
 CREATE TABLE t2 LIKE t1;
 CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END|
@@ -1421,4 +1422,4 @@ CALL p1;
 ERROR 42S22: Unknown column 'f1' in 'where clause'
 DROP PROCEDURE p1;
 DROP TABLE t1, t2;
-set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch=@subselect3_tmp;