MySQL Bugs: #24083: GROUP BY expressions should be allowed in the HAVING clause

Bug #24083	GROUP BY expressions should be allowed in the HAVING clause
Submitted:	8 Nov 2006 11:02	Modified:	2 Oct 2008 14:07
Reporter:	Mick Francis (Candidate Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.29-BK, 5.0.26-community	OS:	Linux (Linux, WinXP)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	qc

Description:
Any of the GROUP BY expressions ought to be allowed in the HAVING clause, but this is not the case. Using a column alias gets around the problem, but shouldn't be necessary (and requires special case coding in a DBMS-independent environment).

How to repeat:
--Create the table
drop table if exists T;
create table T (a int, b int);

-- Shouldn't this work? Gives the following error:
--      ERROR 1054 (42S22): Unknown column 'a' in 'having clause'
select a + b from T group by a + b having a + b > 2;

-- This one does (symantically identical?)
select a + b as s from T group by a + b having s > 2;

Thank you for a bug report. Verified just as described with 5.0.29-BK on Linux:

openxs@suse:~/dbs/5.0>bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.29-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists T;
Query OK, 0 rows affected, 1 warning (0.00 sec)

cmysql> create table T (a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> select a + b from T group by a + b having a + b > 2;
ERROR 1054 (42S22): Unknown column 'a' in 'having clause'
mysql> select a + b AS c from T group by a + b having c > 2;
Empty set (0.00 sec)

mysql> select a + b AS c from T group by c having c > 2;
Empty set (0.00 sec)

Although one may say (see http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html) that using expressions in HAVING clause is against SQL standard, but as we support it in GROUP BY, we have to support it in HAVING, for consistency.

Thank you for a reasonable feature request.