Bug #27348 aggregate in subquery executed, even if position in qualifying query is invalid
Submitted: 21 Mar 2007 17:37 Modified: 11 May 2007 19:28
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.40 OS:Any (*)
Assigned to: Igor Babaev
Tags: aggregate, subquery

[21 Mar 2007 17:37] Martin Friebe
Description:
from sql:draft2003 (section 6.9)

7) SFS shall be contained in the <having clause>, <window clause>, or <select 
list> of its aggregation query. 

In the following query, the aggregation query for "count(b)" is the outer query ("from t1")

 select a from t1 where (select count(b)) >0 group by a;

"count(b)" therefore appears in the "where" clause of its aggregation query. Yet the query is executed.

How to repeat:
drop table if exists t1,t2;
create table t1 (a int, b int);
insert into t1 values (1,11), (2,22), (2,22);

select a from t1 where (select count(b)) >0 group by a;
select a from t1 where (select count(b)) >1 group by a;

Suggested fix:
-
[21 Mar 2007 18:15] Miguel Solorzano
Thank you for the bug report. Verified as described.
[21 Mar 2007 21:56] Martin Friebe
I have come across some doc in the code (after I reported this).

It may be that this is an intended exception from the SQL2003.
please see http://lists.mysql.com/internals/34435

If this is intended, and intended to be kept like this, then this bug can be closed as "not a bug"
[27 Mar 2007 16:48] 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/23050

ChangeSet@1.2421, 2007-03-27 09:48:10-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #27348.
  If a set function with a outer reference s(outer_ref) cannot be aggregated 
  the outer query against which the reference has been resolved then MySQL
  interpretes s(outer_ref) in the same way as it would interpret s(const).
  Hovever the standard requires throwing an error in this situation.
  Added some code to support this requirement in ansi mode.
  Corrected another minor bug in Item_sum::check_sum_func.
[31 Mar 2007 8:39] Bugs System
Pushed into 5.1.18-beta
[31 Mar 2007 8:44] Bugs System
Pushed into 5.0.40
[11 May 2007 19:28] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

If a set function S with an outer reference S(outer_ref) cannot be
aggregated in the outer query against which the outer reference has
been resolved, MySQL interprets S(outer_ref) the same way that it
would interpret S(const). However, standard SQL requires throwing an
error in this situation. An error now is thrown for such queries if
the ANSI SQL mode is enabled. 

Also updated description of ANSI mode.