MySQL Bugs: #27348: aggregate in subquery executed, even if position in qualifying query is invalid

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	CPU Architecture:	Any
Tags:	aggregate, subquery

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

Thank you for the bug report. Verified as described.

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"

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.

Pushed into 5.1.18-beta

Pushed into 5.0.40

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.