Made join_cache_level == 2 by default.

23 files changed, 116 insertions, 3 deletions

diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 953a4c8fcef..581d01058db 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -4,9 +4,11 @@ drop view if exists v1,v2,v3,v4;
 --enable_warnings
 
 set @exit_optimizer_switch=@@optimizer_switch;
+set @exit_join_cache_level=@@join_cache_level;
 set optimizer_switch='derived_merge=on,derived_with_keys=on';
 # The 'default' value within the scope of this test:
 set @save_optimizer_switch=@@optimizer_switch;
+set join_cache_level=1;
 
 create table t1(f1 int, f11 int);
 create table t2(f2 int, f22 int);
@@ -1276,3 +1278,4 @@ drop table t1,t2,t3;
 
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
+set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index 796732fa097..0f0cbcf26d0 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -158,6 +158,10 @@ create table t3 (
 insert into t1 values (1,'yes'), (2,'no');
 insert into t2 values (1,1);
 insert into t3 values (1,1);
+
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
+
 EXPLAIN
 SELECT DISTINCT
     t1.id
@@ -195,6 +199,8 @@ WHERE
     AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
 drop table t1,t2,t3;
 
+set join_cache_level=@save_join_cache_level;
+
 #
 # Test using DISTINCT on a function that contains a group function
 # This also test the case when one doesn't use all fields in GROUP BY.
diff --git a/mysql-test/t/greedy_optimizer.test b/mysql-test/t/greedy_optimizer.test
index 5131c97f122..8f969f2562a 100644
--- a/mysql-test/t/greedy_optimizer.test
+++ b/mysql-test/t/greedy_optimizer.test
@@ -10,6 +10,9 @@
 drop table if exists t1,t2,t3,t4,t5,t6,t7;
 --enable_warnings
 
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
+
 create table t1 (
   c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer,
   primary key (c11)
@@ -384,3 +387,5 @@ SET optimizer_search_depth = DEFAULT;
 DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
 
 --echo End of 5.0 tests
+
+set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 5a48f7653e4..0feb4e30af0 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -33,6 +33,8 @@ SELECT COUNT(*) FROM CountryLanguage;
 
 show variables like 'join_buffer_size';
 
+set join_cache_level=1;
+
 show variables like 'join_cache_level';
 
 EXPLAIN
@@ -196,7 +198,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population
 DROP INDEX City_Population ON City;
 DROP INDEX City_Name ON City;
 
-set join_cache_level=default;
+set join_cache_level=1;
 
 set join_buffer_size=256;
 show variables like 'join_buffer_size';
@@ -2191,6 +2193,8 @@ insert into t2 values (1,1),(2,2);
 create table t3 (a int, b int);
 insert into t3 values (1,1),(2,2);
 
+set join_cache_level=1;
+
 explain select t1.* from t1,t2,t3;
 select t1.* from t1,t2,t3; 
 
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index fa7b59e84c3..3168e95f620 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -5,6 +5,15 @@ DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
 
 SET @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch=ifnull(@optimizer_switch_for_join_nested_test,'outer_join_with_cache=off');
+if (`select @join_cache_level_for_join_nested_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_join_nested_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_join_nested_test;
+}
+
 
 CREATE TABLE t0 (a int, b int, c int);
 CREATE TABLE t1 (a int, b int, c int);
diff --git a/mysql-test/t/join_nested_jcl6.test b/mysql-test/t/join_nested_jcl6.test
index f250702da7e..0e8646bceda 100644
--- a/mysql-test/t/join_nested_jcl6.test
+++ b/mysql-test/t/join_nested_jcl6.test
@@ -12,6 +12,7 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 
 set @optimizer_switch_for_join_nested_test=@@optimizer_switch;
+set @join_cache_level_for_join_nested_test=@@join_cache_level;
 
 --source t/join_nested.test
 
@@ -107,3 +108,4 @@ show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
 set @optimizer_switch_for_join_nested_test=NULL;
+set @join_cache_level_for_join_nested_test=NULL;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 3bd5532ada0..04816402205 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -10,7 +10,14 @@ drop table if exists t0,t1,t2,t3,t4,t5;
 
 SET @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off');
-
+if (`select @join_cache_level_for_join_outer_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_join_outer_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_join_outer_test;
+}
 
 CREATE TABLE t1 (
   grp int(11) default NULL,
diff --git a/mysql-test/t/join_outer_jcl6.test b/mysql-test/t/join_outer_jcl6.test
index ba0a6686a51..e34cc615216 100644
--- a/mysql-test/t/join_outer_jcl6.test
+++ b/mysql-test/t/join_outer_jcl6.test
@@ -12,6 +12,7 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 
 set @optimizer_switch_for_join_outer_test=@@optimizer_switch;
+set @join_cache_level_for_join_outer_test=@@join_cache_level;
 
 --source t/join_outer.test
 
@@ -20,3 +21,4 @@ show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
 set @optimizer_switch_for_join_outer_test=NULL;
+set @join_cache_level_for_join_outer_test=NULL;
diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test
index 4cd4c277a7f..fe7dc7acc79 100644
--- a/mysql-test/t/maria_mrr.test
+++ b/mysql-test/t/maria_mrr.test
@@ -78,6 +78,9 @@ INSERT INTO t3 VALUES
   (88, 442, 'y'), (99, 445, 'w'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'),
   (97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k');
 
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
+
 SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
   WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
 EXPLAIN 
@@ -90,6 +93,8 @@ EXPLAIN
   SELECT COUNT(t1.v) FROM t1, t2, t3
   WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
 
+set join_cache_level=@save_join_cache_level;
+
 DROP TABLE t1,t2,t3;
 
 --echo #
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 9c037fa9106..75ea88b6bde 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -15,6 +15,14 @@ drop view if exists v1;
 
 SET @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off');
+if (`select @join_cache_level_for_select_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_select_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_select_test;
+}
 
 CREATE TABLE t1 (
   Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
diff --git a/mysql-test/t/select_jcl6.test b/mysql-test/t/select_jcl6.test
index 29c86679515..f7c1aa988c7 100644
--- a/mysql-test/t/select_jcl6.test
+++ b/mysql-test/t/select_jcl6.test
@@ -12,6 +12,7 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 
 set @optimizer_switch_for_select_test=@@optimizer_switch;
+set @join_cache_level_for_select_test=@@join_cache_level;
 
 --source t/select.test
 
@@ -20,3 +21,4 @@ show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
 set @optimizer_switch_for_select_test=NULL;
+set @join_cache_level_for_select_test=NULL;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 13bbc01af17..d53ba706388 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -15,7 +15,16 @@ drop view if exists v2;
 set @subselect_tmp=@@optimizer_switch;
 set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
                               "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
-set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+
+if (`select @join_cache_level_for_subselect_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_subselect_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_subselect_test;
+}
+ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 
 select (select 2);
 explain extended select (select 2);
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index aadc08e18e0..ec6ceff8822 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -4,6 +4,14 @@ drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
 
 set @subselect3_tmp= @@optimizer_switch;
 set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on';
+if (`select @join_cache_level_for_subselect3_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_subselect3_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_subselect3_test;
+}
 
 #
 # 1. Subquery with GROUP/HAVING
diff --git a/mysql-test/t/subselect3_jcl6.test b/mysql-test/t/subselect3_jcl6.test
index 8d880809476..e4b1c144b93 100644
--- a/mysql-test/t/subselect3_jcl6.test
+++ b/mysql-test/t/subselect3_jcl6.test
@@ -11,9 +11,12 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set join_cache_level=6;
 show variables like 'join_cache_level';
 
+set @join_cache_level_for_subselect3_test=@@join_cache_level;
+
 --source t/subselect3.test
 
 set join_cache_level=default;
 show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch;
+set @join_cache_level_for_subselect3_test=NULL;
diff --git a/mysql-test/t/subselect_no_mat.test b/mysql-test/t/subselect_no_mat.test
index 0265ec91e88..ccd93af64ce 100644
--- a/mysql-test/t/subselect_no_mat.test
+++ b/mysql-test/t/subselect_no_mat.test
@@ -5,8 +5,13 @@ select @@optimizer_switch like '%materialization=on%';
 set optimizer_switch='materialization=off';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 
+set join_cache_level=1;
+set @join_cache_level_for_subselect_test=@@join_cache_level;
+
 --source t/subselect.test
 
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
+set @join_cache_level_for_subselect_test=NULL;
+
 
diff --git a/mysql-test/t/subselect_no_opts.test b/mysql-test/t/subselect_no_opts.test
index 724cbab6310..8a699fefaf7 100644
--- a/mysql-test/t/subselect_no_opts.test
+++ b/mysql-test/t/subselect_no_opts.test
@@ -4,6 +4,10 @@
 
 
 set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off,subquery_cache=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 
+
+set join_cache_level=1;
+set @join_cache_level_for_subselect_test=@@join_cache_level;
+
 --source t/subselect.test
 
 set @optimizer_switch_for_subselect_test=null; 
diff --git a/mysql-test/t/subselect_no_scache.test b/mysql-test/t/subselect_no_scache.test
index fe8ff749a59..a8ff559b82b 100644
--- a/mysql-test/t/subselect_no_scache.test
+++ b/mysql-test/t/subselect_no_scache.test
@@ -4,8 +4,12 @@
 select @@optimizer_switch like '%subquery_cache=on%';
 set optimizer_switch='subquery_cache=off';
 
+set join_cache_level=1;
+set @join_cache_level_for_subselect_test=@@join_cache_level;
+
 --source t/subselect.test
 
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
+set @join_cache_level_for_subselect_test=NULL;
 
diff --git a/mysql-test/t/subselect_no_semijoin.test b/mysql-test/t/subselect_no_semijoin.test
index c836c12ec50..46791667173 100644
--- a/mysql-test/t/subselect_no_semijoin.test
+++ b/mysql-test/t/subselect_no_semijoin.test
@@ -3,6 +3,10 @@
 #
 set @optimizer_switch_for_subselect_test='semijoin=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 
 
+set join_cache_level=1;
+set @join_cache_level_for_subselect_test=@@join_cache_level;
+
 --source t/subselect.test
 
 set @optimizer_switch_for_subselect_test=null; 
+set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 3b419dc0d09..6cd74b9689d 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -12,6 +12,14 @@ set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'outer_join_with_cache=off');
 SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'semijoin_with_cache=off');
+if (`select @join_cache_level_for_subselect_sj_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_subselect_sj_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_subselect_sj_test;
+}
 
 # The 'default' value within the scope of this test:
 set @save_optimizer_switch=@@optimizer_switch;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 5229546c08e..9a664ee9881 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -8,6 +8,14 @@ set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'outer_join_with_cache=off');
 SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'semijoin_with_cache=off');
+if (`select @join_cache_level_for_subselect_sj2_test is null`)
+{
+  set join_cache_level=1;
+}
+if (`select @join_cache_level_for_subselect_sj2_test is not null`)
+{
+  set join_cache_level=@join_cache_level_for_subselect_sj2_test;
+}
 
 --disable_warnings
 drop table if exists t0, t1, t2, t3, t4, t5;
diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test
index 0b20c39c183..09212ba63d0 100644
--- a/mysql-test/t/subselect_sj2_jcl6.test
+++ b/mysql-test/t/subselect_sj2_jcl6.test
@@ -12,6 +12,7 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 
 set @optimizer_switch_for_subselect_sj2_test=@@optimizer_switch;
+set @join_cache_level_for_subselect_sj2_test=@@join_cache_level;
 
 --source t/subselect_sj2.test
 
@@ -104,4 +105,5 @@ show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
 set @optimizer_switch_for_subselect_sj2_test=NULL;
+set @join_cache_level_subselect_sj2_test=NULL;
 
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
index bfb867830c4..4eeaa465b11 100644
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -14,6 +14,7 @@ set join_cache_level=6;
 show variables like 'join_cache_level';
 
 set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch;
+set @join_cache_level_for_subselect_sj_test=@@join_cache_level;
 
 --source t/subselect_sj.test
 
@@ -94,3 +95,4 @@ show variables like 'join_cache_level';
 
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
 set @optimizer_switch_for_subselect_sj_test=NULL;
+set @join_cache_level_subselect_sj_test=NULL;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index e660c63df5b..a0d24aa53ed 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -7,6 +7,8 @@ set @subselect_sj_mat_tmp= @@optimizer_switch;
 set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on');
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set @optimizer_switch_local_default= @@optimizer_switch;
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=1;
 
 --disable_warnings
 drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i;
@@ -1497,4 +1499,5 @@ DROP TABLE t1;
 
 --echo # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
+set join_cache_level=@save_join_cache_level;