diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 35 |
1 files changed, 18 insertions, 17 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 3e983ecf333..74049298981 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -907,7 +907,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) F id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index -2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; @@ -1297,7 +1297,7 @@ a explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); @@ -1307,7 +1307,7 @@ a explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1317,7 +1317,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) @@ -1339,7 +1339,7 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1); 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 index +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 101 100.00 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`) @@ -1349,7 +1349,7 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); 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 index +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 101 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)) @@ -1359,8 +1359,8 @@ 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 index -1 PRIMARY t3 index a a 5 NULL 3 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index 1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.61 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`.`t1`.`b` = `test`.`t3`.`a`)) @@ -1376,7 +1376,7 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); 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 index +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 101 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)) @@ -2831,9 +2831,10 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cac explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); 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 t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1) +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 1.00 +2 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` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N')) +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 @@ -4203,8 +4204,8 @@ CREATE INDEX I1 ON t1 (a); CREATE INDEX I2 ON t1 (b); EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1) SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); a b CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); @@ -4213,15 +4214,15 @@ CREATE INDEX I1 ON t2 (a); CREATE INDEX I2 ON t2 (b); EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan -1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition +1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where +1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 Using where; Using index; FirstMatch(t2) SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); a b EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1) SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); a b DROP TABLE t1,t2; |