diff options
Diffstat (limited to 'mysql-test/r/subselect_sj2.result')
| -rw-r--r-- | mysql-test/r/subselect_sj2.result | 43 |
1 files changed, 23 insertions, 20 deletions
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index c95aaab1c13..85a3b6971bd 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1,7 +1,10 @@ set @subselect_sj2_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; -drop table if exists t0, t1, t2, t3; +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'outer_join_with_cache=off'); +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'semijoin_with_cache=off'); +set join_cache_level=1; +drop table if exists t0, t1, t2, t3, t4, t5; drop view if exists v1; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -48,7 +51,7 @@ explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where select * from t2 where b in (select a from t1); a b 1 1 @@ -66,9 +69,9 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL b NULL NULL NULL 20 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t3 ref b b 5 test.t1.a 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -94,7 +97,7 @@ explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 -2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where +2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 0 0 0 0 @@ -145,7 +148,7 @@ from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY it ALL NULL NULL NULL NULL 22 +2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -178,7 +181,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY it ALL NULL NULL NULL NULL 32 +2 MATERIALIZED it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -213,7 +216,7 @@ from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY it ALL NULL NULL NULL NULL 22 +2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -246,7 +249,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY it ALL NULL NULL NULL NULL 52 +2 MATERIALIZED it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -287,9 +290,9 @@ from t0 where a in id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 Using where -2 SUBQUERY t1 index a a 5 NULL 10 Using where; Using index -2 SUBQUERY t2 ref a a 5 test.t1.a 1 Using index -2 SUBQUERY t3 ref a a 5 test.t1.a 1 Using index +2 MATERIALIZED t1 index a a 5 NULL 10 Using where; Using index +2 MATERIALIZED t2 ref a a 5 test.t1.a 1 Using index +2 MATERIALIZED t3 ref a a 5 test.t1.a 1 Using index drop table t0, t1,t2,t3; CREATE TABLE t1 ( ID int(11) NOT NULL auto_increment, @@ -328,8 +331,8 @@ WHERE Language='English' AND Percentage > 10 AND t2.Population > 100000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Rowid-ordered scan; Start temporary -1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where -1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; End temporary +1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary +1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where set optimizer_switch=@bug35674_save_optimizer_switch; DROP TABLE t1,t2,t3; CREATE TABLE t1 ( @@ -367,7 +370,7 @@ SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where +2 MATERIALIZED t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); @@ -602,7 +605,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where -2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 10 Using index +2 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 10 Using index drop table t0, t1, t2, t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -716,7 +719,7 @@ explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY t3 index a a 5 NULL 30000 Using index +2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -872,10 +875,10 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 3 DERIVED t1 ALL NULL NULL NULL NULL 1 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); |