diff options
54 files changed, 205 insertions, 13 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 5281dd4bae4..a109aa3198b 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1,8 +1,10 @@ drop table if exists t1,t2; drop view if exists v1,v2,v3,v4; set @exit_optimizer_switch=@@optimizer_switch; +set @exit_join_cache_level=@@join_cache_level; set optimizer_switch='derived_merge=on,derived_with_keys=on'; 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); insert into t1 values(1,1),(2,2),(3,3),(5,5),(9,9),(7,7); @@ -1871,3 +1873,4 @@ col_varchar_key pk col_varchar_key col_varchar_nokey set max_heap_table_size= @tmp_882994; drop table t1,t2,t3; set optimizer_switch=@exit_optimizer_switch; +set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index eafd16440b0..99d98d2a9bc 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -280,6 +280,8 @@ unique (id, idx) 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 @@ -326,6 +328,7 @@ AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); id 2 drop table t1,t2,t3; +set join_cache_level=@save_join_cache_level; create table t1 (a int not null, b int not null, t time); insert into t1 values (1,1,"00:06:15"),(1,2,"00:06:15"),(1,2,"00:30:15"),(1,3,"00:06:15"),(1,3,"00:30:15"); select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b; diff --git a/mysql-test/r/greedy_optimizer.result b/mysql-test/r/greedy_optimizer.result index b4844785318..b4c3f4ec9e0 100644 --- a/mysql-test/r/greedy_optimizer.result +++ b/mysql-test/r/greedy_optimizer.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7; +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) @@ -732,3 +734,4 @@ t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_1.l) ON t5.d = t4.d SET optimizer_search_depth = DEFAULT; DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1; End of 5.0 tests +set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index f2fda746e5e..f4c995ab1ac 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -40,6 +40,7 @@ COUNT(*) show variables like 'join_buffer_size'; Variable_name Value join_buffer_size 131072 +set join_cache_level=1; show variables like 'join_cache_level'; Variable_name Value join_cache_level 1 @@ -453,7 +454,7 @@ Côte d?Ivoire 14786000 NULL NULL Czech Republic 10278100 NULL NULL 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'; Variable_name Value @@ -776,7 +777,7 @@ Variable_name Value join_buffer_size 131072 show variables like 'join_cache_level'; Variable_name Value -join_cache_level 1 +join_cache_level 2 DROP DATABASE world; CREATE DATABASE world; use world; @@ -2690,7 +2691,7 @@ Variable_name Value join_buffer_size 131072 show variables like 'join_cache_level'; Variable_name Value -join_cache_level 1 +join_cache_level 2 set join_cache_level=1; SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; @@ -3875,6 +3876,7 @@ create table t2 (a int, b int); 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 87f0478f284..dfb1a8c72d3 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1,6 +1,7 @@ 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'); +set join_cache_level=1; CREATE TABLE t0 (a int, b int, c int); CREATE TABLE t1 (a int, b int, c int); CREATE TABLE t2 (a int, b int, c int); diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index 53107b1941f..69eb4532c73 100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -8,9 +8,11 @@ show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 set @optimizer_switch_for_join_nested_test=@@optimizer_switch; +set @join_cache_level_for_join_nested_test=@@join_cache_level; 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'); +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); CREATE TABLE t2 (a int, b int, c int); @@ -1964,6 +1966,7 @@ DROP TABLE t5,t6,t7,t8; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value -join_cache_level 1 +join_cache_level 2 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/r/join_outer.result b/mysql-test/r/join_outer.result index 02ad3a1a16a..9e84010d5ce 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1,6 +1,7 @@ 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'); +set join_cache_level=1; CREATE TABLE t1 ( grp int(11) default NULL, a bigint(20) unsigned default NULL, diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index ec6e773fbae..ab9e437c6bf 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -8,9 +8,11 @@ show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 set @optimizer_switch_for_join_outer_test=@@optimizer_switch; +set @join_cache_level_for_join_outer_test=@@join_cache_level; 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'); +set join_cache_level=@join_cache_level_for_join_outer_test; CREATE TABLE t1 ( grp int(11) default NULL, a bigint(20) unsigned default NULL, @@ -1666,6 +1668,7 @@ SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value -join_cache_level 1 +join_cache_level 2 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/r/maria_mrr.result b/mysql-test/r/maria_mrr.result index 652bea93e7a..b3f5ada833d 100644 --- a/mysql-test/r/maria_mrr.result +++ b/mysql-test/r/maria_mrr.result @@ -347,6 +347,8 @@ INSERT INTO t3 SELECT * FROM t1; 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; COUNT(t1.v) @@ -369,6 +371,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx 7 NULL 15 Using index 1 SIMPLE t2 ALL PRIMARY,idx NULL NULL NULL 16 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 ref PRIMARY,idx idx 3 test.t2.v 2 Using index condition; Using where +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; # # BUG#671361: virtual int Mrr_ordered_index_reader::refill_buffer(): Assertion `!know_key_tuple_params diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index a3647c10350..29a6bb4bf35 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3,6 +3,7 @@ drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 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'); +set join_cache_level=1; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index f5104ce254d..fd23d6cc2d6 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -8,11 +8,13 @@ show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 set @optimizer_switch_for_select_test=@@optimizer_switch; +set @join_cache_level_for_select_test=@@join_cache_level; drop table if exists t1,t2,t3,t4,t11; drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 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'); +set join_cache_level=@join_cache_level_for_select_test; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL @@ -5112,6 +5114,7 @@ SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value -join_cache_level 1 +join_cache_level 2 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/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index a3647c10350..29a6bb4bf35 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -3,6 +3,7 @@ drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 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'); +set join_cache_level=1; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 2eaf4062659..58a4e61ce0b 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3,6 +3,7 @@ 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 join_cache_level=1; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); (select 2) diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 7d13bce1f85..8d79eb26557 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1,6 +1,7 @@ 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'; +set join_cache_level=1; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 5ad5878623d..a0153cf209d 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -7,9 +7,11 @@ set join_cache_level=6; show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 +set @join_cache_level_for_subselect3_test=@@join_cache_level; 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'; +set join_cache_level=@join_cache_level_for_subselect3_test; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), @@ -1502,5 +1504,6 @@ set @@optimizer_switch=@subselect3_tmp; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value -join_cache_level 1 +join_cache_level 2 set @@optimizer_switch=@save_optimizer_switch; +set @join_cache_level_for_subselect3_test=NULL; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index cf615caf30b..7e4cf4ed9f1 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -3,6 +3,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; drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i; drop table if exists columns; drop table if exists t1_16, t2_16, t3_16; @@ -1797,6 +1799,7 @@ Note 1003 select 8 AS `a` from `test`.`t1` where <expr_cache><8>(<in_optimizer>( DROP TABLE t1; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; +set join_cache_level=@save_join_cache_level; set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 2acca5e0af9..6d9cdc08572 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -3,11 +3,14 @@ select @@optimizer_switch like '%materialization=on%'; 1 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; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; 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 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); (select 2) @@ -5901,3 +5904,4 @@ set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' 1 +set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index b5e0f16c388..53ac091b722 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -1,9 +1,12 @@ 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; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; 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 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); (select 2) diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index aaa162528fc..e3ad1397c02 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -2,11 +2,14 @@ select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' 1 set optimizer_switch='subquery_cache=off'; +set join_cache_level=1; +set @join_cache_level_for_subselect_test=@@join_cache_level; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; 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 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); (select 2) @@ -5906,3 +5909,4 @@ set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' 1 +set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 7923d4cc68b..d5c6af1640a 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -1,9 +1,12 @@ 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; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; 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 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); (select 2) @@ -5894,3 +5897,4 @@ drop table t1; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null; +set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 6bea2c8c17b..c0a37be68cf 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -6,6 +6,7 @@ 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'); +set join_cache_level=1; set @save_optimizer_switch=@@optimizer_switch; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index f9d70fd8943..5bf470a5245 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -3,6 +3,7 @@ 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'); +set join_cache_level=1; drop table if exists t0, t1, t2, t3, t4, t5; drop view if exists v1; create table t0 (a int); diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index eb5b86a3dc6..1f84da5433d 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -8,11 +8,13 @@ show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 set @optimizer_switch_for_subselect_sj2_test=@@optimizer_switch; +set @join_cache_level_for_subselect_sj2_test=@@join_cache_level; set @subselect_sj2_tmp= @@optimizer_switch; 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'); +set join_cache_level=@join_cache_level_for_subselect_sj2_test; drop table if exists t0, t1, t2, t3, t4, t5; drop view if exists v1; create table t0 (a int); @@ -982,6 +984,7 @@ DROP TABLE t1,t2; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value -join_cache_level 1 +join_cache_level 2 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/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 021eedab5b8..f217cee38ce 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -5,6 +5,7 @@ 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'); +set join_cache_level=1; drop table if exists t0, t1, t2, t3, t4, t5; drop view if exists v1; create table t0 (a int); diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 26f6ef11878..df3ab436f41 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -10,6 +10,7 @@ show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch; +set @join_cache_level_for_subselect_sj_test=@@join_cache_level; drop table if exists t0, t1, t2, t3, t4, t5, t10, t11, t12; drop view if exists v1, v2, v3, v4; drop procedure if exists p1; @@ -18,6 +19,7 @@ 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'); +set join_cache_level=@join_cache_level_for_subselect_sj_test; set @save_optimizer_switch=@@optimizer_switch; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -2243,6 +2245,7 @@ DROP TABLE t1,t2,t3; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value -join_cache_level 1 +join_cache_level 2 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/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index af7c9a1de5b..bdfa834977b 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2,6 +2,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; drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i; drop table if exists columns; drop table if exists t1_16, t2_16, t3_16; @@ -1834,3 +1836,4 @@ Note 1003 select 8 AS `a` from <materialize> (select min(`test`.`t1`.`a`) from DROP TABLE t1; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; +set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/suite/pbxt/r/distinct.result b/mysql-test/suite/pbxt/r/distinct.result index f821023f03a..aa4ba4bef06 100644 --- a/mysql-test/suite/pbxt/r/distinct.result +++ b/mysql-test/suite/pbxt/r/distinct.result @@ -280,6 +280,8 @@ unique (id, idx) 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 @@ -326,6 +328,7 @@ AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); id 2 drop table t1,t2,t3; +set join_cache_level=@save_join_cache_level; create table t1 (a int not null, b int not null, t time); insert into t1 values (1,1,"00:06:15"),(1,2,"00:06:15"),(1,2,"00:30:15"),(1,3,"00:06:15"),(1,3,"00:30:15"); select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b; diff --git a/mysql-test/suite/pbxt/r/greedy_optimizer.result b/mysql-test/suite/pbxt/r/greedy_optimizer.result index 2fb65d4d824..7b46f4275b0 100644 --- a/mysql-test/suite/pbxt/r/greedy_optimizer.result +++ b/mysql-test/suite/pbxt/r/greedy_optimizer.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7; +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) @@ -655,3 +657,4 @@ show status like 'Last_query_cost'; Variable_name Value Last_query_cost 795.625316 drop table t1,t2,t3,t4,t5,t6,t7; +set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/suite/pbxt/t/distinct.test b/mysql-test/suite/pbxt/t/distinct.test index 5d02b38aed8..854d7fc838e 100644 --- a/mysql-test/suite/pbxt/t/distinct.test +++ b/mysql-test/suite/pbxt/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/suite/pbxt/t/greedy_optimizer.test b/mysql-test/suite/pbxt/t/greedy_optimizer.test index fd0be172e83..b3c05f4b3cd 100644 --- a/mysql-test/suite/pbxt/t/greedy_optimizer.test +++ b/mysql-test/suite/pbxt/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) @@ -312,6 +315,8 @@ show status like 'Last_query_cost'; drop table t1,t2,t3,t4,t5,t6,t7; +set join_cache_level=@save_join_cache_level; + --disable_query_log drop database pbxt; --enable_query_log 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; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 606af24e0db..1a60d996fed 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -7294,7 +7294,7 @@ each time the SQL thread starts.", "Controls what join operations can be executed with join buffers. Odd numbers are used for plain join buffers while even numbers are used for linked buffers", &global_system_variables.join_cache_level, &max_system_variables.join_cache_level, - 0, GET_ULONG, REQUIRED_ARG, 1, 0, 8, 0, 1, 0}, + 0, GET_ULONG, REQUIRED_ARG, 2, 0, 8, 0, 1, 0}, {"keep_files_on_create", OPT_KEEP_FILES_ON_CREATE, "Don't overwrite stale .MYD and .MYI even if no directory is specified.", &global_system_variables.keep_files_on_create, |