diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/derived.test | 6 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 21 |
2 files changed, 17 insertions, 10 deletions
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 6d1d825a523..1dbdd6e0ae8 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -37,7 +37,11 @@ select * from (select * from t1 union select * from t1) a; select * from (select * from t1 union all select * from t1) a; explain select * from (select * from t1 union select * from t1) a; explain select * from (select * from t1 union all select * from t1) a; -drop table if exists t1; +CREATE TABLE t2 (a int not null); +insert into t2 values(1); +select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a; +select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a; +drop table t1, t2; create table t1(a int not null, t char(8), index(a)); disable_query_log; let $1 = 10000; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index e715cd1d09f..baf58916bd4 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -206,19 +206,22 @@ SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='j SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b; -- error 1054 SELECT 1 IN (SELECT 1 FROM t2 HAVING a); -SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date); -SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); + +SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic); +SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100); SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1); -SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date); -SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); +SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic); +SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100); SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1); -SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date); -SELECT topic FROM t2 GROUP BY date; -SELECT topic FROM t2 GROUP BY date HAVING topic < 4100; -SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); -SELECT *, date as fff from t2 where not (SELECT date FROM t2 GROUP BY date HAVING topic < 4100 and fff!=date); +SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic); +SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100); +SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100; +SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2; SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2); SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2); +delete from t2 where topic=40143; +SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2; +SELECT *, topic as fff, exists (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100 and fff = topic) from t2; drop table t1,t2; #forumconthardwarefr7 |