Bug #27257 | incorrect result in subquery with aggregate(outer_column) | ||
---|---|---|---|
Submitted: | 19 Mar 2007 12:06 | Modified: | 11 Apr 2007 1:59 |
Reporter: | Miguel Solorzano | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.1BK | OS: | Linux (Linux) |
Assigned to: | Igor Babaev | CPU Architecture: | Any |
[19 Mar 2007 12:06]
Miguel Solorzano
[20 Mar 2007 10:51]
Martin Friebe
Maybe worth checking what the bug is? 1) the invalid result 2) accepting "count(*) in this position The behaviour is not (yet) documented, so I can not tell the behaviour intended. But I had a look at a draft of sql:2003, and believe this query may not be valid (see also my post at http://lists.mysql.com/internals/34406 ) Unless I have overlooked additional info this is the relevant part: > 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. There is no column reference, so "6a" should apply. In which case "count(*)" can not be standing within "where"
[20 Mar 2007 18:51]
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/22409 ChangeSet@1.2487, 2007-03-20 11:51:09-07:00, igor@olga.mysql.com +3 -0 Fixed bug #27257: queries containing subqueries with COUNT(*) aggregated in outer context returned wrong results. This happened only if the subquery did not contain any references to outer fields. As there were no references to outer fields the subquery erroneously was taken for non-correlated one. Now any set function aggregated in outer context makes the subquery correlated.
[23 Mar 2007 13:59]
Alexey Botchkov
Pushed in 5.0.40 and 5.1.18
[11 Apr 2007 1:59]
Paul DuBois
Noted in 5.0.40, 5.1.18 changelogs. Queries containing subqueries with COUNT(*) aggregated in an outer context returned incorrect results. This happened only if the subquery did not contain any references to outer columns.