diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/distinct.result | 3 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 21 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 12 |
3 files changed, 31 insertions, 5 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index e347a95b037..89f4a8ae7f2 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -92,7 +92,8 @@ NULL NULL NULL 0 0 select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp; grp count(*) -0 7 +NULL 1 +0 6 1 6 SELECT DISTINCT FACILITY FROM t1; FACILITY diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index bd92c496f29..e7cfa50d7f7 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1,7 +1,7 @@ select (select 2); (select 2) 2 -drop table if exists t1,t2,t3,t4; +drop table if exists t1,t2,t3,t4,t5; create table t1 (a int); create table t2 (a int, b int); create table t3 (a int); @@ -82,4 +82,21 @@ 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 -drop table t1,t2,t3,t4; +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 +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 return more than 1 record +drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 7b1ebdcbc27..224149900d2 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1,6 +1,6 @@ select (select 2); -drop table if exists t1,t2,t3,t4; +drop table if exists t1,t2,t3,t4,t5; create table t1 (a int); create table t2 (a int, b int); create table t3 (a int); @@ -33,4 +33,12 @@ 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); -drop table t1,t2,t3,t4; +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; +-- error 1230 +select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; +drop table t1,t2,t3,t4,t5; |