MySQL Bugs: #8038: unexpected error (correlated subquery in HAVING can't refer to sum() result)

Bug #8038	unexpected error (correlated subquery in HAVING can't refer to sum() result)
Submitted:	20 Jan 2005 10:48	Modified:	10 Mar 2005 20:55
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.9	OS:
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
the documentation doesn't specify anything about corre;ated subqueries refering to results of aggregate functions. 
Naturaly those queries return an error, if the result of the aggregate is not know, (e.g. if the subquery is in the ungrouped (select/where) part of the outer query)

However the reference should (and could) work for subqueries in the HAVING part of the outer query.

How to repeat:
select a, sum(a) s from (select 1 a) t group by a having s=1;
+---+------+
| a | s    |
+---+------+
| 1 |    1 |
+---+------+

select a, sum(a) s from (select 1 a) t group by a having (select s)=1;
ERROR 1247 (42S22): Reference 's' not supported (reference on group function)

Suggested fix:
If this is intentionaly not supported, it should be documented. As there seems to be no logical restriction why this should not work.
Otherwise mysqlshould resove the reference

Thanks for the bug report. 
According to the SQL standard, the specified query is not valid. I would agree that it is "in the spirit" of MySQL extensions.

There are similar queries that should be valid in standard SQL but are invalid in MySQL, e.g.
select sum(b) from t10 group by a having 1 = (select b from t11 where t11.a = sum(t10.b));

We've decided to make no changes the server functionality for now. I'm passing this to documentation team to update the manual as they see appropriate.

I have added a note about this restriction to the
section on correlated subqueries.