diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect.result | 14 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 12 |
2 files changed, 10 insertions, 16 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index d442e4d97ce..e2a5494eaf7 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -81,21 +81,18 @@ 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; +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); +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); +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 Using where 2 SUBSELECT t3 ALL NULL NULL NULL NULL 3 Using filesort @@ -330,19 +327,15 @@ Unknown column 'a' in 'having clause' SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date); mot topic date pseudo joce 40143 2002-10-22 joce -joce 43506 2002-10-22 joce SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); mot topic date pseudo -joce 43506 2002-10-22 joce SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1); mot topic date pseudo SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date); mot topic date pseudo joce 40143 2002-10-22 joce -joce 43506 2002-10-22 joce SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); mot topic date pseudo -joce 43506 2002-10-22 joce SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1); mot topic date pseudo SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date); @@ -351,6 +344,7 @@ joce 40143 2002-10-22 joce SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); mot topic date pseudo joce 40143 2002-10-22 joce +joce 43506 2002-10-22 joce SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2); mot topic date pseudo SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2); diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 8b174882bc6..c22827dff18 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -42,12 +42,9 @@ 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 * 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; @@ -190,7 +187,10 @@ 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 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 SUM(topic) FROM t2); SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2); drop table t1,t2; |