diff options
Diffstat (limited to 'mysql-test/r')
22 files changed, 153 insertions, 93 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index d18af9fd4e1..e123989567d 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -199,16 +199,16 @@ insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1'); flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); dt flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); dt diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 33376b22bc6..6bae35e27a2 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1542,8 +1542,8 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1) +1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; @@ -1555,8 +1555,8 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 4 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) +1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where SHOW VARIABLES LIKE 'old'; Variable_name Value old OFF diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 0b3df689089..c33e632ffbb 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -1432,7 +1432,7 @@ group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index -2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2) +3 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.b) and @@ -1489,7 +1489,7 @@ group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index -2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2) +3 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.c) and diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 58a40bae4da..7b890e4d4aa 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -1,6 +1,7 @@ DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; DROP DATABASE IF EXISTS world; 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'; set names utf8; diff --git a/mysql-test/r/optimizer_switch.result b/mysql-test/r/optimizer_switch.result index 937b4dfeffa..ff077ed9fc9 100644 --- a/mysql-test/r/optimizer_switch.result +++ b/mysql-test/r/optimizer_switch.result @@ -4,19 +4,19 @@ # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch=4; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4' set optimizer_switch=NULL; @@ -43,60 +43,60 @@ set optimizer_switch=default; set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on # # Check index_merge's @@optimizer_switch flags # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on BUG#37120 optimizer_switch allowable values not according to specification select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on set optimizer_switch=default; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 63f2775b094..56c2c915cb0 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1,7 +1,8 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; -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); (select 2) 2 @@ -4803,7 +4804,6 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; -set @@optimizer_switch=@save_optimizer_switch; # # Bug #49512 : subquery with aggregate function crash # subselect_single_select_engine::exec() @@ -5127,3 +5127,4 @@ ON t2.f10 = t3.f10 f1 DROP TABLE t1,t2,t3; End of 5.3 tests +set optimizer_switch=@subselect_tmp; diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 16273711a49..4fd86f10530 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -1,4 +1,6 @@ drop table if exists t1, t2, t3, t4; +set @subselect2_test_tmp=@@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on'; CREATE TABLE t1 ( DOCID VARCHAR(32)BINARY NOT NULL @@ -144,3 +146,4 @@ and t2.a='1' AND t1.a=t3.b) > 0; a 2 DROP TABLE t1,t2,t3; +set optimizer_switch=@subselect2_test_tmp; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index e88461173df..3d5091bf36b 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1,5 +1,6 @@ drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; -set @save_optimizer_switch=@@optimizer_switch; +set @subselect3_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), @@ -698,7 +699,6 @@ a MAX(b) test 2 3 h 3 4 i DROP TABLE t1, t2; -set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int, PRIMARY KEY(b)); @@ -1084,7 +1084,8 @@ a 17 18 19 -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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch @@ -1399,7 +1400,7 @@ drop table t1,t2,t3; # # BUG#47367 Crash in Name_resolution_context::process_error # -SET SESSION optimizer_switch = 'default,semijoin=off'; +SET SESSION optimizer_switch = 'semijoin=off'; CREATE TABLE t1 (f1 INTEGER); CREATE TABLE t2 LIKE t1; CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| @@ -1421,4 +1422,4 @@ CALL p1; ERROR 42S22: Unknown column 'f1' in 'where clause' DROP PROCEDURE p1; DROP TABLE t1, t2; -set @@optimizer_switch=@save_optimizer_switch; +set @@optimizer_switch=@subselect3_tmp; diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index e254fad2300..2225242c663 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -6,7 +6,8 @@ show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; -set @save_optimizer_switch=@@optimizer_switch; +set @subselect3_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), @@ -705,7 +706,6 @@ a MAX(b) test 2 3 h 3 4 i DROP TABLE t1, t2; -set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int, PRIMARY KEY(b)); @@ -1091,7 +1091,8 @@ a 17 18 19 -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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch @@ -1406,7 +1407,7 @@ drop table t1,t2,t3; # # BUG#47367 Crash in Name_resolution_context::process_error # -SET SESSION optimizer_switch = 'default,semijoin=off'; +SET SESSION optimizer_switch = 'semijoin=off'; CREATE TABLE t1 (f1 INTEGER); CREATE TABLE t2 LIKE t1; CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| @@ -1428,7 +1429,7 @@ CALL p1; ERROR 42S22: Unknown column 'f1' in 'where clause' DROP PROCEDURE p1; DROP TABLE t1, t2; -set @@optimizer_switch=@save_optimizer_switch; +set @@optimizer_switch=@subselect3_tmp; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 4cdc75a29f8..de4f386f831 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4,t5,t6; +set @subselect4_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; # # Bug #46791: Assertion failed:(table->key_read==0),function unknown # function,file sql_base.cc @@ -1736,3 +1738,4 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary drop table t1, t2, t3; +set optimizer_switch=@subselect4_tmp; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 889293d2908..409c14c3d57 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1,4 +1,7 @@ -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'; +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; drop table if exists t1, t2, t3, t1i, t2i, t3i; drop table if exists columns; drop table if exists t1_16, t2_16, t3_16; @@ -180,28 +183,34 @@ a1 a2 select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 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; a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 -set @@optimizer_switch='default,semijoin=off'; +set @@optimizer_switch=@optimizer_switch_local_default; +set @@optimizer_switch='semijoin=off'; execute st1; a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 -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; a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 -set @@optimizer_switch='default,materialization=off'; +set @@optimizer_switch=@optimizer_switch_local_default; +set @@optimizer_switch='materialization=off,in_to_exists=on'; execute st1; a1 a2 1 - 01 2 - 01 @@ -1145,28 +1154,31 @@ id select_type table type possible_keys key key_len ref rows Extra select min(a1) from t1 where 7 in (select b1 from t2 group by b1); min(a1) 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found select min(a1) from t1 where 7 in (select b1 from t2 group by b1); min(a1) -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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found select min(a1) from t1 where 7 in (select b1 from t2); min(a1) -set @@optimizer_switch='default,materialization=off'; +set @@optimizer_switch=@optimizer_switch_local_default; +set @@optimizer_switch='materialization=off,in_to_exists=on'; # with MariaDB and MWL#90, this particular case is solved: explain select min(a1) from t1 where 7 in (select b1 from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables select min(a1) from t1 where 7 in (select b1 from t2); min(a1) -NULL # but when we go around MWL#90 code, the problem still shows up: explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; id select_type table type possible_keys key key_len ref rows Extra @@ -1199,7 +1211,8 @@ CREATE TABLE t2 LIKE t1; 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 @@ -1222,7 +1235,8 @@ INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 @@ -1320,6 +1334,8 @@ select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select substring(b1, left(a1,7) left(a2,7) 1 - 01x 2 - 01x drop table t1_1024, t2_1024; +set optimizer_switch=@subselect_sj_mat_tmp; +set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; create table t0 (a int); insert into t0 values (0),(1),(2); diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index eee9db65ae9..dabffd16d93 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -4,8 +4,9 @@ select @@optimizer_switch like '%materialization=on%'; set optimizer_switch='materialization=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; -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); (select 2) 2 @@ -4807,7 +4808,6 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; -set @@optimizer_switch=@save_optimizer_switch; # # Bug #49512 : subquery with aggregate function crash # subselect_single_select_engine::exec() @@ -5131,6 +5131,7 @@ ON t2.f10 = t3.f10 f1 DROP TABLE t1,t2,t3; End of 5.3 tests +set optimizer_switch=@subselect_tmp; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 493ab9dba59..2e643027ce6 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -1,8 +1,9 @@ -set optimizer_switch='materialization=off,semijoin=off'; +set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; -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); (select 2) 2 @@ -4804,7 +4805,6 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; -set @@optimizer_switch=@save_optimizer_switch; # # Bug #49512 : subquery with aggregate function crash # subselect_single_select_engine::exec() @@ -5128,4 +5128,5 @@ ON t2.f10 = t3.f10 f1 DROP TABLE t1,t2,t3; End of 5.3 tests -set optimizer_switch=default; +set optimizer_switch=@subselect_tmp; +set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 434bcd33a21..ebe85ca4670 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -1,8 +1,9 @@ -set optimizer_switch='semijoin=off'; +set @optimizer_switch_for_subselect_test='semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; -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); (select 2) 2 @@ -4804,7 +4805,6 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; -set @@optimizer_switch=@save_optimizer_switch; # # Bug #49512 : subquery with aggregate function crash # subselect_single_select_engine::exec() @@ -5128,4 +5128,5 @@ ON t2.f10 = t3.f10 f1 DROP TABLE t1,t2,t3; End of 5.3 tests -set optimizer_switch=default; +set optimizer_switch=@subselect_tmp; +set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 316f8c7e815..23e216a282a 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -1,6 +1,8 @@ drop table if exists t0, t1, t2, t3, t4, t10, t11, t12; drop view if exists v1, v2; drop procedure if exists p1; +set @subselect_sj_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; 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); @@ -1536,4 +1538,4 @@ AND t1.f1 = t2.f1 ; f1 f1 DROP TABLE t1, t2; set optimizer_switch=@save_802965; -set @@optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 45dd12a6b77..7a770abb474 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1,3 +1,5 @@ +set @subselect_sj2_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; drop table if exists t0, t1, t2, t3; drop view if exists v1; create table t0 (a int); @@ -744,3 +746,4 @@ LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10 GROUP BY field2; field2 drop table t1, t2, t3; +set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 45cce630a22..5292e70e100 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -5,6 +5,8 @@ set join_cache_level=6; show variables like 'join_cache_level'; Variable_name Value join_cache_level 6 +set @subselect_sj2_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; drop table if exists t0, t1, t2, t3; drop view if exists v1; create table t0 (a int); @@ -753,6 +755,7 @@ LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10 GROUP BY field2; field2 drop table t1, t2, t3; +set optimizer_switch=@subselect_sj2_tmp; set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 0919e8701e2..83507909cf1 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1,4 +1,6 @@ set optimizer_switch='materialization=on'; +set @subselect_sj2_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; drop table if exists t0, t1, t2, t3; drop view if exists v1; create table t0 (a int); @@ -755,6 +757,7 @@ LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10 GROUP BY field2; field2 drop table t1, t2, t3; +set optimizer_switch=@subselect_sj2_tmp; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 282142496d1..684d3d805da 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -1,4 +1,5 @@ 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'; @@ -9,6 +10,8 @@ join_cache_level 6 drop table if exists t0, t1, t2, t3, t4, t10, t11, t12; drop view if exists v1, v2; drop procedure if exists p1; +set @subselect_sj_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; 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); @@ -1544,7 +1547,7 @@ AND t1.f1 = t2.f1 ; f1 f1 DROP TABLE t1, t2; set optimizer_switch=@save_802965; -set @@optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off # diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index a3b19b38867..31afe6fed1c 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1,3 +1,6 @@ +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; drop table if exists t1, t2, t3, t1i, t2i, t3i; drop table if exists columns; drop table if exists t1_16, t2_16, t3_16; @@ -190,28 +193,34 @@ a1 a2 select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 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; a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 -set @@optimizer_switch='default,semijoin=off'; +set @@optimizer_switch=@optimizer_switch_local_default; +set @@optimizer_switch='semijoin=off'; execute st1; a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 -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; a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 -set @@optimizer_switch='default,materialization=off'; +set @@optimizer_switch=@optimizer_switch_local_default; +set @@optimizer_switch='materialization=off,in_to_exists=on'; execute st1; a1 a2 1 - 01 2 - 01 @@ -1182,21 +1191,24 @@ select min(a1) from t1 where 7 in (select b1 from t2 group by b1); min(a1) NULL 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found select min(a1) from t1 where 7 in (select b1 from t2 group by b1); min(a1) -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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables select min(a1) from t1 where 7 in (select b1 from t2); min(a1) -set @@optimizer_switch='default,materialization=off'; +set @@optimizer_switch=@optimizer_switch_local_default; +set @@optimizer_switch='materialization=off,in_to_exists=on'; # with MariaDB and MWL#90, this particular case is solved: explain select min(a1) from t1 where 7 in (select b1 from t2); id select_type table type possible_keys key key_len ref rows Extra @@ -1237,7 +1249,8 @@ CREATE TABLE t2 LIKE t1; 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 @@ -1260,7 +1273,8 @@ INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 @@ -1358,3 +1372,4 @@ select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select substring(b1, left(a1,7) left(a2,7) 1 - 01x 2 - 01x drop table t1_1024, t2_1024; +set optimizer_switch=@subselect_sj_mat_tmp; diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result index e0d1140da74..c1d733ad622 100644 --- a/mysql-test/r/subselect_sj_nonmerged.result +++ b/mysql-test/r/subselect_sj_nonmerged.result @@ -1,6 +1,6 @@ drop table if exists t0, t1, t2, t3, t4; 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); create table t1 as select * from t0; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 092be494302..4dd2fc320ed 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -514,10 +514,11 @@ explain extended select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where (('2007-04-25 18:30:22' = 0)) +Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <expr_cache><1,'2007-04-25 18:30:22'>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t1` `x1` where 0))) select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id cur_date @@ -525,10 +526,11 @@ explain extended select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where (('2007-04-25' = 0)) +Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <expr_cache><1,'2007-04-25'>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t2` `x1` where 0))) select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date @@ -538,11 +540,11 @@ explain extended select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0)) +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <expr_cache><`test`.`t1`.`id`,`test`.`t1`.`cur_date`>(<in_optimizer>(`test`.`t1`.`id`,<exists>(select `test`.`x1`.`id` from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`))))) select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id cur_date @@ -550,11 +552,11 @@ explain extended select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0)) +Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <expr_cache><`test`.`t2`.`id`,`test`.`t2`.`cur_date`>(<in_optimizer>(`test`.`t2`.`id`,<exists>(select `test`.`x1`.`id` from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`))))) select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date |