diff options
Diffstat (limited to 'mysql-test/r/subselect3.result')
| -rw-r--r-- | mysql-test/r/subselect3.result | 38 |
1 files changed, 21 insertions, 17 deletions
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 78a75776573..9720a586147 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1023,7 +1023,7 @@ update t22 set c = '2005-12-08 15:58:27' where a = 255; explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 Using temporary; Using filesort 1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (flat, BNL join) 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where @@ -1041,7 +1041,7 @@ select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) a id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1 +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 5 func 1 3 SUBQUERY Z ALL NULL NULL NULL NULL 2 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq @@ -1112,7 +1112,7 @@ set @@optimizer_switch=@save_optimizer_switch; 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 subselect3 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 func 1 3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 2 DERIVED t0 ALL NULL NULL NULL NULL 11 drop table t0, t1; @@ -1126,7 +1126,7 @@ insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 100 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index create table t4 (pk int primary key); insert into t4 select a from t3; @@ -1134,7 +1134,7 @@ explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 100 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR 2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index drop table t1, t3, t4; @@ -1167,7 +1167,7 @@ create table t3 ( a int , filler char(100), key(a)); insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 1 PRIMARY t3 ref a a 5 test.t2.a 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where a in (select a from t2); @@ -1216,22 +1216,25 @@ insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 test.A.a 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 test.t2.a 1 2 SUBQUERY A ALL NULL NULL NULL NULL 10 2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 test.t2.a 1 2 SUBQUERY A ALL NULL NULL NULL NULL 10 2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select straight_join * from t2 X, t2 Y @@ -1239,6 +1242,7 @@ where X.a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 test.X.a 1 2 SUBQUERY A ALL NULL NULL NULL NULL 10 2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) create table t0 (a int, b int); @@ -1246,14 +1250,14 @@ insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where 2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where drop table t0,t1,t2,t3,t4; @@ -1265,12 +1269,12 @@ create table t2 as select * from t1; explain select * from t2 where a in (select b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index explain select * from t2 where (b,a) in (select a,b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index drop table t1,t2; create table t1 (a int, b int); @@ -1281,14 +1285,14 @@ set @@optimizer_switch='firstmatch=off'; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=@save_optimizer_switch; @@ -1300,7 +1304,7 @@ insert into t1 select * from t0; explain select * from t0 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 2 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 select * from t0 where a in (select a from t1); a @@ -1314,7 +1318,7 @@ insert into t1 select * from t0; explain select * from t0 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 2 -1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 select * from t0 where a in (select a from t1); a @@ -1329,7 +1333,7 @@ insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); 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 15 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 15 func,func,func 1 2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) 2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) @@ -1402,7 +1406,7 @@ INNER JOIN t2 c ON c.idContact=cona.idContact WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join) 2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 |