diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 236 |
1 files changed, 192 insertions, 44 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 10e73a7f971..eeac0bbb81e 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1,7 +1,21 @@ select (select 2); SELECT (SELECT 1) UNION SELECT (SELECT 2); SELECT (SELECT (SELECT 0 UNION SELECT 0)); -drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit; +-- error 1245 +SELECT (SELECT 1 FROM (SELECT 1) HAVING a=1) as a; +-- error 1245 +SELECT (SELECT 1 FROM (SELECT 1) HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) HAVING a=1) as b; +SELECT (SELECT 1),MAX(1) FROM (SELECT 1); +-- error 1245 +SELECT (SELECT a) as a; +EXPLAIN SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1; +SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1; +-- error 1054 +SELECT (SELECT 1), a; +SELECT 1 as a FROM (SELECT 1) HAVING (SELECT a)=1; +-- error 1054 +SELECT 1 FROM (SELECT (SELECT a)); +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; create table t1 (a int); create table t2 (a int, b int); create table t3 (a int); @@ -9,6 +23,8 @@ 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); +-- error 1245 +select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1; 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; @@ -18,7 +34,7 @@ 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) +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); @@ -64,11 +80,11 @@ select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) 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); +create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); +create table t7( uq int primary key, name char(25)); +insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); +insert into t6 values (1,1),(1,2),(2,2),(1,3); +select * from t6 where exists (select * from t7 where uq = clinic_uq); # not unique fields -- error 1052 @@ -87,67 +103,199 @@ 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; +drop table if exists t8; -CREATE TABLE `inscrit` ( +CREATE TABLE `t8` ( `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'); +INSERT INTO t8 (pseudo,email) VALUES ('joce','test'); +INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); +INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); +EXPLAIN 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'); -- error 1239 -SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM -inscrit WHERE pseudo='joce'); +SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM +t8 WHERE pseudo='joce'); -- error 1239 -SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE +SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE pseudo='joce'); -SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce'); +SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); -- error 1240 -SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo LIKE '%joce%'); +SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%'); -drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; -drop table if exists searchconthardwarefr3, forumconthardwarefr7; -CREATE TABLE `searchconthardwarefr3` ( +#searchconthardwarefr3 forumconthardwarefr7 +CREATE TABLE `t1` ( `topic` mediumint(8) unsigned NOT NULL default '0', `date` date NOT NULL default '0000-00-00', `pseudo` varchar(35) character set latin1 NOT NULL default '', PRIMARY KEY (`pseudo`,`date`,`topic`), KEY `topic` (`topic`) ) TYPE=MyISAM ROW_FORMAT=DYNAMIC; -INSERT INTO searchconthardwarefr3 (topic,date,pseudo) VALUES +INSERT INTO t1 (topic,date,pseudo) VALUES ('43506','2002-10-02','joce'),('40143','2002-08-03','joce'); -EXPLAIN SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03'; -EXPLAIN SELECT (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03'); -SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03'; -SELECT (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03'); -SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; +EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; +EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); +SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; +SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); +SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; -- error 1240 -SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; -EXPLAIN SELECT 1 FROM searchconthardwarefr3 WHERE 1=(SELECT 1 UNION SELECT 1); +SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; +EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); +drop table t1; + +#forumconthardwarefr7 searchconthardwarefr7 +CREATE TABLE `t1` ( + `numeropost` mediumint(8) unsigned NOT NULL auto_increment, + `maxnumrep` int(10) unsigned NOT NULL default '0', + PRIMARY KEY (`numeropost`), + UNIQUE KEY `maxnumrep` (`maxnumrep`) +) TYPE=MyISAM ROW_FORMAT=FIXED; + +INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); + +CREATE TABLE `t2` ( + `mot` varchar(30) NOT NULL default '', + `topic` mediumint(8) unsigned NOT NULL default '0', + `date` date NOT NULL default '0000-00-00', + `pseudo` varchar(35) NOT NULL default '', + PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`) + ) TYPE=MyISAM ROW_FORMAT=DYNAMIC; + +INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce'); +select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); +SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20; -- error 1054 -SELECT 1 IN (SELECT 1 FROM searchconthardwarefr3 HAVING a); -SELECT * from searchconthardwarefr3 where topic IN (SELECT topic FROM searchconthardwarefr3 GROUP BY date); -SELECT * from searchconthardwarefr3 where topic IN (SELECT topic FROM searchconthardwarefr3 GROUP BY date HAVING topic < 4100); -SELECT * from searchconthardwarefr3 where topic IN (SELECT SUM(topic) FROM searchconthardwarefr3); -SELECT * from searchconthardwarefr3 where topic = any (SELECT topic FROM searchconthardwarefr3 GROUP BY date); -SELECT * from searchconthardwarefr3 where topic = any (SELECT topic FROM searchconthardwarefr3 GROUP BY date HAVING topic < 4100); -SELECT * from searchconthardwarefr3 where topic = any (SELECT SUM(topic) FROM searchconthardwarefr3); -SELECT * from searchconthardwarefr3 where topic = all (SELECT topic FROM searchconthardwarefr3 GROUP BY date); -SELECT * from searchconthardwarefr3 where topic = all (SELECT topic FROM searchconthardwarefr3 GROUP BY date HAVING topic < 4100); -SELECT * from searchconthardwarefr3 where topic = all (SELECT SUM(topic) FROM searchconthardwarefr3); -SELECT * from searchconthardwarefr3 where topic <> any (SELECT SUM(topic) FROM searchconthardwarefr3); -CREATE TABLE `forumconthardwarefr7` ( +SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1); +drop table t1, t2; + +#forumconthardwarefr7 +drop table if exists t1; +CREATE TABLE `t1` ( + `numeropost` mediumint(8) unsigned NOT NULL auto_increment, + `maxnumrep` int(10) unsigned NOT NULL default '0', + PRIMARY KEY (`numeropost`), + UNIQUE KEY `maxnumrep` (`maxnumrep`) +) TYPE=MyISAM ROW_FORMAT=FIXED; + +INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1); +-- error 1240 +select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); +-- error 1240 +select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1); +drop table t1; + +#iftest +CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b'); +INSERT INTO t1 VALUES (); +-- error 1240 +SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b'); +drop table t1; + +# threadhardwarefr7 +CREATE TABLE `t1` ( + `numeropost` mediumint(8) unsigned NOT NULL default '0', + `numreponse` int(10) unsigned NOT NULL auto_increment, + `pseudo` varchar(35) NOT NULL default '', + PRIMARY KEY (`numeropost`,`numreponse`), + UNIQUE KEY `numreponse` (`numreponse`), + KEY `pseudo` (`pseudo`,`numeropost`) +) TYPE=MyISAM; +-- error 1245 +SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a; +-- error 1054 +SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; +SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; +INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); +-- error 1240 +EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; +EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); +SELECT 1 IN (SELECT 1 FROM t1 HAVING a); +SELECT * from t1 where topic IN (SELECT topic FROM t1 GROUP BY date); +SELECT * from t1 where topic IN (SELECT topic FROM t1 GROUP BY date HAVING topic < 4100); +SELECT * from t1 where topic IN (SELECT SUM(topic) FROM t1); +SELECT * from t1 where topic = any (SELECT topic FROM t1 GROUP BY date); +SELECT * from t1 where topic = any (SELECT topic FROM t1 GROUP BY date HAVING topic < 4100); +SELECT * from t1 where topic = any (SELECT SUM(topic) FROM t1); +SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY date); +SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY date HAVING topic < 4100); +SELECT * from t1 where topic = all (SELECT SUM(topic) FROM t1); +SELECT * from t1 where topic <> any (SELECT SUM(topic) FROM t1); +CREATE TABLE `t2` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, `maxnumrep` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`numeropost`), UNIQUE KEY `maxnumrep` (`maxnumrep`)) TYPE=MyISAM ROW_FORMAT=FIXED; -INSERT INTO forumconthardwarefr7 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); -SELECT SQL_CALC_FOUND_ROWS numeropost,maxnumrep FROM forumconthardwarefr7 WHERE numeropost IN (SELECT topic FROM searchconthardwarefr3 WHERE pseudo='joce' AND date >= '2002-10-06') ORDER BY maxnumrep DESC LIMIT 0,20; -drop table searchconthardwarefr3, forumconthardwarefr7;
\ No newline at end of file +INSERT INTO t2 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); +SELECT SQL_CALC_FOUND_ROWS numeropost,maxnumrep FROM t2 WHERE numeropost IN (SELECT topic FROM t1 WHERE pseudo='joce' AND date >= '2002-10-06') ORDER BY maxnumrep DESC LIMIT 0,20; +drop table t1,t2; + +CREATE TABLE t1 (a int(1)); +INSERT INTO t1 VALUES (1); +SELECT 1 FROM (SELECT a FROM t1) HAVING (SELECT a)=1; +drop table t1; + +#update with subselects +create table t1 (a int NOT NULL, b int, primary key (a)); +create table t2 (a int NOT NULL, b int, primary key (a)); +insert into t1 values (0, 10),(1, 11),(2, 12); +insert into t2 values (1, 21),(2, 22),(3, 23); +select * from t1; +update t1 set b= (select b from t2 where t1.a = t2.a); +select * from t1; +drop table t1, t2; + +#delete with subselects +create table t1 (a int NOT NULL, b int, primary key (a)); +create table t2 (a int NOT NULL, b int, primary key (a)); +insert into t1 values (0, 10),(1, 11),(2, 12); +insert into t2 values (1, 21),(2, 12),(3, 23); +select * from t1; +select * from t1 where b = (select b from t2 where t1.a = t2.a); +delete from t1 where b = (select b from t2 where t1.a = t2.a); +select * from t1; +drop table t1, t2; + +#insert with subselects +CREATE TABLE t1 (x int); +create table t2 (a int); +insert into t2 values (1); +INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); +select * from t1; +insert into t2 values (1); +INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2)); +-- sleep 1 +select * from t1; +INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2; +select * from t1; +-- error 1093 +INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2; +INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t1)); +-- sleep 1 +select * from t1; +drop table t1, t2; + +#replace with subselects +CREATE TABLE t1 (x int not null, y int, primary key (x)); +create table t2 (a int); +insert into t2 values (1); +select * from t1; +replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); +select * from t1; +replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2)); +select * from t1; +replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2)); +-- sleep 1 +select * from t1; +replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2)); +-- sleep 1 +select * from t1; +replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2)); +select * from t1; +drop table t1, t2; |