MySQL Bugs: #4405: Strange behaviour on HAVING clause

Bug #4405	Strange behaviour on HAVING clause
Submitted:	5 Jul 2004 4:24	Modified:	5 Jul 2004 10:09
Reporter:	Quentin Ochem	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.2-alpha	OS:	Windows (windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
As far as I understand SQL, having clauses should performs test on the data already selected. Then, if I do a request like this : 

SELECT * FROM a
HAVING Val = MAX(Val);

I should get at least one result (except if there are no data in a), the entry where Val is the max. But I don't get anything !

How to repeat:
Do this request :

SELECT * FROM a
HAVING Val = MAX(Val);

On a database like this one

CREATE TABLE `a` (
  `Key` int(11) NOT NULL default '0',
  `Val` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Contenu de la table `a`
#

INSERT INTO `a` VALUES (1, 89);
INSERT INTO `a` VALUES (2, 125);

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

You a interpretattion of HAVING clause is correct, but you are using agrregate function in wrong way.

"I should get at least one result ... where Val is the max" 
Not at all.

MAX() can't be used such way simply because HAVING does comparison row by row (like WHERE clause) and MAX() is undefined before the whole table is considered.

Without GROUP BY clause HAVING is same as WHERE with the only difference that HAVING can only reference columns in result set. 

So in a way 
SELECT * FROM a HAVING <expr>;
is equivalent to
SELECT * FROM a WHERE <expr>;

But if you try:

SELECT * FROM a WHERE MAX(Val) = Val;
MySQL will report:
ERROR 1111 (HY000): Invalid use of group function

However using aggregation functions is allowed in HAVING clause when there is GROUP BY like in:

SELECT `Key`, MAX(Val) FROM a group by `key` HAVING MAX(Val) > 80;

What you try to achieve is doable with subquery:

SELECT * FROM a 
  WHERE Val = (SELECT MAX(Val) FROM a);

+-----+-----+
| Key | Val |
+-----+-----+
|   2 | 125 |
+-----+-----+