diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 181 |
1 files changed, 181 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result new file mode 100644 index 00000000000..b5b944583da --- /dev/null +++ b/mysql-test/r/subselect.result @@ -0,0 +1,181 @@ +select (select 2); +(select 2) +2 +drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit; +create table t1 (a int); +create table t2 (a int, b int); +create table t3 (a int); +create table t4 (a int, b int); +insert into t1 values (2); +insert into t2 values (1,7),(2,7); +insert into t4 values (4,8),(3,8),(5,9); +select (select a from t1 where t1.a=t2.a), a from t2; +(select a from t1 where t1.a=t2.a) a +NULL 1 +2 2 +select (select a from t1 where t1.a=t2.b), a from t2; +(select a from t1 where t1.a=t2.b) a +NULL 1 +NULL 2 +select (select a from t1), a from t2; +(select a from t1) a +2 1 +2 2 +select (select a from t3), a from t2; +(select a from t3) a +NULL 1 +NULL 2 +select * from t2 where t2.a=(select a from t1); +a b +2 7 +insert into t3 values (6),(7),(3); +select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); +a b +1 7 +2 7 +select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) +union (select * from t4 order by a limit 2) limit 3; +a b +1 7 +2 7 +3 8 +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); +a b +1 7 +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 PRIMARY 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 +7 2 +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 PRIMARY <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 +select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); +a +2 +select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); +a +select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +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 PRIMARY 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 +select * from t3 where not exists (select * from t2 where t2.b=t3.a); +a +6 +3 +insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9); +select b,max(a) as ma from t4 group by b having b < (select max(t2.a) +from t2 where t2.b=t4.b); +b ma +select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) +from t2 where t2.b=t4.b); +b ma +7 12 +create table t5 (a int); +select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a +NULL 1 +2 2 +insert into t5 values (5); +select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a +NULL 1 +2 2 +insert into t5 values (2); +select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +(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 PRIMARY 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 returns more than 1 record +create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq)); +create table clinic( uq int primary key, name char(25)); +insert into clinic values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); +insert into attend values (1,1),(1,2),(2,2),(1,3); +select * from attend where exists (select * from clinic where uq = clinic_uq); +patient_uq clinic_uq +1 1 +1 2 +2 2 +select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); +Column: 'a' in field list is ambiguous +drop table if exists t1,t2,t3; +CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0'); +INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b'); +CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0'); +INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2'); +CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00'); +INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13'); +SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1); +a b +W 1732-02-22 +SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2); +a b +W 1 +SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3); +a b +W a +drop table if exists inscrit; +CREATE TABLE `inscrit` ( +`pseudo` varchar(35) character set latin1 NOT NULL default '', +`email` varchar(60) character set latin1 NOT NULL default '', +PRIMARY KEY (`pseudo`), +UNIQUE KEY `email` (`email`) +) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; +INSERT INTO inscrit (pseudo,email) VALUES ('joce','test'); +INSERT INTO inscrit (pseudo,email) VALUES ('joce1','test1'); +INSERT INTO inscrit (pseudo,email) VALUES ('2joce1','2test1'); +EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE pseudo=(SELECT +pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE pseudo=(SELECT +pseudo FROM inscrit WHERE pseudo='joce'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY inscrit const PRIMARY PRIMARY 35 const 1 +4 SUBSELECT inscrit const PRIMARY PRIMARY 35 const 1 +2 SUBSELECT inscrit const PRIMARY PRIMARY 35 const 1 +3 SUBSELECT inscrit const PRIMARY PRIMARY 35 const 1 +SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM +inscrit WHERE pseudo='joce'); +Subselect returns more than 1 field +SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE +pseudo='joce'); +Subselect returns more than 1 field +SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce'); +pseudo +joce +SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo LIKE '%joce%'); +Subselect returns more than 1 record +drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit; |