MySQL Bugs: #27363: nested aggregates in outer, subquery / sum(select count(outer))

Bug #27363	nested aggregates in outer, subquery / sum(select count(outer))
Submitted:	21 Mar 2007 22:25	Modified:	8 May 2007 1:34
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, maybe 4.1	OS:	Any (*)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	aggregate, qc, subquery

Description:
Look at the sql below. The result is incorrect.

However the expectation depends on the outcome of Bug #27348 and http://lists.mysql.com/internals/34435.

1)
If it is according to my understanding of the sql2003 then the "count(a)" should require the outer query as aggregation query.
This would be imbossible (nesting of aggregates) and an error should be returned.

2)
If it is according to the code-embedded docs, then "count(a)" keeps the inner query as aggregation query:
This would mean "select count(b) from DUAL" imports the value of b, for each execution. Since b is always "1", the subquery always is "select count(1) from DUAL", which always returns "1"

The outer query then would be 
 select sum(1) from t1

and should return the amount of rows in t1. It does not.

---

In any case, the very last query can not be correct, it contains 3 nested aggregates for 2 queries. There is no way to distribute them in any meaningful way.
But again the expected behaviour depends on the above.

How to repeat:
drop table if exists t1;
create table t1 (a int);

insert into t1 select 1;
select sum( (select count(a) from DUAL) ) from t1;
+------------------------------------+
| sum( (select count(a) from DUAL) ) |
+------------------------------------+
| 1                                  |
+------------------------------------+
insert into t1 select 1;
select sum( (select count(a) from DUAL) ) from t1;
+------------------------------------+
| sum( (select count(a) from DUAL) ) |
+------------------------------------+
| 3                                  |
+------------------------------------+

insert into t1 select 1;
select sum( (select count(a) from DUAL) ) from t1;
+------------------------------------+
| sum( (select count(a) from DUAL) ) |
+------------------------------------+
| 6                                  |
+------------------------------------+

insert into t1 select 1;
select sum( (select count(a) from DUAL) ) from t1;
+------------------------------------+
| sum( (select count(a) from DUAL) ) |
+------------------------------------+
| 10                                  |
+------------------------------------+

# for case 2
select count(1) from DUAL;
+----------+
| count(1) |
+----------+
|        1 |
+----------+

# should always give an error
select sum( (select sum(count(a)) from DUAL) ) from t1;
+-----------------------------------------+
| sum( (select sum(count(a)) from DUAL) ) |
+-----------------------------------------+
| 10                                      |
+-----------------------------------------+
1 row in set (0.02 sec)

Suggested fix:
-

Thank you for a bug report. Verified just as described with latest 5.0.40-BK.

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/23642

ChangeSet@1.2424, 2007-04-03 13:32:22+03:00, gkodinov@magare.gmz +4 -0
  Bug #27363: 
  To correctly decide where to aggregate or can it 
  aggregate at all an aggregate function the optimizer 
  must know the maximum subquery nesting level of the 
  field references and the aggregation level of the
  aggregate functions quoted in the arguments of the
  enclosing aggregate function.
  The optimizer collects that information when resolving
  outer field references and aggregate functions.
  However it was not collection the information from
  a subquery inside aggregation function arguments, e.g:
  SELECT SUM(1 + (SELECT COUNT(t1.a) FROM DUAL)) FROM t1.
  Fixed by adjusting the condition for collecting the 
  maximum aggregation levels of column references and
  nested aggregate functions to work correctly across 
  subquery boundaries.

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/24671

ChangeSet@1.2456, 2007-04-17 17:44:55+03:00, gkodinov@magare.gmz +4 -0
  Bug #27363: 
  To correctly decide where to aggregate or can it 
  aggregate at all an aggregate function the optimizer 
  must know the maximum subquery nesting level of the 
  field references and the aggregation level of the
  aggregate functions quoted in the arguments of the
  enclosing aggregate function.
  The optimizer collects that information when resolving
  outer field references and aggregate functions.
  However it was not collection the information from
  a subquery inside aggregation function arguments, e.g:
  SELECT SUM(1 + (SELECT COUNT(t1.a) FROM DUAL)) FROM t1.
  Fixed by adjusting the condition for collecting the 
  maximum aggregation levels of column references and
  nested aggregate functions to work correctly across 
  subquery boundaries.

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/25259

ChangeSet@1.2424, 2007-04-24 15:14:18+03:00, gkodinov@magare.gmz +4 -0
  Bug #27363: 
  To correctly decide where to aggregate or can it 
  aggregate at all an aggregate function the optimizer 
  must know the maximum subquery nesting level of the 
  field references and the aggregation level of the
  aggregate functions quoted in the arguments of the
  enclosing aggregate function.
  The optimizer collects that information when resolving
  outer field references and aggregate functions.
  However it was not collecting the information from
  a subquery inside aggregation function arguments, e.g:
  SELECT SUM(1 + (SELECT COUNT(t1.a) FROM DUAL)) FROM t1.
  Fixed by adjusting the condition for collecting the 
  maximum aggregation levels of column references and
  nested aggregate functions to work correctly across 
  subquery boundaries.

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/25261

ChangeSet@1.2424, 2007-04-24 15:30:01+03:00, gkodinov@magare.gmz +4 -0
  Bug #27363: 
  To correctly decide where to aggregate or can it 
  aggregate at all an aggregate function the optimizer 
  must know the maximum subquery nesting level of the 
  field references and the aggregation level of the
  aggregate functions quoted in the arguments of the
  enclosing aggregate function.
  The optimizer collects that information when resolving
  outer field references and aggregate functions.
  However it was not collecting the information from
  a subquery inside aggregation function arguments, e.g:
  SELECT SUM(1 + (SELECT COUNT(t1.a) FROM DUAL)) FROM t1.
  Fixed by adjusting the condition for collecting the 
  maximum aggregation levels of column references and
  nested aggregate functions to work correctly across 
  subquery boundaries.

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/25382

ChangeSet@1.2424, 2007-04-25 13:13:10+03:00, gkodinov@magare.gmz +4 -0
  Bug #27363:
  Validity checks for nested set functions
  were not taking into account that the enclosed
  set function may be on a nest level that is
  lower than the nest level of the enclosing set
  function.
  Fixed by :
   - propagating max_sum_func_level
  up the enclosing set functions chain.
   - updating the max_sum_func_level of the 
     enclosing set function when the enclosed set
     function is aggregated above or on the same
     nesting level of as the level of the enclosing 
     set function.
   - updating the max_arg_level of the enclosing
     set function on a reference that refers to
     an item above or on the same nesting level
     as the level of the enclosing set function.
   - Treating both Item_field and Item_ref as possibly
     referencing items from outer nesting levels.

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/25479

ChangeSet@1.2424, 2007-04-26 10:46:13+03:00, gkodinov@magare.gmz +4 -0
  Bug #27363:
  Validity checks for nested set functions
  were not taking into account that the enclosed
  set function may be on a nest level that is
  lower than the nest level of the enclosing set
  function.
  Fixed by :
   - propagating max_sum_func_level
  up the enclosing set functions chain.
   - updating the max_sum_func_level of the 
     enclosing set function when the enclosed set
     function is aggregated above or on the same
     nest level of as the level of the enclosing 
     set function.
   - updating the max_arg_level of the enclosing
     set function on a reference that refers to
     an item above or on the same nest level
     as the level of the enclosing set function.
   - Treating both Item_field and Item_ref as possibly
     referencing items from outer nest levels.

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/25484

ChangeSet@1.2424, 2007-04-26 11:12:17+03:00, gkodinov@magare.gmz +4 -0
  Bug #27363:
  Validity checks for nested set functions
  were not taking into account that the enclosed
  set function may be on a nest level that is
  lower than the nest level of the enclosing set
  function.
  Fixed by :
   - propagating max_sum_func_level
  up the enclosing set functions chain.
   - updating the max_sum_func_level of the 
     enclosing set function when the enclosed set
     function is aggregated above or on the same
     nest level of as the level of the enclosing 
     set function.
   - updating the max_arg_level of the enclosing
     set function on a reference that refers to
     an item above or on the same nest level
     as the level of the enclosing set function.
   - Treating both Item_field and Item_ref as possibly
     referencing items from outer nest levels.

Pushed into 5.0.42

Pushed into 5.1.18-beta

Noted in 5.0.42, 5.1.18 changelogs.

Nested aggregate functions could be improperly evaluated.