diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 21 |
1 files changed, 10 insertions, 11 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 539382aa092..1ca1fec6897 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -47,13 +47,13 @@ SELECT (SELECT a) as a; ERROR 42S22: Reference 'a' not supported (forward reference in item list) EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><'1'>((select '1')) = 1) +Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><`b`.`a`>((select `b`.`a`)) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -202,11 +202,10 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00 -3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort Warnings: -Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt` +Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a` > 1) select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); a 2 @@ -366,9 +365,9 @@ INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index +4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index 2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 -3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index +3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM @@ -1340,7 +1339,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 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) +1 PRIMARY t1 ref a a 5 test.t2.a 100 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`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); @@ -1350,7 +1349,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 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) +1 PRIMARY t1 ref a a 5 test.t2.a 100 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)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1361,7 +1360,7 @@ explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where 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 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) +1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 11 100.00 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`)) insert into t1 values (3,31); @@ -1377,7 +1376,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 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) +1 PRIMARY t1 ref a a 5 test.t2.a 100 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)) drop table t0, t1, t2, t3; |