From: Date: March 19 2007 2:13pm
Subject: aggregate of outer query in subquery "count()"
List-Archive: http://lists.mysql.com/internals/34406
Message-Id: <45FE8C78.5060608@hybyte.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

not sure, if this is the right list to ask the question...

After looking at http://bugs.mysql.com/bug.php?id=27257 and 
http://bugs.mysql.com/bug.php?id=27229, I assume that in

 select  t1.c1,  ( select min(t2.c1) from t2 where t2.c2 = count(t1.c2) 
) from t1 group by t1.c1;

count(t1.c2)  in the subquery is the count of t1.c2 in table t1. Meaning 
the whole aggregate function is evaluated in the scope of t1. (rather 
than a count of just the value t1.c1 inside the scope of t2)

I looked through a draft of the sql2003 spec
 > 6.9 <set function specification>
 >  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.

This appears to describe the above scenario: the "innermost qualifying query" for t1.c2 is the outer query.

*** now my question ***
http://bugs.mysql.com/bug.php?id=27229

 select count(*), a, ( select m  from t2 where m = count(*) ) from t1 group by a;

"count(*)" inside the subquery, is evaluated as count(*) for the outer query.

However count(*) (in the subquery where) has however no column reference to the outer query?

Having "count(*) referring to the outer query, depending on its position in the subquery seems quite confusing?
This would mean "count(*)" is not allowed in "where" or "group by" of the subquery, and would therefore be interpreted as "count(*)" of the outer query? (which doenst currently happen for "group by")