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:
None 
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
Description:
mysql allows subqueries to contain aggregates on fields of the outer query, which are evaluated in the context of the outer query:

select
 a,
 (select count(b) from t2 where t2.primary  t1.primary)
 from t1 group by a

is the same as 
select a, count(b) from t1 group by a

This is correct according to sql2003 draft:
( from 6.9 <set function specification> )
--quote
6) The aggregation query of a <set function specification> SFS is determined as follows. Case:
a) If SFS has no aggregated column reference, then the aggregation query of SFS is the innermost <query
specification> that contains SFS.
b) Otherwise, the innermost qualifying query of the aggregated column references of SFS is the aggregation
query of SFS. 
--quote end

At least it is my understanding of 6b, and it is what mysql does.

How to repeat:
look through the documentation this is not documented.

Au contraire:
http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html says:
> Correlated subqueries cannot refer to the results of aggregate functions from the outer query.

This is correct, they cannot "refer", but they can "contain".

Suggested fix:
add to docs.

I tread this as a bug, not feature request, because currently the documentation explains that individual columns can be resolved from the outer query.

This explains a different behaviour (which can be seen in mysql 4.1):
select  a,
 (select count(b) from t2 where t2.primary  t1.primary)
 from t1 group by a

The current doc resolve b as a value from the outer query, and count how often it occurs in t2.
[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
-