diff options
Diffstat (limited to 'mysql-test/suite/pbxt')
31 files changed, 223 insertions, 109 deletions
diff --git a/mysql-test/suite/pbxt/r/derived.result b/mysql-test/suite/pbxt/r/derived.result index f2247504936..5cfa8c77cda 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 @@ -190,13 +192,13 @@ pla_id test explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where; Using join buffer (flat, BNL join) 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where; Using join buffer (flat, BNL join) 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 drop table t1,t2; @@ -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; @@ -387,3 +390,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/distinct.result b/mysql-test/suite/pbxt/r/distinct.result index f821023f03a..aa4ba4bef06 100644 --- a/mysql-test/suite/pbxt/r/distinct.result +++ b/mysql-test/suite/pbxt/r/distinct.result @@ -280,6 +280,8 @@ unique (id, idx) insert into t1 values (1,'yes'), (2,'no'); insert into t2 values (1,1); insert into t3 values (1,1); +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; EXPLAIN SELECT DISTINCT t1.id @@ -326,6 +328,7 @@ AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); id 2 drop table t1,t2,t3; +set join_cache_level=@save_join_cache_level; create table t1 (a int not null, b int not null, t time); insert into t1 values (1,1,"00:06:15"),(1,2,"00:06:15"),(1,2,"00:30:15"),(1,3,"00:06:15"),(1,3,"00:30:15"); select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b; diff --git a/mysql-test/suite/pbxt/r/endspace.result b/mysql-test/suite/pbxt/r/endspace.result index c9a2db8bbfe..641ed14023d 100644 --- a/mysql-test/suite/pbxt/r/endspace.result +++ b/mysql-test/suite/pbxt/r/endspace.result @@ -25,10 +25,11 @@ insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); check table t1; Table Op Msg_type Msg_text test.t1 check status OK -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or +text1 like 'teststring_%' ORDER BY text1; text1 -teststring teststring +teststring select * from t1 where text1='teststring' or text1 like 'teststring_%'; text1 teststring @@ -48,10 +49,11 @@ alter table t1 modify text1 char(32) binary not null; check table t1; Table Op Msg_type Msg_text test.t1 check status OK -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or +text1 like 'teststring_%' ORDER BY text1; text1 -teststring teststring +teststring select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%'; concat('|', text1, '|') |teststring | @@ -132,10 +134,11 @@ concat('|', text1, '|') drop table t1; create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap; insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or +text1 like 'teststring_%' ORDER BY text1; text1 -teststring teststring +teststring select * from t1 where text1='teststring' or text1 like 'teststring_%'; text1 teststring diff --git a/mysql-test/suite/pbxt/r/func_group.result b/mysql-test/suite/pbxt/r/func_group.result index 1204b3c3981..4aca6dac1ed 100644 --- a/mysql-test/suite/pbxt/r/func_group.result +++ b/mysql-test/suite/pbxt/r/func_group.result @@ -215,6 +215,8 @@ a1 a2 a1 a2 10 bbb BBB 20 20 zzz AAA 10 20 zzz BBB 20 +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9; max(t1.a1) max(t2.a1) NULL NULL @@ -245,6 +247,7 @@ NULL select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; max(t1.a2) max(t2.a1) zzz BBB +SET optimizer_switch=@save_optimizer_switch; drop table t1,t2; CREATE TABLE t1 (a int, b int); select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; diff --git a/mysql-test/suite/pbxt/r/func_op.result b/mysql-test/suite/pbxt/r/func_op.result index 636163e6b29..ce755e86b4c 100644 --- a/mysql-test/suite/pbxt/r/func_op.result +++ b/mysql-test/suite/pbxt/r/func_op.result @@ -40,9 +40,12 @@ create table t1(a int); create table t2(a int, b int); insert into t1 values (1), (2), (3); insert into t2 values (1, 7), (3, 7); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select t1.a, t2.a, t2.b, bit_count(t2.b) from t1 left join t2 on t1.a=t2.a; a a b bit_count(t2.b) 1 1 7 3 2 NULL NULL NULL 3 3 7 3 +SET optimizer_switch=@save_optimizer_switch; drop table t1, t2; diff --git a/mysql-test/suite/pbxt/r/greedy_optimizer.result b/mysql-test/suite/pbxt/r/greedy_optimizer.result index aeed76ab167..0dab29e2e59 100644 --- a/mysql-test/suite/pbxt/r/greedy_optimizer.result +++ b/mysql-test/suite/pbxt/r/greedy_optimizer.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7; +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; create table t1 ( c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer, primary key (c11) @@ -202,28 +204,28 @@ select @@optimizer_search_depth; 0 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1372.225316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1372.225316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -231,11 +233,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -243,11 +245,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -255,11 +257,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -267,11 +269,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 set optimizer_search_depth=1; select @@optimizer_search_depth; @@optimizer_search_depth @@ -287,7 +289,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -299,7 +301,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -311,7 +313,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -323,7 +325,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -335,7 +337,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -347,35 +349,35 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 set optimizer_search_depth=62; select @@optimizer_search_depth; @@optimizer_search_depth 62 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1372.225316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1372.225316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -383,11 +385,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -395,11 +397,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -407,11 +409,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -419,11 +421,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 set optimizer_prune_level=1; select @@optimizer_prune_level; @@optimizer_prune_level @@ -443,7 +445,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -455,55 +457,55 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 set optimizer_search_depth=1; select @@optimizer_search_depth; @@optimizer_search_depth @@ -519,7 +521,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -531,7 +533,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -543,7 +545,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -555,7 +557,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -567,7 +569,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -579,7 +581,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 set optimizer_search_depth=62; select @@optimizer_search_depth; @@optimizer_search_depth @@ -595,7 +597,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -607,53 +609,54 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 drop table t1,t2,t3,t4,t5,t6,t7; +set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/suite/pbxt/r/group_by.result b/mysql-test/suite/pbxt/r/group_by.result index 683a862b21e..034a95ecc71 100644 --- a/mysql-test/suite/pbxt/r/group_by.result +++ b/mysql-test/suite/pbxt/r/group_by.result @@ -451,6 +451,8 @@ drop table t1,t2; CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID )); insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2; xID xID1 1 1 @@ -469,6 +471,7 @@ xID xID1 Level 2 2 ** 3 134 *** 4 185 **** +SET optimizer_switch=@save_optimizer_switch; drop table t1; CREATE TABLE t1 ( pid int(11) unsigned NOT NULL default '0', diff --git a/mysql-test/suite/pbxt/r/group_min_max.result b/mysql-test/suite/pbxt/r/group_min_max.result index 6985abeeb43..820dd177ded 100644 --- a/mysql-test/suite/pbxt/r/group_min_max.result +++ b/mysql-test/suite/pbxt/r/group_min_max.result @@ -2258,7 +2258,7 @@ a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 Using where 1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 1 Using index -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 MATERIALIZED t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/suite/pbxt/r/heap.result b/mysql-test/suite/pbxt/r/heap.result.THIS index 0404e8b98f9..0404e8b98f9 100644 --- a/mysql-test/suite/pbxt/r/heap.result +++ b/mysql-test/suite/pbxt/r/heap.result.THIS diff --git a/mysql-test/suite/pbxt/r/join.result b/mysql-test/suite/pbxt/r/join.result index 1a2b6e4fc80..bd47696bfdd 100644 --- a/mysql-test/suite/pbxt/r/join.result +++ b/mysql-test/suite/pbxt/r/join.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; CREATE TABLE t1 (S1 INT); CREATE TABLE t2 (S1 INT); INSERT INTO t1 VALUES (1); @@ -799,7 +801,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.b 1 show status like '%cost%'; Variable_name Value -Last_query_cost 24.262158 +Last_query_cost 28.262158 select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z; Z ^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error @@ -819,4 +821,5 @@ Field Type Null Key Default Extra Name varchar(50) YES NULL DROP VIEW v1; DROP TABLE t1,t2,tv1,tv2; +SET optimizer_switch=@save_optimizer_switch; End of 5.0 tests. diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result index 1a195cb13a8..199e56ee150 100644 --- a/mysql-test/suite/pbxt/r/join_nested.result +++ b/mysql-test/suite/pbxt/r/join_nested.result @@ -1,4 +1,6 @@ DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; CREATE TABLE t0 (a int, b int, c int); CREATE TABLE t1 (a int, b int, c int); CREATE TABLE t2 (a int, b int, c int); @@ -1614,3 +1616,4 @@ WHERE t1.id='5'; id ct pc nm 5 NULL NULL NULL DROP TABLE t1,t2,t3,t4; +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/suite/pbxt/r/join_outer.result b/mysql-test/suite/pbxt/r/join_outer.result index 496095680ce..a556049b8cc 100644 --- a/mysql-test/suite/pbxt/r/join_outer.result +++ b/mysql-test/suite/pbxt/r/join_outer.result @@ -1,4 +1,6 @@ drop table if exists t0,t1,t2,t3,t4,t5; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; CREATE TABLE t1 ( grp int(11) default NULL, a bigint(20) unsigned default NULL, @@ -1198,3 +1200,4 @@ a b 3 3 4 NULL DROP TABLE t1,t2; +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result index 77630e5173b..a3ed13f3de0 100644 --- a/mysql-test/suite/pbxt/r/select.result +++ b/mysql-test/suite/pbxt/r/select.result @@ -1,6 +1,8 @@ drop table if exists t1,t2,t3,t4,t11; drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; drop view if exists v1; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL @@ -2202,10 +2204,10 @@ a a select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1; a a 1 2 -2 2 -3 2 1 3 +2 2 2 3 +3 2 3 3 select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; a a @@ -3636,3 +3638,4 @@ INSERT into t1 values (1), (2), (3); SELECT * FROM t1 LIMIT 2, -1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1' at line 1 DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index 02e1ed6c16a..fb4e93c57c8 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 @@ -1264,7 +1267,7 @@ insert into t1 values (1,0), (2,0), (3,0); insert into t2 values (1,1), (2,1), (3,1), (2,2); update ignore t1 set b=(select b from t2 where t1.a=t2.a); Warnings: -Error 1242 Subquery returns more than 1 row +Warning 1242 Subquery returns more than 1 row select * from t1; a b 1 1 @@ -1333,6 +1336,8 @@ create table t3 (a int, b int, index a (a)); insert into t1 values (1,10), (2,20), (3,30), (4,40); insert into t2 values (2), (3), (4), (5); insert into t3 values (10,3), (20,4), (30,5); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; select * from t2 where t2.a in (select a from t1); a 2 @@ -1360,9 +1365,9 @@ a 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary -1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index -1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index +1 PRIMARY t3 ref a a 5 test.t1.b 1 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) insert into t1 values (3,31); @@ -1381,6 +1386,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) +SET optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -1521,17 +1527,17 @@ a explain extended select * from t3 where a >= all (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary +2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select `test`.`t2`.`b` from `test`.`t2` group by 1) > <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(`test`.`t2`.`b`) from `test`.`t2`) > <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary +2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select `test`.`t2`.`b` from `test`.`t2` group by 1) <= <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1545,9 +1551,9 @@ a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary +2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select `test`.`t2`.`b` from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= NULL))) select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); @@ -1561,9 +1567,9 @@ a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary +2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select `test`.`t2`.`b` from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= NULL))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -2761,6 +2767,7 @@ max(fld) drop table t1; set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; +SET optimizer_switch='semijoin_with_cache=off'; CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -2836,15 +2843,15 @@ explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FR id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`flag` = 'N')) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; set @@optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); @@ -3429,7 +3436,7 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); a b @@ -3441,7 +3448,7 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL a NULL NULL NULL 9 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary DROP TABLE t1; create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); @@ -4220,6 +4227,9 @@ LEFT(t1.a1,1) SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); a2 DROP TABLE t1, t2, t3; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; +SET optimizer_switch='materialization=off'; CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)); INSERT INTO t1 VALUES ('a', 'aa'); INSERT INTO t1 VALUES ('a', 'aaa'); @@ -4251,6 +4261,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); a b DROP TABLE t1,t2; +SET optimizer_switch= @save_optimizer_switch; CREATE TABLE t1(a INT, b INT); INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4); EXPLAIN diff --git a/mysql-test/suite/pbxt/r/union.result b/mysql-test/suite/pbxt/r/union.result index cea55f3f204..1d40c836fe9 100644 --- a/mysql-test/suite/pbxt/r/union.result +++ b/mysql-test/suite/pbxt/r/union.result @@ -876,6 +876,8 @@ Slow_queries 0 drop table t1; create table t1 ( RID int(11) not null default '0', IID int(11) not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM; insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null); NAME PHONE NAME PHONE a 111 NULL NULL @@ -883,6 +885,7 @@ b 222 NULL NULL d 444 d 454 NULL NULL f 666 NULL NULL g 777 +SET optimizer_switch=@save_optimizer_switch; drop table t1; create table t1 (col1 tinyint unsigned, col2 tinyint unsigned); insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10); diff --git a/mysql-test/suite/pbxt/r/view_grant.result b/mysql-test/suite/pbxt/r/view_grant.result.THIS index cef441efc77..cef441efc77 100644 --- a/mysql-test/suite/pbxt/r/view_grant.result +++ b/mysql-test/suite/pbxt/r/view_grant.result.THIS diff --git a/mysql-test/suite/pbxt/t/derived.test b/mysql-test/suite/pbxt/t/derived.test index 3a4f86563a1..aefdd72ae24 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; @@ -286,3 +290,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/distinct.test b/mysql-test/suite/pbxt/t/distinct.test index 5d02b38aed8..854d7fc838e 100644 --- a/mysql-test/suite/pbxt/t/distinct.test +++ b/mysql-test/suite/pbxt/t/distinct.test @@ -158,6 +158,10 @@ create table t3 ( insert into t1 values (1,'yes'), (2,'no'); insert into t2 values (1,1); insert into t3 values (1,1); + +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; + EXPLAIN SELECT DISTINCT t1.id @@ -195,6 +199,8 @@ WHERE AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); drop table t1,t2,t3; +set join_cache_level=@save_join_cache_level; + # # Test using DISTINCT on a function that contains a group function # This also test the case when one doesn't use all fields in GROUP BY. diff --git a/mysql-test/suite/pbxt/t/endspace.test b/mysql-test/suite/pbxt/t/endspace.test index f2502640e71..a2962544a43 100644 --- a/mysql-test/suite/pbxt/t/endspace.test +++ b/mysql-test/suite/pbxt/t/endspace.test @@ -16,7 +16,8 @@ drop table if exists t1; create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)); insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); check table t1; -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or + text1 like 'teststring_%' ORDER BY text1; select * from t1 where text1='teststring' or text1 like 'teststring_%'; select * from t1 where text1='teststring' or text1 > 'teststring\t'; select * from t1 order by text1; @@ -24,7 +25,8 @@ explain select * from t1 order by text1; alter table t1 modify text1 char(32) binary not null; check table t1; -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or + text1 like 'teststring_%' ORDER BY text1; select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%'; select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; select text1, length(text1) from t1 order by text1; @@ -57,7 +59,8 @@ drop table t1; create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap; insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or + text1 like 'teststring_%' ORDER BY text1; select * from t1 where text1='teststring' or text1 like 'teststring_%'; select * from t1 where text1='teststring' or text1 >= 'teststring\t'; select * from t1 order by text1; @@ -97,7 +100,6 @@ select text1, length(text1) from t1 where text1='teststring' or text1 >= 'testst select concat('|', text1, '|') from t1 order by text1; drop table t1; - --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/func_group.test b/mysql-test/suite/pbxt/t/func_group.test index ca303030749..f3b466c463d 100644 --- a/mysql-test/suite/pbxt/t/func_group.test +++ b/mysql-test/suite/pbxt/t/func_group.test @@ -136,6 +136,8 @@ insert into t2 values('AAA', 10, 0.5); insert into t2 values('BBB', 20, 1.0); select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9; select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9; select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10; @@ -144,6 +146,7 @@ select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20; select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10; select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA'; select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; +SET optimizer_switch=@save_optimizer_switch; drop table t1,t2; # diff --git a/mysql-test/suite/pbxt/t/func_op.test b/mysql-test/suite/pbxt/t/func_op.test index 5ac127ad25f..4a6474df946 100644 --- a/mysql-test/suite/pbxt/t/func_op.test +++ b/mysql-test/suite/pbxt/t/func_op.test @@ -28,7 +28,10 @@ create table t1(a int); create table t2(a int, b int); insert into t1 values (1), (2), (3); insert into t2 values (1, 7), (3, 7); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select t1.a, t2.a, t2.b, bit_count(t2.b) from t1 left join t2 on t1.a=t2.a; +SET optimizer_switch=@save_optimizer_switch; drop table t1, t2; --disable_query_log diff --git a/mysql-test/suite/pbxt/t/greedy_optimizer.test b/mysql-test/suite/pbxt/t/greedy_optimizer.test index fd0be172e83..b3c05f4b3cd 100644 --- a/mysql-test/suite/pbxt/t/greedy_optimizer.test +++ b/mysql-test/suite/pbxt/t/greedy_optimizer.test @@ -10,6 +10,9 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7; --enable_warnings +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; + create table t1 ( c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer, primary key (c11) @@ -312,6 +315,8 @@ show status like 'Last_query_cost'; drop table t1,t2,t3,t4,t5,t6,t7; +set join_cache_level=@save_join_cache_level; + --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/group_by.test b/mysql-test/suite/pbxt/t/group_by.test index 46995ded550..76be09cd201 100644 --- a/mysql-test/suite/pbxt/t/group_by.test +++ b/mysql-test/suite/pbxt/t/group_by.test @@ -339,8 +339,11 @@ drop table t1,t2; CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID )); insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2; select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1; +SET optimizer_switch=@save_optimizer_switch; drop table t1; # diff --git a/mysql-test/suite/pbxt/t/heap.test b/mysql-test/suite/pbxt/t/heap.test.THIS index 452bcb754d3..452bcb754d3 100644 --- a/mysql-test/suite/pbxt/t/heap.test +++ b/mysql-test/suite/pbxt/t/heap.test.THIS diff --git a/mysql-test/suite/pbxt/t/join.test b/mysql-test/suite/pbxt/t/join.test index 02c17d8bcaa..fe73c67a6a3 100644 --- a/mysql-test/suite/pbxt/t/join.test +++ b/mysql-test/suite/pbxt/t/join.test @@ -4,6 +4,9 @@ drop table if exists t1,t2,t3; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + # # Test different join syntaxes # @@ -648,6 +651,8 @@ DESCRIBE tv2; DROP VIEW v1; DROP TABLE t1,t2,tv1,tv2; +SET optimizer_switch=@save_optimizer_switch; + --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/join_nested.test b/mysql-test/suite/pbxt/t/join_nested.test index 98ffcbc9a8a..25c8a5a782e 100644 --- a/mysql-test/suite/pbxt/t/join_nested.test +++ b/mysql-test/suite/pbxt/t/join_nested.test @@ -3,6 +3,9 @@ DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + CREATE TABLE t0 (a int, b int, c int); CREATE TABLE t1 (a int, b int, c int); CREATE TABLE t2 (a int, b int, c int); @@ -1063,6 +1066,9 @@ SELECT t1.*, t4.nm DROP TABLE t1,t2,t3,t4; + +SET optimizer_switch=@save_optimizer_switch; + --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/join_outer.test b/mysql-test/suite/pbxt/t/join_outer.test index a9635de7081..4c5006c6b0a 100644 --- a/mysql-test/suite/pbxt/t/join_outer.test +++ b/mysql-test/suite/pbxt/t/join_outer.test @@ -6,6 +6,9 @@ drop table if exists t0,t1,t2,t3,t4,t5; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + CREATE TABLE t1 ( grp int(11) default NULL, a bigint(20) unsigned default NULL, @@ -809,6 +812,8 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0); DROP TABLE t1,t2; +SET optimizer_switch=@save_optimizer_switch; + --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/select.test b/mysql-test/suite/pbxt/t/select.test index e870eb95fa4..ff455e6e90b 100644 --- a/mysql-test/suite/pbxt/t/select.test +++ b/mysql-test/suite/pbxt/t/select.test @@ -13,6 +13,9 @@ drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; drop view if exists v1; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL @@ -3135,6 +3138,9 @@ SELECT * FROM t1 LIMIT 2, -1; DROP TABLE t1; + +SET optimizer_switch=@save_optimizer_switch; + --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/subselect.test b/mysql-test/suite/pbxt/t/subselect.test index 651f47fcee1..3b49dfdebca 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); @@ -829,6 +832,8 @@ commit; enable_query_log; insert into t2 values (2), (3), (4), (5); insert into t3 values (10,3), (20,4), (30,5); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; select * from t2 where t2.a in (select a from t1); explain extended select * from t2 where t2.a in (select a from t1); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); @@ -839,6 +844,7 @@ insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31); explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); +SET optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; # @@ -1792,6 +1798,7 @@ drop table t1; set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; +SET optimizer_switch='semijoin_with_cache=off'; CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); @@ -3088,6 +3095,10 @@ DROP TABLE t1, t2, t3; # Bug #30788: Inconsistent retrieval of char/varchar # +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; +SET optimizer_switch='materialization=off'; + CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)); INSERT INTO t1 VALUES ('a', 'aa'); INSERT INTO t1 VALUES ('a', 'aaa'); @@ -3109,6 +3120,7 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); DROP TABLE t1,t2; +SET optimizer_switch= @save_optimizer_switch; # # Bug #32400: Complex SELECT query returns correct result only on some @@ -3223,6 +3235,7 @@ INSERT INTO t1 VALUES ('a'); SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); DROP TABLE t1; + --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/union.test b/mysql-test/suite/pbxt/t/union.test index a66aba0d46c..e324d824636 100644 --- a/mysql-test/suite/pbxt/t/union.test +++ b/mysql-test/suite/pbxt/t/union.test @@ -481,7 +481,10 @@ drop table t1; # create table t1 ( RID int(11) not null default '0', IID int(11) not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM; insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null); +SET optimizer_switch=@save_optimizer_switch; drop table t1; # diff --git a/mysql-test/suite/pbxt/t/view_grant.test b/mysql-test/suite/pbxt/t/view_grant.test.THIS index 525d9423bb1..525d9423bb1 100644 --- a/mysql-test/suite/pbxt/t/view_grant.test +++ b/mysql-test/suite/pbxt/t/view_grant.test.THIS |