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, 5 insertions, 0 deletions

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;