MySQL Bugs: #23417: (ONLY_FULL_GROUP_BY) function of aggregate is still an aggregate [S3/S2]

Bug #23417	(ONLY_FULL_GROUP_BY) function of aggregate is still an aggregate [S3/S2]
Submitted:	18 Oct 2006 10:04	Modified:	13 Feb 2007 19:03
Reporter:	Maciej Pilichowski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.27-BK, 5.0.24a	OS:	Linux (Linux, opensuse 10.0)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Severity is S3/S2 -- it depends how one define workaround. For serious databasing relaxing SQL_MODE is unacceptable so there is no workaround --> S2. For some other taks, it is, so there is workaround --> S3.

Consider:
SELECT MAX(A),MIN(A) FROM T GROUP BY X;
vs.
SELECT MAX(A)-MIN(A) FROM T GROUP BY X;

Consider:
SELECT MIN(SQRT(A)) FROM T GROUP BY X;
vs.
SELECT SQRT(MIN(A)) FROM T GROUP BY X;

In both scenarios in the second line MySQL refuses to run those queries stating that selected column is not grouped (true). They should be run without any problems, because no matter what you do with aggregate it is an aggregate.

Even with more elaborate example:
SELECT CASE WHEN AVG(A)>=0 THEN 'Positive' ELSE 'Negative' END FROM T GROUP BY X;

It is still correct even there is use of "conditional" processing.

How to repeat:
Just run those queries.

Suggested fix:
Check if the user uses non-aggregated column or not-grouped column, not only the latter because it is too narrow.

What SQL_MODE do you use when you have problems? For me all your queries works OK with SQL_MODE=ANSI, for example (in 5.0.27-BK).

Sorry, it really does not work with sql_mode=ONLY_FULL_GROUP_BY. Verified just as described with 5.0.27-BK on Linux. I was surprised that ANSI does not include this mode.

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

ChangeSet@1.2316, 2006-11-30 16:59:25+02:00, gkodinov@macbook.gmz +14 -0
  Bug #23417: (ONLY_FULL_GROUP_BY) function of aggregate is still an aggregate
              [S3/S2]
  The SQL standard compliant validity check of SELECT list and HAVING clause
  against the GROUP BY expressions was not done consistently.
  This check is performed only in ONLY_FULL_GROUP_BY mode.
  Implemented a comprehensive check. The new check works correctly with 
  subqueries and expressions in GROUP BY.
   
  The check for ONLY_FULL_GROUP_BY works as a recursive algorithm starting 
  at each subquery level with a GROUP BY. It is done for the select list
  and the HAVING clause.
  At each query element : expression or otherwise it checks :
   - if the element is quoted in the current GROUP BY list. When it is it 
     stops right there and declares this query element valid. 
     See Item::ref_group_by_validity_analyzer
   - if the element is a subquery. When it is it marks the fact that it's
     inside a subquery (by pushing to the subqueries element of 
     ref_group_by_validity_analyzer_arg), descends through the subquery
     elements and then pops up the mark. 
     See Item_subselect::ref_group_by_validity_analyzer
   - if the element has sub-elements (function args, etc.), it descends 
     through them. 
     See Item_func::ref_group_by_validity_analyzer, 
     Item_cond::ref_group_by_validity_analyzer, etc.
   - If the current query element is an identifier (Item_field or Item_ref)
     if it is valid (taking into account subquery nesting level and the 
     GROUP BY list) and decides whether to follow the Item_ref or not.
     See Item_ident::ref_group_by_validity_analyzer.
   - If the current query element is a column reference (Item_field)
     and it's not valid reference (as checked above) an error is thrown.
     See Item_field::ref_group_by_validity_analyzer.
  There's error handling : implemented through 
  ref_group_by_validity_analyzer_arg::err_discovered. Initially this is off
  (false). But if an error is discovered 'err_discovered' is set to on causing
  each ref_group_by_validity_analyzer to return FALSE immediately, which 
  effects in getting out of the current compile() call immediately.
  
  In order to implement the above traversal Item::walk() will not suffice : 
  we need to be able to decide wether to check the sub-items at a hook level.
  So the Item::compile() function is extended to cover the same scope as 
  Item::walk().
  Then Item::compile() is used to traverse the Item tree.

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

ChangeSet@1.2325, 2006-12-12 00:46:14+03:00, evgen@moonbone.local +8 -0
  Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
  
  In the ONLY_FULL_GROUP_BY no hidden fields allowed in the select list.
  To ensure this all expression in the select list are checked to be an
  aggregate function, a constant or to be present in the GROUP BY list.
  The last requirement is too strict and doesn't allow expressions like
  CEILING(MAX(X)) which are valid.
  
  
  To solve this problem each select now keeps the list of fields that aren't
  used under any aggregate function. If an expression from the select list
  isn't found in the GROUP BY list the setup_group() function additionally
  checks whether non-aggregated fields from that expression is present.
  If there at least one field the error is thrown. 
  
  The Item_field objects now can store the position in the select list of the
  expression to which they are belongs to. The position is saved during the
  field's Item_field::fix_fields() call.
  
  The non_agg_fields list for non-aggregated fields is added to the SELECT_LEX
  class. The SELECT_LEX::cur_pos_in_select_list now stores the position in the
  select list of the expression being currently fixed.

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

ChangeSet@1.2325, 2006-12-12 03:45:39+03:00, evgen@moonbone.local +8 -0
  Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
  
  Currently in the ONLY_FULL_GROUP_BY mode no hidden fields allowed in the
  select list. To ensure this all expression in the select list are checked
  to be a constant, an aggregate function or to be present in the GROUP BY list.
  The last two requirements are wrong and doesn't allow valid expressions like
  "MAX(b) - MIN(b)" or "a + 1" in a query with grouping by a.
  
  The correct check implemented by the patch will ensure that:
  any field reference in the [sub]expressions of the select list 
    is under an aggregate function or
    is mentioned as member of the group list or
    an outer reference or
    is part of the select list element that coincide with a grouping element.
  
  The Item_field objects now can store the position in the select list of the
  expression to which they are belongs to. The position is saved during the
  field's Item_field::fix_fields() call.
  
  The non_agg_fields list for non-aggregated fields is added to the SELECT_LEX
  class. The SELECT_LEX::cur_pos_in_select_list now stores the position in the
  select list of the expression being currently fixed.

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

ChangeSet@1.2325, 2006-12-12 17:03:29+03:00, evgen@moonbone.local +8 -0
  Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
  
  Currently in the ONLY_FULL_GROUP_BY mode no hidden fields are allowed in the
  select list. To ensure this each expression in the select list is checked
  to be a constant, an aggregate function or to occur in the GROUP BY list.
  The last two requirements are wrong and doesn't allow valid expressions like
  "MAX(b) - MIN(b)" or "a + 1" in a query with grouping by a.
  
  The correct check implemented by the patch will ensure that:
  any field reference in the [sub]expressions of the select list 
    is under an aggregate function or
    is mentioned as member of the group list or
    is an outer reference or
    is part of the select list element that coincide with a grouping element.
  
  The Item_field objects now can contain the position if the select list
  expression which they belong to. The position is saved during the
  field's Item_field::fix_fields() call.
  
  The non_agg_fields list for non-aggregated fields is added to the SELECT_LEX
  class. The SELECT_LEX::cur_pos_in_select_list now contains the position in the
  select list of the expression being currently fixed.

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

ChangeSet@1.2325, 2007-01-10 21:32:48+03:00, evgen@moonbone.local +9 -0
  Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode.
  
  Currently in the ONLY_FULL_GROUP_BY mode no hidden fields are allowed in the
  select list. To ensure this each expression in the select list is checked
  to be a constant, an aggregate function or to occur in the GROUP BY list.
  The last two requirements are wrong and doesn't allow valid expressions like
  "MAX(b) - MIN(b)" or "a + 1" in a query with grouping by a.
  
  The correct check implemented by the patch will ensure that:
  any field reference in the [sub]expressions of the select list 
    is under an aggregate function or
    is mentioned as member of the group list or
    is an outer reference or
    is part of the select list element that coincide with a grouping element.
  
  The Item_field objects now can contain the position of the select list
  expression which they belong to. The position is saved during the
  field's Item_field::fix_fields() call.
  
  The non_agg_fields list for non-aggregated fields is added to the SELECT_LEX
  class. The SELECT_LEX::cur_pos_in_select_list now contains the position in the
  select list of the expression being currently fixed.

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

ChangeSet@1.2376, 2007-01-11 22:42:16+03:00, evgen@moonbone.local +10 -0
  Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode.
  
  Currently in the ONLY_FULL_GROUP_BY mode no hidden fields are allowed in the
  select list. To ensure this each expression in the select list is checked
  to be a constant, an aggregate function or to occur in the GROUP BY list.
  The last two requirements are wrong and doesn't allow valid expressions like
  "MAX(b) - MIN(b)" or "a + 1" in a query with grouping by a.
  
  The correct check implemented by the patch will ensure that:
  any field reference in the [sub]expressions of the select list 
    is under an aggregate function or
    is mentioned as member of the group list or
    is an outer reference or
    is part of the select list element that coincide with a grouping element.
  
  The Item_field objects now can contain the position of the select list
  expression which they belong to. The position is saved during the
  field's Item_field::fix_fields() call.
  
  The non_agg_fields list for non-aggregated fields is added to the SELECT_LEX
  class. The SELECT_LEX::cur_pos_in_select_list now contains the position in the
  select list of the expression being currently fixed.

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

ChangeSet@1.2376, 2007-01-11 23:18:01+03:00, evgen@moonbone.local +10 -0
  Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode.
  
  Currently in the ONLY_FULL_GROUP_BY mode no hidden fields are allowed in the
  select list. To ensure this each expression in the select list is checked
  to be a constant, an aggregate function or to occur in the GROUP BY list.
  The last two requirements are wrong and doesn't allow valid expressions like
  "MAX(b) - MIN(b)" or "a + 1" in a query with grouping by a.
  
  The correct check implemented by the patch will ensure that:
  any field reference in the [sub]expressions of the select list 
    is under an aggregate function or
    is mentioned as member of the group list or
    is an outer reference or
    is part of the select list element that coincide with a grouping element.
  
  The Item_field objects now can contain the position of the select list
  expression which they belong to. The position is saved during the
  field's Item_field::fix_fields() call.
  
  The non_agg_fields list for non-aggregated fields is added to the SELECT_LEX
  class. The SELECT_LEX::cur_pos_in_select_list now contains the position in the
  select list of the expression being currently fixed.

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

ChangeSet@1.2378, 2007-01-15 22:35:31+03:00, evgen@moonbone.local +1 -0
  sql_select.cc:
    Fix for crashes on 64bit platforms after fixing bug#23417.

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

ChangeSet@1.2386, 2007-01-19 19:05:01+03:00, evgen@moonbone.local +1 -0
  sql_select.cc:
    Code cleanup after fix for bug#23417.

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

ChangeSet@1.2391, 2007-01-22 23:04:14+03:00, evgen@moonbone.local +1 -0
  sql_select.cc:
    Code cleanup after fix for bug#23417.

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

Noted in 5.0.36, 5.1.16 changelogs.

With ONLY_FULL_GROUP_BY enables, the server was too strict: Some
expressions involving only aggregate values were rejected as
non-aggregate (for example, MAX(a) - MIN(a)).