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.

13 files changed, 70 insertions, 26 deletions

diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 1d5addeb808..aac55dcb27b 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -4,6 +4,7 @@ DROP DATABASE IF EXISTS world;
 --enable_warnings
 
 set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 set @@optimizer_switch='semijoin_with_cache=on';
 set @@optimizer_switch='outer_join_with_cache=on';
 
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 3f843cdf534..d6aa73e8010 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -12,8 +12,9 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
 drop view if exists v2;
 --enable_warnings
 
-set @save_optimizer_switch=@@optimizer_switch;
-set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+set @subselect_tmp=@@optimizer_switch;
+set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
+                              "semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
 
 select (select 2);
 explain extended select (select 2);
@@ -4066,7 +4067,7 @@ SELECT 1 FROM t1 GROUP BY
   (SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1);
 DROP TABLE t1;
 
-set @@optimizer_switch=@save_optimizer_switch;
+#Seems to be not needed here: set @@optimizer_switch=@subselect_tmp;
 --echo #
 --echo # Bug #49512 : subquery with aggregate function crash 
 --echo #   subselect_single_select_engine::exec()
@@ -4402,3 +4403,4 @@ WHERE
 DROP TABLE t1,t2,t3;
 
 --echo End of 5.3 tests
+set optimizer_switch=@subselect_tmp;
diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test
index 162bdd0d90a..b6b1388be1c 100644
--- a/mysql-test/t/subselect2.test
+++ b/mysql-test/t/subselect2.test
@@ -8,6 +8,9 @@
 drop table if exists t1, t2, t3, t4;
 --enable_warnings
 
+set @subselect2_test_tmp=@@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on';
+
 CREATE TABLE t1
 (
 DOCID VARCHAR(32)BINARY NOT NULL
@@ -168,3 +171,6 @@ SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a
                            and t2.a='1' AND t1.a=t3.b) > 0;
 
 DROP TABLE t1,t2,t3;
+
+set optimizer_switch=@subselect2_test_tmp;
+
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index 0c1523a04ca..73ee11eb78d 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -2,7 +2,8 @@
 drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
 --enable_warnings
 
-set @save_optimizer_switch=@@optimizer_switch;
+set @subselect3_tmp= @@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 
 #
 # 1. Subquery with GROUP/HAVING
@@ -538,7 +539,6 @@ SELECT a, MAX(b),
 DROP TABLE t1, t2;
 
 # The next three test cases must be executed with the IN=>EXISTS strategy
-set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
 
 #
@@ -906,7 +906,8 @@ set @@optimizer_switch='firstmatch=off,materialization=off';
 insert into t0 values(2);
 explain select * from t1 where 2 in (select a from t0);
 select * from t1 where 2 in (select a from t0);
-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);
 select * from t1 where 2 in (select a from t0);
 set @@optimizer_switch=@save_optimizer_switch;
@@ -1160,7 +1161,7 @@ drop table t1,t2,t3;
 --echo # BUG#47367 Crash in Name_resolution_context::process_error
 --echo #
 
-SET SESSION optimizer_switch = 'default,semijoin=off';
+SET SESSION optimizer_switch = 'semijoin=off';
 CREATE TABLE t1 (f1 INTEGER);
 CREATE TABLE t2 LIKE t1;
 delimiter |;
@@ -1186,4 +1187,4 @@ DROP PROCEDURE p1;
 DROP TABLE t1, t2;
 
 # The following command must be the last one the file 
-set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch=@subselect3_tmp;
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 39561d5114f..bed28400c2c 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -4,6 +4,9 @@
 drop table if exists t1,t2,t3,t4,t5,t6;
 --enable_warnings
 
+set @subselect4_tmp= @@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+
 --echo #
 --echo # Bug #46791: Assertion failed:(table->key_read==0),function unknown
 --echo #    function,file sql_base.cc
@@ -1415,3 +1418,5 @@ WHERE t2.f1 = (
                 FROM t1));
 
 drop table t1, t2, t3;
+
+set optimizer_switch=@subselect4_tmp;
diff --git a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test
index 9d6eb2775ab..172ef651407 100644
--- a/mysql-test/t/subselect_mat.test
+++ b/mysql-test/t/subselect_mat.test
@@ -5,9 +5,11 @@
 
 
 # force the use of materialization
-set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
+set @subselect_mat_test_optimizer_switch_value='materialization=on,in_to_exists=off,semijoin=off';
 
 --source t/subselect_sj_mat.test
+set @subselect_mat_test_optimizer_switch_value=null;
+
 set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 #
 # Test that the contents of the temp table of a materialized subquery is
diff --git a/mysql-test/t/subselect_no_opts.test b/mysql-test/t/subselect_no_opts.test
index d72deab45bf..a29fa5a27ac 100644
--- a/mysql-test/t/subselect_no_opts.test
+++ b/mysql-test/t/subselect_no_opts.test
@@ -1,9 +1,9 @@
 # 
 # Run subselect.test without semi-join and materialization optimizations
 #  (test in-to-exists)
-set optimizer_switch='materialization=off,semijoin=off';
 
+set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off'; 
 --source t/subselect.test
 
-set optimizer_switch=default;
+set @optimizer_switch_for_subselect_test=null; 
 
diff --git a/mysql-test/t/subselect_no_semijoin.test b/mysql-test/t/subselect_no_semijoin.test
index e9f2e0654ce..b8bcb6c4600 100644
--- a/mysql-test/t/subselect_no_semijoin.test
+++ b/mysql-test/t/subselect_no_semijoin.test
@@ -1,8 +1,8 @@
 # 
 # Run subselect.test without semi-join optimization (test materialize)
 #
-set optimizer_switch='semijoin=off';
+set @optimizer_switch_for_subselect_test='semijoin=off'; 
 
 --source t/subselect.test
 
-set optimizer_switch=default;
+set @optimizer_switch_for_subselect_test=null; 
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 0c12cfd4be0..46c8306e144 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -7,6 +7,9 @@ drop view if exists v1, v2;
 drop procedure if exists p1;
 --enable_warnings
 
+set @subselect_sj_tmp= @@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+# The 'default' value within the scope of this test:
 set @save_optimizer_switch=@@optimizer_switch;
 
 #
@@ -1396,4 +1399,4 @@ DROP TABLE t1, t2;
 set optimizer_switch=@save_802965;
 
 # The following command must be the last one the file 
-set @@optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index f4573a94407..7caf5da7b8b 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -2,6 +2,9 @@
 # DuplicateElimination strategy test
 #
 --source include/have_innodb.inc
+
+set @subselect_sj2_tmp= @@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 --disable_warnings
 drop table if exists t0, t1, t2, t3;
 drop view if exists v1;
@@ -939,3 +942,5 @@ WHERE alias2.f11 IN (
 )
 GROUP BY field2;
 drop table t1, t2, t3;
+
+set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
index 086d3bfaa4e..fb5b7ab5655 100644
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -3,6 +3,7 @@
 #
 
 set @save_optimizer_switch_jcl6=@@optimizer_switch;
+set @@optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 set @@optimizer_switch='semijoin_with_cache=on';
 set @@optimizer_switch='outer_join_with_cache=on';
 set @@optimizer_switch='join_cache_hashed=off';
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 728945be880..db798e27037 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -3,6 +3,10 @@
 # (WL#1110: Subquery optimization: materialization)
 #
 
+set @subselect_sj_mat_tmp= @@optimizer_switch;
+set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on');
+set @optimizer_switch_local_default= @@optimizer_switch;
+
 --disable_warnings
 drop table if exists t1, t2, t3, t1i, t2i, t3i;
 drop table if exists columns;
@@ -121,20 +125,26 @@ select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
 # test re-optimization/re-execution with different execution methods
 # prepare once, exec with different modes
 set @save_optimizer_switch=@@optimizer_switch;
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='semijoin=off';
 prepare st1 from
 "select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='materialization=off,in_to_exists=on';
 execute st1;
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='semijoin=off';
 execute st1;
 
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='materialization=off,in_to_exists=on';
 prepare st1 from
 "select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='semijoin=off';
 execute st1;
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='materialization=off,in_to_exists=on';
 execute st1;
 set @@optimizer_switch=@save_optimizer_switch;
 
@@ -826,18 +836,21 @@ explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 # Query with group by, executed via IN=>EXISTS
 set @save_optimizer_switch=@@optimizer_switch;
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='materialization=off,in_to_exists=on';
 explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 
 # Executed with materialization
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='semijoin=off';
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 select min(a1) from t1 where 7 in (select b1 from t2);
 # Executed with semi-join. Notice, this time we get a different result (NULL).
 # This is the only correct result of all four queries. This difference is
 # filed as BUG#40037.
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch=@optimizer_switch_local_default;
+set @@optimizer_switch='materialization=off,in_to_exists=on';
 -- echo # with MariaDB and MWL#90, this particular case is solved:
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 select min(a1) from t1 where 7 in (select b1 from t2);
@@ -875,7 +888,8 @@ INSERT INTO t2 VALUES (1, 1.789);
 INSERT INTO t2 VALUES (13, 1.454);
 
 set @save_optimizer_switch=@@optimizer_switch;
-SET @@optimizer_switch='default,semijoin=on,materialization=on';
+set @@optimizer_switch=@optimizer_switch_local_default;
+SET @@optimizer_switch='semijoin=on,materialization=on';
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 set @@optimizer_switch= @save_optimizer_switch;
@@ -899,7 +913,8 @@ CREATE TABLE t2 LIKE t1;
 INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
 
 set @save_optimizer_switch=@@optimizer_switch;
-SET @@optimizer_switch='default,semijoin=on,materialization=on';
+set @@optimizer_switch=@optimizer_switch_local_default;
+SET @@optimizer_switch='semijoin=on,materialization=on';
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
@@ -972,3 +987,6 @@ explain select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select subst
 select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select substring(b1,1,1024), count(*) from t2_1024 where b1 > '0');
 
 drop table t1_1024, t2_1024;
+
+set optimizer_switch=@subselect_sj_mat_tmp;
+
diff --git a/mysql-test/t/subselect_sj_nonmerged.test b/mysql-test/t/subselect_sj_nonmerged.test
index 5c8fb6db424..f60871c9dd9 100644
--- a/mysql-test/t/subselect_sj_nonmerged.test
+++ b/mysql-test/t/subselect_sj_nonmerged.test
@@ -6,7 +6,7 @@ drop table if exists t0, t1, t2, t3, t4;
 --enable_warnings
 
 set @save_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='materialization=on';
+set optimizer_switch='semijoin=on,materialization=on';
 
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);