MySQL Bugs: #27257: incorrect result in subquery with aggregate(outer

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

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:
-

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"

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.

Pushed in 5.0.40 and 5.1.18

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.