diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 23 |
1 files changed, 23 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index dada87803ad..54528f58697 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -46,6 +46,13 @@ a b 2 7 3 8 4 8 +explain select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) +union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ALL NULL NULL NULL NULL 2 where used +3 UNION t4 ALL NULL NULL NULL NULL 3 where used; Using filesort +4 SUBSELECT t2 ALL NULL NULL NULL NULL 2 +2 SUBSELECT t3 ALL NULL NULL NULL NULL 3 Using filesort select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 @@ -54,6 +61,12 @@ 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; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 +explain 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 Extra +3 DERIVED t2 ALL NULL NULL NULL NULL 2 where used +1 FIRST <derived3> system NULL NULL NULL NULL 1 +2 SUBSELECT t3 ALL NULL NULL NULL NULL 3 where used; Using filesort 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 @@ -67,6 +80,11 @@ b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) 8 7.5000 8 4.5000 9 7.5000 +explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t4 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBSELECT t2 ALL NULL NULL NULL NULL 2 +3 DEPENDENT SUBSELECT t3 ALL NULL NULL NULL NULL 3 where used select * from t3 where exists (select * from t2 where t2.b=t3.a); a 7 @@ -97,6 +115,11 @@ select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a NULL 1 2 2 +explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBSELECT t1 system NULL NULL NULL NULL 1 where used +3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 where used select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; Subselect return more than 1 record create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq)); |