Bug #59839 Aggregation followed by subquery yields wrong result
Submitted: 31 Jan 2011 11:15 Modified: 28 Feb 2011 20:42
Reporter: Martin Hansson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: Martin Hansson CPU Architecture:Any

[31 Jan 2011 11:15] Martin Hansson 
Description:
Aggregation is almost always used as the last step in query execution.  With scalar subqueries however, a subquery is sometimes performed using an aggregated value as correlated search argument.

Observed in all versions.

How to repeat:
Run attached test case and compare with - also attached - expected result.

Suggested fix:
As can be observed in the EXPLAIN output, the subquery that delivers the correct result is using the value delivered from the group prefix as computed during aggregation whereas the failing subquery uses the field t1.a directly. Since aggregation on t1 is already performed, the actual output from the aggregated select node (i.e. t1) may already have read a value outside the group. The latter depending on access method on t1. 

Possibly a direct field value should not be used at all after aggregation. In this case the EXPLAIN output is also wrong, obviously.
[31 Jan 2011 11:18] Martin Hansson 
Test Case

Attachment: bug.test (, text), 643 bytes.
[31 Jan 2011 11:19] Martin Hansson 
Expected Result

Attachment: bug.result (, text), 895 bytes.
[9 Feb 2011 9:58] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/130814

3587 Martin Hansson	2011-02-09
      Bug#59839: Aggregation followed by subquery yields wrong result
      
      The loop that was looping over subqueries' references to outer field used a
      local boolean variable to tell whether the field was grouped or not. But the
      implementor failed to reset the variable after each iteration. Thus a field
      that was not directly aggregated appeared to be.
      
      Fixed by resetting the variable upon each new iteration.
[28 Feb 2011 20:42] Paul DuBois 
Noted in 5.1.57, 5.5.11, 5.6.2 changelogs.

Aggregation followed by a subquery could produce an incorrect result.

CHANGESET - http://lists.mysql.com/commits/131611