diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 99 |
1 files changed, 99 insertions, 0 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test new file mode 100644 index 00000000000..dbf45262a1e --- /dev/null +++ b/mysql-test/t/subselect.test @@ -0,0 +1,99 @@ + +select (select 2); +SELECT (SELECT 1) UNION SELECT (SELECT 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 (select a from t1 where t1.a=t2.b), a from t2; +select (select a from t1), a from t2; +select (select a from t3), a from t2; +select * from t2 where t2.a=(select a from t1); +insert into t3 values (6),(7),(3); +select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); +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; +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); +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); +select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; +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; +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; +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); +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); +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); +select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +select * from t3 where exists (select * from t2 where t2.b=t3.a); +select * from t3 where not exists (select * from t2 where t2.b=t3.a); +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); +select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) +from t2 where t2.b=t4.b); +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; +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; +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; +explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +-- error 1240 +select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; +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); + +# not unique fields +-- error 1052 +select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); + +# different tipes & group functions +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); +SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2); +SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3); + +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'); +-- error 1239 +SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM +inscrit WHERE pseudo='joce'); +-- error 1239 +SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE +pseudo='joce'); +SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce'); +-- error 1240 +SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo LIKE '%joce%'); + +drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;
\ No newline at end of file |