# test of problems with having (Reported by Mark Rogers) # --disable_warnings drop table if exists t1,t2,t3; --enable_warnings create table t1 (a int); select count(a) as b from t1 where a=0 having b > 0; insert into t1 values (null); select count(a) as b from t1 where a=0 having b > 0; select count(a) as b from t1 where a=0 having b >=0; explain extended select count(a) as b from t1 where a=0 having b >=0; drop table t1; # # Test of problem with HAVING and AVG() # CREATE TABLE t1 ( raw_id int(10) NOT NULL default '0', chr_start int(10) NOT NULL default '0', chr_end int(10) NOT NULL default '0', raw_start int(10) NOT NULL default '0', raw_end int(10) NOT NULL default '0', raw_ori int(2) NOT NULL default '0' ); INSERT INTO t1 VALUES (469713,1,164123,1,164123,1),(317330,164124,317193,101,153170,1),(469434,317194,375620,101,58527,1),(591816,375621,484273,1,108653,1),(591807,484274,534671,91,50488,1),(318885,534672,649362,101,114791,1),(318728,649363,775520,102,126259,1),(336829,775521,813997,101,38577,1),(317740,813998,953227,101,139330,1),(1,813998,953227,101,139330,1); CREATE TABLE t2 ( id int(10) unsigned NOT NULL default '0', contig_id int(10) unsigned NOT NULL default '0', seq_start int(10) NOT NULL default '0', seq_end int(10) NOT NULL default '0', strand tinyint(2) NOT NULL default '0', KEY id (id) ); INSERT INTO t2 VALUES (133195,469713,61327,61384,1),(133196,469713,64113,64387,1),(133197,1,1,1,0),(133197,1,1,1,-2); SELECT e.id, MIN( IF(sgp.raw_ori=1, (e.seq_start+sgp.chr_start-sgp.raw_start), (sgp.chr_start+sgp.raw_end-e.seq_end))) as start, MAX( IF(sgp.raw_ori=1, (e.seq_end+sgp.chr_start-sgp.raw_start), (sgp.chr_start+sgp.raw_end-e.seq_start))) as end, AVG(IF (sgp.raw_ori=1,e.strand,(-e.strand))) as chr_strand FROM t1 sgp, t2 e WHERE sgp.raw_id=e.contig_id GROUP BY e.id HAVING chr_strand= -1 and end >= 0 AND start <= 999660; drop table t1,t2; # # Test problem with having and MAX() IS NOT NULL # CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null; select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null; select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null; select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null; drop table t1; # # Test problem with count(distinct) in having # create table t1 (id int not null, qty int not null); insert into t1 values (1,2),(1,3),(2,4),(2,5); select id, sum(qty) as sqty from t1 group by id having sqty>2; select sum(qty) as sqty from t1 group by id having count(id) > 0; select sum(qty) as sqty from t1 group by id having count(distinct id) > 0; drop table t1; # # Test case for Bug #4358 Problem with HAVING clause that uses alias from the # select list and TEXT field # CREATE TABLE t1 ( `id` bigint(20) NOT NULL default '0', `description` text ) ENGINE=MyISAM; CREATE TABLE t2 ( `id` bigint(20) NOT NULL default '0', `description` varchar(20) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (1, 'test'); INSERT INTO t2 VALUES (1, 'test'); CREATE TABLE t3 ( `id` bigint(20) NOT NULL default '0', `order_id` bigint(20) NOT NULL default '0' ) ENGINE=MyISAM; select a.id, a.description, count(b.id) as c from t1 a left join t3 b on a.id=b.order_id group by a.id, a.description having (a.description is not null) and (c=0); select a.*, count(b.id) as c from t2 a left join t3 b on a.id=b.order_id group by a.id, a.description having (a.description is not null) and (c=0); INSERT INTO t1 VALUES (2, 'test2'); select a.id, a.description, count(b.id) as c from t1 a left join t3 b on a.id=b.order_id group by a.id, a.description having (a.description is not null) and (c=0); drop table t1,t2,t3; # # Bug #14274: HAVING clause containing only set function # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (3), (4), (1), (3), (1); SELECT SUM(a) FROM t1 GROUP BY a HAVING SUM(a)>0; SELECT SUM(a) FROM t1 GROUP BY a HAVING SUM(a); DROP TABLE t1; # # Bug #14927: HAVING clause containing constant false conjunct # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1), (2), (1), (3), (2), (1); SELECT a FROM t1 GROUP BY a HAVING a > 1; SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1; SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1; EXPLAIN SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1; EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1; DROP table t1; # End of 4.1 tests # # Tests for WL#1972 CORRECT EVALUATION OF COLUMN REFERENCES IN THE HAVING CLAUSE # Per the SAP VERI tests and WL#1972, MySQL must ensure that HAVING can # correctly evaluate column references from the GROUP BY clause, even if the # same references are not also found in the select list. # # set global sql_mode='ansi'; # set session sql_mode='ansi'; 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; 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 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 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; # # queries with nested sub-queries # # 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); # Item_field must be resolved in the same way as Item_ref select sum(col1) from t1 group by col_t1 having (select col_t1 from t2 where col_t1 = col_t2 order by col_t2 limit 1); # 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; # 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; # # nested HAVING clauses # # 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'); # 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); # # 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 t1 (s1 int); insert into t1 values (1),(2),(3); select count(*) from t1 group by s1 having s1 is null; # prepared statements prints warnings too early --disable_ps_protocol select s1*0 as s1 from t1 group by s1 having s1 <> 0; --enable_ps_protocol # ANSI requires: 3 rows # MySQL returns: 0 rows - because of GROUP BY name resolution select s1*0 from t1 group by s1 having s1 = 0; select s1 from t1 group by 1 having 1 = 0; 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 t1 group by s1 having s1*0=0; -- error 1052 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 t1 a, t1 b group by a.s1 having s1 is null; # currently we first check SELECT, thus s1 is ambiguous. drop table t1; create table t1 (s1 char character set latin1 collate latin1_german1_ci); insert into t1 values ('ΓΌ'),('y'); 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 t1; # # Bug #15917: unexpected complain for a name in having clause # when the server is run on Windows or with --lower-case-table-names=1 # DROP SCHEMA IF EXISTS HU; CREATE SCHEMA HU ; USE HU ; CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL UNIQUE, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL UNIQUE, PNAME CHAR(20), PTYPE CHAR(6), BUDGET DECIMAL(9), CITY CHAR(15)); INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale'); INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna'); INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna'); INSERT INTO STAFF VALUES ('E4','Don',12,'Deale'); INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron'); INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale'); INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna'); INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa'); INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale'); INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna'); INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale'); SELECT EMPNUM, GRADE*1000 FROM HU.STAFF WHERE GRADE * 1000 > ANY (SELECT SUM(BUDGET) FROM HU.PROJ GROUP BY CITY, PTYPE HAVING HU.PROJ.CITY = HU.STAFF.CITY); DROP SCHEMA HU;