diff options
Diffstat (limited to 'mysql-test/r/subselect_sj2.result')
| -rw-r--r-- | mysql-test/r/subselect_sj2.result | 91 |
1 files changed, 58 insertions, 33 deletions
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 00c62920fce..ce16f3b95ba 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1,3 +1,6 @@ +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; drop view if exists v1; create table t0 (a int); @@ -32,9 +35,8 @@ a b 9 5 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 subselect2 ALL unique_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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t2 ref b b 5 test.t1.a 2 End temporary select * from t2 where b in (select a from t1); a b 1 1 @@ -51,8 +53,8 @@ primary key(pk1, pk2, pk3) insert into t3 select a,a, a,a,a 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 10 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t3 ref b b 5 test.t1.a 1 End temporary select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -74,9 +76,8 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; 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 subselect2 ALL unique_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 +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Start temporary +1 PRIMARY t3 ref b b 5 test.t0.a 1 End temporary 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 @@ -97,9 +98,8 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY t2 index b b 5 NULL 10 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ref b b 5 test.t1.a 2 Using index; FirstMatch(t1) select * from t1; a b 1 1 @@ -126,9 +126,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 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 subselect2 ALL unique_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer -2 SUBQUERY it ALL NULL NULL NULL NULL 22 +1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary +1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; End temporary; Using join buffer (flat, BNL join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -160,8 +159,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) 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 subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY it ALL NULL NULL NULL NULL 32 +1 PRIMARY it ALL NULL NULL NULL NULL 32 Using where; FirstMatch(ot) select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -194,9 +192,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 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 subselect2 ALL unique_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer -2 SUBQUERY it ALL NULL NULL NULL NULL 22 +1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary +1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; End temporary; Using join buffer (flat, BNL join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -228,8 +225,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) 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 subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY it ALL NULL NULL NULL NULL 52 +1 PRIMARY it ALL NULL NULL NULL NULL 52 Using where; FirstMatch(ot) select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -268,10 +264,10 @@ explain select * from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 +1 PRIMARY t1 index a a 5 NULL 10 Using where; Using index 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index -1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary +1 PRIMARY t3 ref a a 5 test.t1.a 1 Using index; FirstMatch(t0) drop table t0, t1,t2,t3; CREATE TABLE t1 ( ID int(11) NOT NULL auto_increment, @@ -299,6 +295,8 @@ Percentage float(3,1) NOT NULL default '0.0', PRIMARY KEY (Country, Language), INDEX (Percentage) ); +set @bug35674_save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='materialization=off'; EXPLAIN SELECT Name FROM t2 WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000) @@ -307,9 +305,10 @@ t2.Code IN (SELECT Country FROM t3 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; Using MRR -1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where -1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t3.Country 1 Using index condition; Using where +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 +set optimizer_switch=@bug35674_save_optimizer_switch; DROP TABLE t1,t2,t3; CREATE TABLE t1 ( Code char(3) NOT NULL DEFAULT '', @@ -345,8 +344,7 @@ WHERE t1.Code IN ( 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 subselect2 eq_ref unique_key unique_key 3 func 1 -2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where +1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; FirstMatch(t1) SELECT Name FROM t1 WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); @@ -356,6 +354,10 @@ Canada China Czech Republic drop table t1, t2; +drop procedure if exists p1; +drop procedure if exists p2; +drop procedure if exists p3; +drop procedure if exists p4; CREATE TABLE t1(a INT); CREATE TABLE t2(c INT); CREATE PROCEDURE p1(v1 int) @@ -422,7 +424,7 @@ explain extended select * from t0 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where 1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary Warnings: @@ -688,9 +690,8 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: 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 subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY t3 index a a 5 NULL 30000 Using index +1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index +1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -720,3 +721,27 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) drop table t2, t3; +# +# BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 +# +CREATE TABLE t1 ( f1 int NOT NULL , f10 int) ; +INSERT IGNORE INTO t1 VALUES (25,0),(29,0); +CREATE TABLE t2 ( f10 int) ENGINE=InnoDB; +CREATE TABLE t3 ( f11 int) ; +INSERT IGNORE INTO t3 VALUES (0); +SELECT alias1.f10 AS field2 +FROM t2 AS alias1 +JOIN ( +t3 AS alias2 +JOIN t1 AS alias3 +ON alias3.f10 +) ON alias3.f1 +WHERE alias2.f11 IN ( +SELECT SQ4_alias1.f10 +FROM t1 AS SQ4_alias1 +LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10 +) +GROUP BY field2; +field2 +drop table t1, t2, t3; +set optimizer_switch=@subselect_sj2_tmp; |