Bug #27278 | aggregate of outer query for subquery not documented | ||
---|---|---|---|
Submitted: | 19 Mar 2007 23:47 | Modified: | 20 Mar 2007 17:57 |
Reporter: | Martin Friebe (Gold Quality Contributor) (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.0 | OS: | Any (*) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | aggregate, documentation, subquery |
[19 Mar 2007 23:47]
Martin Friebe
[20 Mar 2007 16:20]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Replacing current paragraph with: Aggregate functions in correlated subqueries may contain outer references, provided the function contains nothing but outer references, and provided the function is not contained in another function or expression.
[20 Mar 2007 16:44]
Martin Friebe
> and provided the function is not contained in another function or expression. currently it can be contained in another function select a, (select sum( (count(t1.b) ) from t2 ) from t1 group by a equals select a, count(b)* (select count(*) from t2) from t1 group by a Not sure if that is intended... see also #27229
[20 Mar 2007 17:04]
Martin Friebe
My proposed text would be: Aggregate functions in correlated subqueries that contain outer references and no references to the inner query will be evaluated in the context of the outer query. If such an aggregate function contains references to more than one outer query, it will be executed in the context of the most inner referenced query. [ optional, to be checked against sql:2003 ] The result of such an aggregate is not treated as a reference to the outer table. If the result of the "outer aggregate" occurs wrapped in an aggregate, then the wrapping aggregate is evaluated in the subquery, aggregating the result of the outer aggregate. [ optional ] Example: select t1.a, (select t2.b from t2 where t2.b = count(t1.a)) from t1 group by a; "count(t1.a)" occurs in the subquery, but it will be evaluated, as if it was in the select list part of the outer query.
[20 Mar 2007 17:57]
Martin Friebe
-