diff options
Diffstat (limited to 'mysql-test/r/subselect3_jcl6.result')
| -rw-r--r-- | mysql-test/r/subselect3_jcl6.result | 38 |
1 files changed, 21 insertions, 17 deletions
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index a3ed949cda2..1a5bcd005ab 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1030,7 +1030,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 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (flat, BNLH join) 1 PRIMARY t22 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join) 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where @@ -1048,7 +1048,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 @@ -1119,7 +1119,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; @@ -1133,7 +1133,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; @@ -1141,7 +1141,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; @@ -1174,7 +1174,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 Using join buffer (flat, BKA join) 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where a in (select a from t2); @@ -1223,22 +1223,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 Using join buffer (flat, BKA join) 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 @@ -1246,6 +1249,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); @@ -1253,14 +1257,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 Using join buffer (flat, BKA join) 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; Using join buffer (flat, BKA join) 2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where drop table t0,t1,t2,t3,t4; @@ -1272,12 +1276,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); @@ -1288,14 +1292,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; @@ -1307,7 +1311,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 @@ -1321,7 +1325,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 @@ -1336,7 +1340,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 (incremental, BNL join) @@ -1409,7 +1413,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 Using join buffer (flat, BKA join) |