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