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:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.1.9 OS:
Assigned to: Sergey Petrunya CPU Architecture:Any

[20 Jan 2005 10:48] Martin Friebe
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
[2 Feb 2005 6:42] Sergey Petrunya
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.
[10 Mar 2005 20:55] Paul DuBois
I have added a note about this restriction to the
section on correlated subqueries.