diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/having.result | 174 | ||||
| -rw-r--r-- | mysql-test/t/having.test | 196 |
2 files changed, 258 insertions, 112 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 04f73792dd6..6f548d20668 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2; +drop table if exists t1,t2,t3; create table t1 (a int); select count(a) as b from t1 where a=0 having b > 0; b @@ -128,74 +128,76 @@ id description c 1 test 0 2 test2 0 drop table t1,t2,t3; -drop table if exists wl1972; -create table wl1972 ( -col1 int, -col2 varchar(5) character set latin1 collate latin1_bin) -engine=innodb; -insert into wl1972 values(10,'hello'); -insert into wl1972 values(20,'hello'); -insert into wl1972 values(30,'hello'); -insert into wl1972 values(10,'bye'); -insert into wl1972 values(10,'sam'); -insert into wl1972 values(10,'bob'); -select count(*) from wl1972 group by col1 having col1 = 10; +create table t1 (col1 int, col2 varchar(5), col_t1 int); +create table t2 (col1 int, col2 varchar(5), col_t2 int); +create table t3 (col1 int, col2 varchar(5), col_t3 int); +insert into t1 values(10,'hello',10); +insert into t1 values(20,'hello',20); +insert into t1 values(30,'hello',30); +insert into t1 values(10,'bye',10); +insert into t1 values(10,'sam',10); +insert into t1 values(10,'bob',10); +insert into t2 select * from t1; +insert into t3 select * from t1; +select count(*) from t1 group by col1 having col1 = 10; count(*) 4 -select count(*) as count_col1 from wl1972 group by col1 having col1 = 10; +select count(*) as count_col1 from t1 group by col1 having col1 = 10; count_col1 4 -select count(*) as count_col1 from wl1972 as t1 group by col1 having col1 = 10; +select count(*) as count_col1 from t1 as tmp1 group by col1 having col1 = 10; count_col1 4 -select count(*) from wl1972 group by col2 having col2 = 'hello'; +select count(*) from t1 group by col2 having col2 = 'hello'; count(*) 3 -select count(*) from wl1972 group by col2 having col1 = 10; +select count(*) from t1 group by col2 having col1 = 10; ERROR 42S22: Unknown column 'col1' in 'having clause' -select col1 as count_col1 from wl1972 as t1 group by col1 having col1 = 10; +select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10; count_col1 10 -select col1 as count_col1 from wl1972 as t1 group by col1 having count_col1 = 10; +select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10; count_col1 10 -select col1 as count_col1 from wl1972 as t1 group by count_col1 having col1 = 10; +select col1 as count_col1 from t1 as tmp1 group by count_col1 having col1 = 10; count_col1 10 -select col1 as count_col1 from wl1972 as t1 group by count_col1 having count_col1 = 10; +select col1 as count_col1 from t1 as tmp1 group by count_col1 having count_col1 = 10; count_col1 10 -select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having col1 = 10; +select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col1 = 10; count_col1 col2 10 bob 10 bye 10 hello 10 sam -select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having count_col1 = 10; +select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10; count_col1 col2 10 bob 10 bye 10 hello 10 sam -select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having col2 = 'hello'; +select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello'; count_col1 col2 10 hello 20 hello 30 hello -select col1 as count_col1,col2 as group_col2 from wl1972 as t1 -group by col1,col2 having group_col2 = 'hello'; +select col1 as count_col1,col2 as group_col2 from t1 as tmp1 group by col1,col2 having group_col2 = 'hello'; count_col1 group_col2 10 hello 20 hello 30 hello -select sum(col1) as co12 from wl1972 group by col2 having col2 10; +select sum(col1) as co12 from t1 group by col2 having col2 10; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10' at line 1 -select sum(col1) as co2, count(col2) as cc from wl1972 group by col1 having col1 =10; +select sum(col1) as co2, count(col2) as cc from t1 group by col1 having col1 =10; co2 cc 40 4 -select t1.col1 from wl1972 as t1 where t1.col2 in -(select t2.col2 from wl1972 as t2 -group by t2.col1,t2.col2 having t2.col1<=10); +select t2.col2 from t2 group by t2.col1, t2.col2 having t1.col1 <= 10; +ERROR 42S22: Unknown column 't1.col1' in 'having clause' +select t1.col1 from t1 +where t1.col2 in +(select t2.col2 from t2 +group by t2.col1, t2.col2 having t2.col1 <= 10); col1 10 20 @@ -203,10 +205,12 @@ col1 10 10 10 -select t1.col1 from wl1972 as t1 where t1.col2 in -(select t2.col2 from wl1972 as t2 -group by t2.col1,t2.col2 having t2.col1<= -(select min(t3.col1) from wl1972 as t3)); +select t1.col1 from t1 +where t1.col2 in +(select t2.col2 from t2 +group by t2.col1, t2.col2 +having t2.col1 <= +(select min(t3.col1) from t3)); col1 10 20 @@ -214,36 +218,106 @@ col1 10 10 10 -drop table wl1972; -create table wl1972 (s1 int); -insert into wl1972 values (1),(2),(3); -select count(*) from wl1972 group by s1 having s1 is null; +select t1.col1 from t1 +where t1.col2 in +(select t2.col2 from t2 +group by t2.col1, t2.col2 having t1.col1 <= 10); +col1 +10 +10 +10 +10 +select t1.col1 as tmp_col from t1 +where t1.col2 in +(select t2.col2 from t2 +group by t2.col1, t2.col2 having tmp_col <= 10); +tmp_col +10 +10 +10 +10 +select t1.col1 from t1 +where t1.col2 in +(select t2.col2 from t2 +group by t2.col1, t2.col2 having col_t1 <= 10); +col1 +10 +10 +10 +10 +select t1.col1 from t1 +where t1.col2 in +(select t2.col2 from t2 +group by t2.col1, t2.col2 having col_t1 <= 10) +having col_t1 <= 20; +ERROR 42S22: Unknown column 'col_t1' in 'having clause' +select t1.col1 from t1 +where t1.col2 in +(select t2.col2 from t2 +group by t2.col1, t2.col2 having col_t1 <= 10) +group by col_t1 +having col_t1 <= 20; +col1 +10 +select col_t1, sum(col1) from t1 +group by col_t1 +having col_t1 > 10 and +exists (select sum(t2.col1) from t2 +group by t2.col2 having t2.col2 > 'b'); +col_t1 sum(col1) +20 20 +30 30 +select sum(col1) from t1 +group by col_t1 +having col_t1 in (select sum(t2.col1) from t2 +group by t2.col2, t2.col1 having t2.col1 = t1.col1); +ERROR 42S22: Unknown column 't1.col1' in 'having clause' +select sum(col1) from t1 +group by col_t1 +having col_t1 in (select sum(t2.col1) from t2 +group by t2.col2, t2.col1 having t2.col1 = col_t1); +sum(col1) +40 +20 +30 +select t1.col1, t2.col1 from t1, t2 where t1.col1 = t2.col1 +group by t1.col1, t2.col1 having col1 = 2; +ERROR 23000: Column 'col1' in having clause is ambiguous +select t1.col1*10+t2.col1 from t1,t2 where t1.col1=t2.col1 +group by t1.col1, t2.col1 having col1 = 2; +ERROR 23000: Column 'col1' in having clause is ambiguous +drop table t1, t2, t3; +create table t1 (s1 int); +insert into t1 values (1),(2),(3); +select count(*) from t1 group by s1 having s1 is null; count(*) -select s1*0 as s1 from wl1972 group by s1 having s1 <> 0; +select s1*0 as s1 from t1 group by s1 having s1 <> 0; s1 0 0 0 -select s1*0 from wl1972 group by s1 having s1 = 0; +Warnings: +Warning 1052 Column 's1' in having clause is ambiguous +select s1*0 from t1 group by s1 having s1 = 0; s1*0 -select s1 from wl1972 group by 1 having 1 = 0; +select s1 from t1 group by 1 having 1 = 0; s1 -select count(s1) from wl1972 group by s1 having count(1+1)=2; +select count(s1) from t1 group by s1 having count(1+1)=2; count(s1) -select count(s1) from wl1972 group by s1 having s1*0=0; +select count(s1) from t1 group by s1 having s1*0=0; count(s1) 1 1 1 -select * from wl1972 a, wl1972 b group by a.s1 having s1 is null; +select * from t1 a, t1 b group by a.s1 having s1 is null; ERROR 23000: Column 's1' in having clause is ambiguous -drop table wl1972; -create table wl1972 (s1 char character set latin1 collate latin1_german1_ci); -insert into wl1972 values ('ü'),('y'); +drop table t1; +create table t1 (s1 char character set latin1 collate latin1_german1_ci); +insert into t1 values ('ü'),('y'); Warnings: Warning 1265 Data truncated for column 's1' at row 1 -select s1,count(s1) from wl1972 +select s1,count(s1) from t1 group by s1 collate latin1_swedish_ci having s1 = 'y'; s1 count(s1) y 1 -drop table wl1972; +drop table t1; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 8d21f070956..d2a5ca4361e 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -2,7 +2,7 @@ # --disable_warnings -drop table if exists t1,t2; +drop table if exists t1,t2,t3; --enable_warnings create table t1 (a int); @@ -134,108 +134,180 @@ drop table t1,t2,t3; # set global sql_mode='ansi'; # set session sql_mode='ansi'; ---disable_warnings -drop table if exists wl1972; ---enable_warnings - -create table wl1972 ( - col1 int, - col2 varchar(5) character set latin1 collate latin1_bin) -engine=innodb; - -insert into wl1972 values(10,'hello'); -insert into wl1972 values(20,'hello'); -insert into wl1972 values(30,'hello'); -insert into wl1972 values(10,'bye'); -insert into wl1972 values(10,'sam'); -insert into wl1972 values(10,'bob'); +create table t1 (col1 int, col2 varchar(5), col_t1 int); +create table t2 (col1 int, col2 varchar(5), col_t2 int); +create table t3 (col1 int, col2 varchar(5), col_t3 int); -select count(*) from wl1972 group by col1 having col1 = 10; +insert into t1 values(10,'hello',10); +insert into t1 values(20,'hello',20); +insert into t1 values(30,'hello',30); +insert into t1 values(10,'bye',10); +insert into t1 values(10,'sam',10); +insert into t1 values(10,'bob',10); -select count(*) as count_col1 from wl1972 group by col1 having col1 = 10; - -select count(*) as count_col1 from wl1972 as t1 group by col1 having col1 = 10; - -select count(*) from wl1972 group by col2 having col2 = 'hello'; +insert into t2 select * from t1; +insert into t3 select * from t1; +select count(*) from t1 group by col1 having col1 = 10; +select count(*) as count_col1 from t1 group by col1 having col1 = 10; +select count(*) as count_col1 from t1 as tmp1 group by col1 having col1 = 10; +select count(*) from t1 group by col2 having col2 = 'hello'; --error 1054 -select count(*) from wl1972 group by col2 having col1 = 10; - -select col1 as count_col1 from wl1972 as t1 group by col1 having col1 = 10; - -select col1 as count_col1 from wl1972 as t1 group by col1 having count_col1 = 10; - -select col1 as count_col1 from wl1972 as t1 group by count_col1 having col1 = 10; +select count(*) from t1 group by col2 having col1 = 10; +select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10; +select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10; +select col1 as count_col1 from t1 as tmp1 group by count_col1 having col1 = 10; # ANSI: should return SQLSTATE 42000 Syntax error or access violation # MySQL: returns 10 - because of GROUP BY name resolution - -select col1 as count_col1 from wl1972 as t1 group by count_col1 having count_col1 = 10; +select col1 as count_col1 from t1 as tmp1 group by count_col1 having count_col1 = 10; # ANSI: should return SQLSTATE 42000 Syntax error or access violation # MySQL: returns 10 - because of GROUP BY name resolution +select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col1 = 10; +select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10; +select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello'; +select col1 as count_col1,col2 as group_col2 from t1 as tmp1 group by col1,col2 having group_col2 = 'hello'; +--error 1064 +select sum(col1) as co12 from t1 group by col2 having col2 10; +select sum(col1) as co2, count(col2) as cc from t1 group by col1 having col1 =10; +--error 1054 +select t2.col2 from t2 group by t2.col1, t2.col2 having t1.col1 <= 10; -select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having col1 = 10; -select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having count_col1 = 10; +# +# queries with nested sub-queries +# -select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having col2 = 'hello'; +# the having column is resolved in the same query +select t1.col1 from t1 +where t1.col2 in + (select t2.col2 from t2 + group by t2.col1, t2.col2 having t2.col1 <= 10); + +select t1.col1 from t1 +where t1.col2 in + (select t2.col2 from t2 + group by t2.col1, t2.col2 + having t2.col1 <= + (select min(t3.col1) from t3)); + +# the having column is resolved in the SELECT clause of the outer query - +# works in ANSI +select t1.col1 from t1 +where t1.col2 in + (select t2.col2 from t2 + group by t2.col1, t2.col2 having t1.col1 <= 10); + +# the having column is resolved in the SELECT clause of the outer query - +# error in ANSI, works with MySQL extension +select t1.col1 as tmp_col from t1 +where t1.col2 in + (select t2.col2 from t2 + group by t2.col1, t2.col2 having tmp_col <= 10); + +# the having column is resolved in the FROM clause of the outer query - +# works in ANSI +select t1.col1 from t1 +where t1.col2 in + (select t2.col2 from t2 + group by t2.col1, t2.col2 having col_t1 <= 10); + +# nested queries with HAVING, inner having column resolved in outer FROM clause +# the outer having column is not referenced in GROUP BY which results in an error +--error 1054 +select t1.col1 from t1 +where t1.col2 in + (select t2.col2 from t2 + group by t2.col1, t2.col2 having col_t1 <= 10) +having col_t1 <= 20; -select col1 as count_col1,col2 as group_col2 from wl1972 as t1 -group by col1,col2 having group_col2 = 'hello'; +# both having columns are resolved in the GROUP clause of the outer query +select t1.col1 from t1 +where t1.col2 in + (select t2.col2 from t2 + group by t2.col1, t2.col2 having col_t1 <= 10) +group by col_t1 +having col_t1 <= 20; ---error 1064 -select sum(col1) as co12 from wl1972 group by col2 having col2 10; -select sum(col1) as co2, count(col2) as cc from wl1972 group by col1 having col1 =10; +# +# nested HAVING clauses +# -select t1.col1 from wl1972 as t1 where t1.col2 in - (select t2.col2 from wl1972 as t2 - group by t2.col1,t2.col2 having t2.col1<=10); +# non-correlated subqueries +select col_t1, sum(col1) from t1 +group by col_t1 +having col_t1 > 10 and + exists (select sum(t2.col1) from t2 + group by t2.col2 having t2.col2 > 'b'); -select t1.col1 from wl1972 as t1 where t1.col2 in - (select t2.col2 from wl1972 as t2 - group by t2.col1,t2.col2 having t2.col1<= - (select min(t3.col1) from wl1972 as t3)); +# correlated subqueries - inner having column 't1.col2' resolves to +# the outer FROM clause, which cannot be used because the outer query +# is grouped +--error 1054 +select sum(col1) from t1 +group by col_t1 +having col_t1 in (select sum(t2.col1) from t2 + group by t2.col2, t2.col1 having t2.col1 = t1.col1); + +# correlated subqueries - inner having column 'col_t1' resolves to +# the outer GROUP clause +select sum(col1) from t1 +group by col_t1 +having col_t1 in (select sum(t2.col1) from t2 + group by t2.col2, t2.col1 having t2.col1 = col_t1); -drop table wl1972; + +# +# queries with joins and ambiguous column names +# +--error 1052 +select t1.col1, t2.col1 from t1, t2 where t1.col1 = t2.col1 +group by t1.col1, t2.col1 having col1 = 2; + +--error 1052 +select t1.col1*10+t2.col1 from t1,t2 where t1.col1=t2.col1 +group by t1.col1, t2.col1 having col1 = 2; + +drop table t1, t2, t3; # More queries to test ANSI compatibility -create table wl1972 (s1 int); -insert into wl1972 values (1),(2),(3); +create table t1 (s1 int); +insert into t1 values (1),(2),(3); -select count(*) from wl1972 group by s1 having s1 is null; +select count(*) from t1 group by s1 having s1 is null; -select s1*0 as s1 from wl1972 group by s1 having s1 <> 0; +select s1*0 as s1 from t1 group by s1 having s1 <> 0; # ANSI requires: 3 rows # MySQL returns: 0 rows - because of GROUP BY name resolution -select s1*0 from wl1972 group by s1 having s1 = 0; +select s1*0 from t1 group by s1 having s1 = 0; -select s1 from wl1972 group by 1 having 1 = 0; +select s1 from t1 group by 1 having 1 = 0; -select count(s1) from wl1972 group by s1 having count(1+1)=2; +select count(s1) from t1 group by s1 having count(1+1)=2; # ANSI requires: 3 rows # MySQL returns: 0 rows - because of GROUP BY name resolution -select count(s1) from wl1972 group by s1 having s1*0=0; +select count(s1) from t1 group by s1 having s1*0=0; -- error 1052 -select * from wl1972 a, wl1972 b group by a.s1 having s1 is null; +select * from t1 a, t1 b group by a.s1 having s1 is null; # ANSI requires: 0 rows # MySQL returns: # "ERROR 1052 (23000): Column 's1' in having clause is ambiguous" # I think the column is ambiguous in ANSI too. # It is the same as: -# select a.s1, b.s1 from wl1972 a, wl1972 b group by a.s1 having s1 is null; +# select a.s1, b.s1 from t1 a, t1 b group by a.s1 having s1 is null; # currently we first check SELECT, thus s1 is ambiguous. -drop table wl1972; +drop table t1; -create table wl1972 (s1 char character set latin1 collate latin1_german1_ci); -insert into wl1972 values ('ü'),('y'); +create table t1 (s1 char character set latin1 collate latin1_german1_ci); +insert into t1 values ('ü'),('y'); -select s1,count(s1) from wl1972 +select s1,count(s1) from t1 group by s1 collate latin1_swedish_ci having s1 = 'y'; # ANSI requires: 1 row, with count(s1) = 2 # MySQL returns: 1 row, with count(s1) = 1 -drop table wl1972; +drop table t1; |