diff options
Diffstat (limited to 'mysql-test')
47 files changed, 204 insertions, 19 deletions
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index 238d3797fe8..4ad9c2046e0 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -264,7 +264,10 @@ explain select * from t1 where key1=3 or key2=4 union select * from t1 where key1<4 or key3=5; # index merge in subselect +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; +set optimizer_switch=@tmp_optimizer_switch; # 12. check for long index_merges. create table t3 like t0; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index cb84d10e0fa..f34810ada6a 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3; +set @save_derived_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; select * from (select 2 from DUAL) b; 2 2 @@ -205,6 +207,7 @@ x 1 create user mysqltest_1; create table t1 select 1 as a; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; select 2 as a from (select * from t1) b; ERROR 3D000: No database selected use test; @@ -413,3 +416,4 @@ MIN(i) 1 DROP TABLE t1; # End of 5.0 tests +set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 856e33258c1..2493b04488f 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -309,6 +309,8 @@ End of 5.1 tests. CREATE TABLE t1 (a int) ; CREATE TABLE t2 (a int) ; INSERT INTO t2 VALUES (8); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN EXTENDED SELECT * FROM ( SELECT t1.a FROM t1,t2 WHERE t2.a = t1.a ) AS t; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -316,4 +318,5 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select NULL AS `a` from (select NULL AS `a` from `test`.`t1` join `test`.`t2` where 0) `t` +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index a24784ace4b..0983d76f449 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1930,6 +1930,8 @@ FROM t2); MIN(a) NULL +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT MIN(a) FROM (SELECT a FROM empty1) tt @@ -1940,6 +1942,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +set optimizer_switch=@tmp_optimizer_switch; # # 5) Test that subquery materialization is setup for query with diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index f23026096a5..a239a74acb1 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2547,6 +2547,8 @@ End of 5.0 tests drop table if exists t1; create table t1(f1 tinyint default null)engine=myisam; insert into t1 values (-1),(null); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 as a from t1,(select decode(f1,f1) as b from t1) a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 @@ -2557,6 +2559,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 2 +set optimizer_switch=@tmp_optimizer_switch; drop table t1; # # Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0 diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index 773e74f9701..bde2324028f 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -686,6 +686,8 @@ INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1 VALUES (1000000, 0, 0); SET SESSION sort_buffer_size = 1024*36; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX(primary,idx) @@ -711,6 +713,7 @@ WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; COUNT(*) 6145 DROP TABLE t1; +set optimizer_switch=@tmp_optimizer_switch; # # Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly # with Innodb tables diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 1f35fb7a16e..3616171ef58 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -285,10 +285,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where 2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +set optimizer_switch=@tmp_optimizer_switch; create table t3 like t0; insert into t3 select * from t0; alter table t3 add key9 int not null, add index i9(key9); diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 1f56ee4d53b..6694cf48109 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1277,6 +1277,8 @@ COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) COLUMN_DEFAULT=get_value( fld1 7cf7a6782be951a1f2464a350da926a5 65532 1 DROP TABLE bug23037; DROP FUNCTION get_value; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; create view v1 as select table_schema as object_schema, table_name as object_name, @@ -1290,6 +1292,7 @@ explain select * from (select table_name from information_schema.tables) as a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED tables ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +set optimizer_switch=@tmp_optimizer_switch; drop view v1; create table t1 (f1 int(11)); create table t2 (f1 int(11), f2 int(11)); diff --git a/mysql-test/r/optimizer_switch.result b/mysql-test/r/optimizer_switch.result index 842b99ccb11..1606547e137 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off # # 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=off,materialization=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off 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=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off +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=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 90981166fb6..becea752810 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -151,6 +151,8 @@ c32 set('monday', 'tuesday', 'wednesday') create table t2 like t1; set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ; prepare stmt1 from @stmt ; execute stmt1 ; @@ -178,6 +180,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables deallocate prepare stmt1; +set optimizer_switch=@tmp_optimizer_switch; drop tables t1,t2; set @@optimizer_switch=@save_optimizer_switch; set @arg00=1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cf501b3fb28..c2acdf7f018 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -201,6 +201,8 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -209,6 +211,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -5536,10 +5539,13 @@ DROP TABLE parent, child; CREATE TABLE t1 (a INT, b INT, INDEX (a)); INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 ref a a 5 const 1 +set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 8b1d7e46e4e..28436a7cc23 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1119,12 +1119,15 @@ a set @@optimizer_switch=@save_optimizer_switch; set @@optimizer_switch=@save_optimizer_switch; set @@optimizer_switch='materialization=off'; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select * from (select a from t0) X where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join) 2 DERIVED t0 ALL NULL NULL NULL NULL 11 drop table t0, t1; +set optimizer_switch=@tmp_optimizer_switch; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 8bf699fbc4d..59848c565d5 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1128,12 +1128,15 @@ a set @@optimizer_switch=@save_optimizer_switch; set @@optimizer_switch=@save_optimizer_switch; set @@optimizer_switch='materialization=off'; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select * from (select a from t0) X where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join) 2 DERIVED t0 ALL NULL NULL NULL NULL 11 drop table t0, t1; +set optimizer_switch=@tmp_optimizer_switch; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 499963710e1..8eaad6d8034 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1337,6 +1337,8 @@ DROP TABLE t1,t2,t3; CREATE TABLE t1 (a INTEGER); CREATE TABLE t2 (b INTEGER); INSERT INTO t2 VALUES (1); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain SELECT a FROM ( SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) ) table1; @@ -1348,6 +1350,7 @@ SELECT a FROM ( SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) ) table1; a +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2; # End BUG#56367 # diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index dc6421df395..5623aef011e 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -156,6 +156,8 @@ drop table t1, t2, t3; CREATE TABLE t2 ( f2 int(11)) ; CREATE TABLE t1 ( f3 int(11), KEY (f3)) ; INSERT INTO t1 VALUES (6),(4); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t2) AS a2 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); @@ -171,6 +173,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index 2 DERIVED t2 ALL NULL NULL NULL NULL 2 +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; # # LP BUG#715027 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed @@ -179,6 +182,8 @@ CREATE TABLE t1 ( f1 int(11), PRIMARY KEY (f1)) ; INSERT INTO t1 VALUES (28),(29); CREATE TABLE t2 ( f2 int(11), f3 int(11), f10 varchar(1)) ; INSERT INTO t2 VALUES (NULL,6,'f'),(4,2,'d'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 @@ -203,6 +208,7 @@ WHERE t1.f1 AND alias2.f10 ) ORDER BY field1 ; field1 +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; # # LP BUG#718578 Yet another Assertion `!table || diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 8a5443c70de..bf7a9504279 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -206,6 +206,8 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -214,6 +216,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -5535,10 +5538,13 @@ DROP TABLE parent, child; CREATE TABLE t1 (a INT, b INT, INDEX (a)); INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 ref a a 5 const 1 +set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index d62ba3f022d..514efeefe3a 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -202,6 +202,8 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -210,6 +212,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -5531,10 +5534,13 @@ DROP TABLE parent, child; CREATE TABLE t1 (a INT, b INT, INDEX (a)); INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 ref a a 5 const 1 +set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index b1c62cfc7dd..5cf75c54a7f 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -205,6 +205,8 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -213,6 +215,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -5540,10 +5543,13 @@ DROP TABLE parent, child; CREATE TABLE t1 (a INT, b INT, INDEX (a)); INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 ref a a 5 const 1 +set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 7f9cb7f7c3d..eafddafb368 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -202,6 +202,8 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -210,6 +212,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -5531,10 +5534,13 @@ DROP TABLE parent, child; CREATE TABLE t1 (a INT, b INT, INDEX (a)); INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 ref a a 5 const 1 +set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/subselect_partial_match.result b/mysql-test/r/subselect_partial_match.result index d23f9885f44..a01d6375064 100644 --- a/mysql-test/r/subselect_partial_match.result +++ b/mysql-test/r/subselect_partial_match.result @@ -766,6 +766,8 @@ CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL); INSERT INTO t2 VALUES (6,NULL); INSERT INTO t2 VALUES (NULL,0); set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -774,6 +776,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `table1`.`a1` AS `a1`,`table1`.`a2` AS `a2` from (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery3>`.`b2`)))))))) `table1` +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2; # # LP BUG#613009 Crash in Ordered_key::get_field_idx diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index d5ab105207f..a9b4b4cb0cf 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -1862,6 +1862,8 @@ INSERT INTO t2 VALUES (1,2,4,'22:34:09','v','v'), (16,8,1,'02:57:29',NULL,NULL),(17,3,1,'16:46:13','r','r'), (18,3,9,'19:39:02','v','v'),(19,9,1,NULL,NULL,NULL), (20,6,5,'20:58:33','r','r'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain SELECT alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, @@ -1897,6 +1899,7 @@ WHERE alias1.c IN (SELECT SQ3_alias1.b FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2, t3; set optimizer_switch=@tmp_830993; set join_buffer_size= @tmp_830993_jbs; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 871f58199e7..68a130b04bf 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -1873,6 +1873,8 @@ INSERT INTO t2 VALUES (1,2,4,'22:34:09','v','v'), (16,8,1,'02:57:29',NULL,NULL),(17,3,1,'16:46:13','r','r'), (18,3,9,'19:39:02','v','v'),(19,9,1,NULL,NULL,NULL), (20,6,5,'20:58:33','r','r'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain SELECT alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, @@ -1908,6 +1910,7 @@ WHERE alias1.c IN (SELECT SQ3_alias1.b FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2, t3; set optimizer_switch=@tmp_830993; set join_buffer_size= @tmp_830993_jbs; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index ec2ee967b22..472cadf04c4 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1372,6 +1372,8 @@ DROP TABLE t1,t2,t3; CREATE TABLE t1 (a INTEGER); CREATE TABLE t2 (b INTEGER); INSERT INTO t2 VALUES (1); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain SELECT a FROM ( SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) ) table1; @@ -1383,6 +1385,7 @@ SELECT a FROM ( SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) ) table1; a +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2; # End BUG#56367 # diff --git a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result index e8ba522af2e..30e58aacff5 100644 --- a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result @@ -97,6 +97,8 @@ insert into t1 values (1,1), (2,null), (3,1), (4,1), # Demonstrate that for the SELECT statement # used later in the test JT_EQ_REF access method is used. # +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 2 as a, 1 as b union all select 2 as a, 2 as b) as t2 for update; @@ -150,6 +152,7 @@ key_len NULL ref NULL rows NULL Extra +set optimizer_switch=@tmp_optimizer_switch; # # Demonstrate that the reported SELECT statement # no longer produces warnings. @@ -301,6 +304,8 @@ begin; # is retreived and processed first. # # Verify that JT_EQ_REF is used. +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 3 as a, 2 as b union all select 3 as a, 1 as b) as t2 for update; @@ -354,6 +359,7 @@ key_len NULL ref NULL rows NULL Extra +set optimizer_switch=@tmp_optimizer_switch; # Lock the record. select 1 from t1 natural join (select 3 as a, 2 as b union all select 3 as a, 1 as b) as t2 for update; diff --git a/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1.test b/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1.test index fcbf2b1cfc7..2707b97bfa2 100644 --- a/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1.test +++ b/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1.test @@ -140,9 +140,12 @@ insert into t1 values (1,1), (2,null), (3,1), (4,1), --echo # used later in the test JT_EQ_REF access method is used. --echo # --vertical_results +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 2 as a, 1 as b union all select 2 as a, 2 as b) as t2 for update; +set optimizer_switch=@tmp_optimizer_switch; --horizontal_results --echo # --echo # Demonstrate that the reported SELECT statement @@ -236,9 +239,12 @@ begin; --echo # --echo # Verify that JT_EQ_REF is used. --vertical_results +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 3 as a, 2 as b union all select 3 as a, 1 as b) as t2 for update; +set optimizer_switch=@tmp_optimizer_switch; --horizontal_results --echo # Lock the record. select 1 from t1 natural join (select 3 as a, 2 as b union all diff --git a/mysql-test/suite/innodb_plugin/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb_plugin/r/innodb_lock_wait_timeout_1.result index c1e54465066..5d8c24ed11f 100644 --- a/mysql-test/suite/innodb_plugin/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb_plugin/r/innodb_lock_wait_timeout_1.result @@ -99,6 +99,7 @@ insert into t1 values (1,1), (2,null), (3,1), (4,1), # set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='index_condition_pushdown=off'; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 2 as a, 1 as b union all select 2 as a, 2 as b) as t2 for update; @@ -306,6 +307,7 @@ begin; # Verify that JT_EQ_REF is used. set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='index_condition_pushdown=off'; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 3 as a, 2 as b union all select 3 as a, 1 as b) as t2 for update; diff --git a/mysql-test/suite/innodb_plugin/t/innodb_lock_wait_timeout_1.test b/mysql-test/suite/innodb_plugin/t/innodb_lock_wait_timeout_1.test index c458b0ba44d..f8aef2af86d 100644 --- a/mysql-test/suite/innodb_plugin/t/innodb_lock_wait_timeout_1.test +++ b/mysql-test/suite/innodb_plugin/t/innodb_lock_wait_timeout_1.test @@ -142,6 +142,7 @@ insert into t1 values (1,1), (2,null), (3,1), (4,1), --vertical_results set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='index_condition_pushdown=off'; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 2 as a, 1 as b union all select 2 as a, 2 as b) as t2 for update; @@ -241,6 +242,7 @@ begin; --vertical_results set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='index_condition_pushdown=off'; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 3 as a, 2 as b union all select 3 as a, 1 as b) as t2 for update; diff --git a/mysql-test/suite/pbxt/r/derived.result b/mysql-test/suite/pbxt/r/derived.result index 4fc9216f126..1e4126315fb 100644 --- a/mysql-test/suite/pbxt/r/derived.result +++ b/mysql-test/suite/pbxt/r/derived.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3; +set @save_derived_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; select * from (select 2 from DUAL) b; 2 2 @@ -208,6 +210,7 @@ create table t1 select 1 as a; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK +set optimizer_switch='derived_merge=off,derived_with_keys=off'; select 2 as a from (select * from t1) b; ERROR 3D000: No database selected use test; @@ -389,3 +392,4 @@ select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID; ID DATA FID drop table t1, t2; drop user mysqltest_1; +set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index 6b1ed9ea052..0b5bfc579cf 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -196,6 +196,8 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -204,6 +206,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 diff --git a/mysql-test/suite/pbxt/t/derived.test b/mysql-test/suite/pbxt/t/derived.test index a9127b49741..49effe4f14b 100644 --- a/mysql-test/suite/pbxt/t/derived.test +++ b/mysql-test/suite/pbxt/t/derived.test @@ -3,6 +3,9 @@ drop table if exists t1,t2,t3; --enable_warnings +set @save_derived_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + select * from (select 2 from DUAL) b; -- error 1054 SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b; @@ -107,6 +110,7 @@ create table t1 select 1 as a; analyze table t1; # PBXT: Required to get a consitent result connect (con1,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); connection con1; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; --error 1046 select 2 as a from (select * from t1) b; use test; @@ -283,3 +287,5 @@ drop user mysqltest_1; drop database pbxt; --enable_query_log # End of 4.1 tests + +set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/suite/pbxt/t/subselect.test b/mysql-test/suite/pbxt/t/subselect.test index 5e9c6eee6a6..fa838363403 100644 --- a/mysql-test/suite/pbxt/t/subselect.test +++ b/mysql-test/suite/pbxt/t/subselect.test @@ -80,8 +80,11 @@ explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); diff --git a/mysql-test/suite/vcol/inc/vcol_select.inc b/mysql-test/suite/vcol/inc/vcol_select.inc index 28c3416a55a..0641e14564a 100644 --- a/mysql-test/suite/vcol/inc/vcol_select.inc +++ b/mysql-test/suite/vcol/inc/vcol_select.inc @@ -84,10 +84,16 @@ eval $s; eval explain $s; --echo # select_type=DERIVED, type=system + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + let $s = select * from (select a,b,c from t1) as t11; eval $s; eval explain $s; +set optimizer_switch=@tmp_optimizer_switch; + --echo ### --echo ### Using aggregate functions with/without DISTINCT --echo ### diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index a0ccc166199..a41c13a2a4c 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -89,6 +89,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 UNION t2 ALL NULL NULL NULL NULL 1 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL # select_type=DERIVED, type=system +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; select * from (select a,b,c from t1) as t11; a b c 2 -2 -2 @@ -100,6 +102,7 @@ explain select * from (select a,b,c from t1) as t11; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 2 DERIVED t1 ALL NULL NULL NULL NULL 5 +set optimizer_switch=@tmp_optimizer_switch; ### ### Using aggregate functions with/without DISTINCT ### diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index 7e716b8b897..3ad15009c76 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -89,6 +89,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL # select_type=DERIVED, type=system +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; select * from (select a,b,c from t1) as t11; a b c 2 -2 -2 @@ -100,6 +102,7 @@ explain select * from (select a,b,c from t1) as t11; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 2 DERIVED t1 ALL NULL NULL NULL NULL 5 +set optimizer_switch=@tmp_optimizer_switch; ### ### Using aggregate functions with/without DISTINCT ### diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 962cec95add..7defc55b473 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -3,6 +3,9 @@ drop table if exists t1,t2,t3; --enable_warnings +set @save_derived_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + select * from (select 2 from DUAL) b; -- error 1054 SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b; @@ -106,6 +109,7 @@ create user mysqltest_1; create table t1 select 1 as a; connect (con1,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); connection con1; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; --error 1046 select 2 as a from (select * from t1) b; use test; @@ -315,3 +319,5 @@ WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3; DROP TABLE t1; --echo # End of 5.0 tests + +set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 186a00af16a..94548fd5fde 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -279,8 +279,11 @@ CREATE TABLE t1 (a int) ; CREATE TABLE t2 (a int) ; INSERT INTO t2 VALUES (8); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN EXTENDED SELECT * FROM ( SELECT t1.a FROM t1,t2 WHERE t2.a = t1.a ) AS t; +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 874b26f7549..65b5b3936cc 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1250,12 +1250,15 @@ SELECT v FROM t2); --echo +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT MIN(a) FROM (SELECT a FROM empty1) tt HAVING ('m') IN ( SELECT v FROM t2); +set optimizer_switch=@tmp_optimizer_switch; --echo --echo # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 92c4bae5327..4302bf5e7eb 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1314,9 +1314,13 @@ DROP TABLE t1, t2; drop table if exists t1; --enable_warnings create table t1(f1 tinyint default null)engine=myisam; -insert into t1 values (-1),(null); +insert into t1 values (-1),(null); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 as a from t1,(select decode(f1,f1) as b from t1) a; explain select 1 as a from t1,(select encode(f1,f1) as b from t1) a; +set optimizer_switch=@tmp_optimizer_switch; drop table t1; --echo # diff --git a/mysql-test/t/index_merge_innodb.test b/mysql-test/t/index_merge_innodb.test index fa05c96a6f9..ae568c5b5d9 100644 --- a/mysql-test/t/index_merge_innodb.test +++ b/mysql-test/t/index_merge_innodb.test @@ -68,6 +68,8 @@ INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1 VALUES (1000000, 0, 0); SET SESSION sort_buffer_size = 1024*36; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; # We have to use FORCE INDEX here as Innodb gives inconsistent estimates # which causes different query plans. @@ -89,6 +91,7 @@ SELECT COUNT(*) FROM WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; DROP TABLE t1; +set optimizer_switch=@tmp_optimizer_switch; --echo # --echo # Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index e78b180caf7..e1962255cf6 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -962,6 +962,8 @@ DROP FUNCTION get_value; # # Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash # +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; create view v1 as select table_schema as object_schema, table_name as object_name, @@ -970,6 +972,7 @@ from information_schema.tables order by object_schema; explain select * from v1; explain select * from (select table_name from information_schema.tables) as a; +set optimizer_switch=@tmp_optimizer_switch; drop view v1; # diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index f4e27f3ba42..357e7d4fe8f 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -166,12 +166,18 @@ create table t2 like t1; set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ; prepare stmt1 from @stmt ; execute stmt1 ; execute stmt1 ; explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; deallocate prepare stmt1; + +set optimizer_switch=@tmp_optimizer_switch; + drop tables t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index a39b7ea49b8..abce58348da 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -94,8 +94,11 @@ explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); @@ -4648,7 +4651,10 @@ CREATE TABLE t1 (a INT, b INT, INDEX (a)); INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); --echo +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; +set optimizer_switch=@tmp_optimizer_switch; --echo EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 7e4943d85ba..aadc08e18e0 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -925,8 +925,11 @@ set @@optimizer_switch='materialization=off'; # # FirstMatch referring to a derived table # +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select * from (select a from t0) X where a in (select a from t1); drop table t0, t1; +set optimizer_switch=@tmp_optimizer_switch; # # LooseScan: Check if we can pick it together with range access diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 4ce19012e0c..463685bbdf0 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -183,6 +183,9 @@ CREATE TABLE t2 ( f2 int(11)) ; CREATE TABLE t1 ( f3 int(11), KEY (f3)) ; INSERT INTO t1 VALUES (6),(4); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + EXPLAIN SELECT * FROM (SELECT * FROM t2) AS a2 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); @@ -192,6 +195,7 @@ EXPLAIN SELECT * FROM (SELECT * FROM t2) AS a2 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; --echo # @@ -204,6 +208,9 @@ INSERT INTO t1 VALUES (28),(29); CREATE TABLE t2 ( f2 int(11), f3 int(11), f10 varchar(1)) ; INSERT INTO t2 VALUES (NULL,6,'f'),(4,2,'d'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + EXPLAIN SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 @@ -223,6 +230,7 @@ WHERE ( ) ORDER BY field1 ; +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; --echo # diff --git a/mysql-test/t/subselect_partial_match.test b/mysql-test/t/subselect_partial_match.test index 45386efd266..fd1e6de716c 100644 --- a/mysql-test/t/subselect_partial_match.test +++ b/mysql-test/t/subselect_partial_match.test @@ -622,8 +622,11 @@ INSERT INTO t2 VALUES (NULL,0); set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1; +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 5ae968742aa..a2f46c76fce 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1690,6 +1690,9 @@ INSERT INTO t2 VALUES (1,2,4,'22:34:09','v','v'), (18,3,9,'19:39:02','v','v'),(19,9,1,NULL,NULL,NULL), (20,6,5,'20:58:33','r','r'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + explain SELECT alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, @@ -1720,6 +1723,8 @@ WHERE FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; +set optimizer_switch=@tmp_optimizer_switch; + drop table t1,t2, t3; set optimizer_switch=@tmp_830993; set join_buffer_size= @tmp_830993_jbs; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index a72128bf5ed..4ddc19f49f5 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1022,12 +1022,15 @@ CREATE TABLE t1 (a INTEGER); CREATE TABLE t2 (b INTEGER); INSERT INTO t2 VALUES (1); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; let $query = SELECT a FROM ( SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) ) table1; eval explain $query; eval $query; +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2; |