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:
None 
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
Description:
This bug is related to the bug: http://bugs.mysql.com/bug.php?id=27229

--disable_warnings
drop table if exists t1, t2;
--enable_warnings

create table t1 (a int, b int);
create table t2 (m int, n int);
insert into t1 values (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
insert into t2 values (1,11), (2,22), (3,32), (4,44), (4,44);
set @@sql_mode='TRADITIONAL';

# Querie returning, but Wrong result
select count(*), a,
 ( select m  from t2 where m = count(*) )
  from t1 group by a;

How to repeat:
see description

Suggested fix:
-
[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.