MySQL Bugs: #27219: count(*) in order by

Bug #27219	count(*) in order by
Submitted:	16 Mar 2007 15:48	Modified:	7 Apr 2008 17:24
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.38	OS:	Linux (Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	count, order by, qc

Description:
I couldnt find the documentation that says, if you are / are not allowed aggregate functions in order by.

Mysql 4.x doesnt allow it, mysql 5.x does allow it.

I assume it is intended to be allowed in 5.x in which case the bug(s) as described below. (If it is not allowed at all, the bug is obvious).

Normally you can not mix aggregate and non-aggregate columns without a "GROUP BY"
 select count(*), a from t1;
 ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)...

There is no documentation, if that should apply to hidden fields too.
 select a from t1 order by count(*); # 1 row
 select a from t1 order by count(b); # many rows

Both of the queries return a result. (@@sql_mode='TRADITIONAL' AND @@sql_mode='ONLY_FULL_GROUP_BY')
One of them does group and return 1 row;
the other one does not group and return many rows.

The Bug is either (and maybe depending on sql-mode?):
1)
The 2 statements above should both fail, due to mix of aggregate / non-aggregate
2)
The behaviour of the 2 statements is not consistent. They should either both do the grouping, or both not do it (follow one and the same logic)

How to repeat:
drop table if exists t1;
create table t1 (a int, b int);
insert into t1 values (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);

set @@sql_mode='TRADITIONAL';
select a from t1 order by count(*);
select a from t1 order by count(b);

set @@sql_mode='ONLY_FULL_GROUP_BY';
select a from t1 order by count(*);
select a from t1 order by count(b);

------------ with results
mysql> set @@sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> select a from t1 order by count(*);
+------+
| a    |
+------+
|    2 |
|    2 |
|    3 |
|    3 |
|    3 |
|    4 |
+------+
6 rows in set (0.00 sec)

mysql> select a from t1 order by count(b);
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql>
mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select a from t1 order by count(*);
+------+
| a    |
+------+
|    2 |
|    2 |
|    3 |
|    3 |
|    3 |
|    4 |
+------+
6 rows in set (0.00 sec)

mysql> select a from t1 order by count(b);
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Suggested fix:
Decide upon the intended behaviour.

I attach a test case, which contains several variations of the above queries, including the usage of non-filed/constants in the select part. Some of the queries in the test do already work ok, and are only for coverage.
The .result file will be missing, as the behaviour needs decission first.

template for test

Attachment: order_by_aggregate.test (application/octet-stream, text), 1.65 KiB.

Thank you for the report.

Verified as described.

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

ChangeSet@1.2498, 2007-06-27 17:35:25+03:00, mhansson@linux-st28.site +3 -0
  Bug #27219: count(*) in order by
  Aggregate functions were allowed in ORDER BY queries even when sql mode 
  ONLY_FULL_GROUP_BY was set. Previously this was checked only for queries
  with GROUP BY. Fixed by checking for aggregate functions when there is an
  ORDER BY also.

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

ChangeSet@1.2546, 2008-01-04 15:26:46+01:00, mhansson@linux-st28.site +4 -0
  Bug #27219: count(*) in order by
  
  Aggregate functions were allowed in ORDER BY clauses even when sql mode 
  ONLY_FULL_GROUP_BY was set. Previously this was checked only for GROUP BY
  clauses. Fixed by checking for aggregate functions in ORDER BY clause also.

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

ChangeSet@1.2546, 2008-01-12 16:06:01+01:00, mhansson@linux-st28.site +3 -0
  Bug #27219: count(*) in order by
  
  Aggregate functions were allowed in ORDER BY clauses without GROUP BY,
  with unclear semantics and inconsistent results.
  Fixed by rasing an error in this case if ONLY_FULL_GROUP_BY is set.

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

ChangeSet@1.2590, 2008-01-31 19:07:28+01:00, mhansson@linux-st28.site +8 -0
  Bug#27219: count(*) in order by
  
  The error check for using aggregate functions in ORDER BY was incomplete,
  especially with respect to subqueries.
  Fixed by extending the check to cater for nesting of grouped statements.

There is a similar problem with subqueries in the HAVING clause: subquery without GROUP BY clause rises ER_MIX_OF_GROUP_FUNC_AND_FIELDS if its HAVING clause contains set function that was aggregated in the outer query:

SELECT t1.a FROM t1 GROUP BY t1.a
  HAVING t1.a IN (SELECT t2.a FROM t2 HAVING SUM(t1.b) = t2.a);

Actually there is no mixing of grouped columns with non-grouped: SUM(t1.b) is aggregated on the outer query level and it is constant on the subquery level, so there are no groupings in subquery at all.

Oops, please ignore phrase: "so there are no groupings in subquery at all" at the end of previous message, of course there is a grouping without GROUP BY clause.

Sorry for another mistake: query in the previous messages should be

SELECT t1.a FROM t1 GROUP BY t1.a
  HAVING t1.a IN (SELECT t2.a FROM t2 HAVING SUM(t1.b));

(error message is: "1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause")

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

ChangeSet@1.2607, 2008-02-22 05:41:21+04:00, gshchepa@host.loc +7 -0
  Fixed bug #27219: mixing of columns and aggregate
  functions (COUNT/MIN/SUM...) in SELECT and ORDER BY
  expression list in the query without GROUP BY clause
  doesn't raise the ER_MIX_OF_GROUP_FUNC_AND_FIELDS
  error in the ONLY_FULL_GROUP_BY mode.
  
  Examples:
    SELECT a FROM t ORDER BY COUNT(a);
    SELECT 1 FROM t ORDER BY COUNT(*), a;
  
  That has been fixed. Also recognition of parts in
  the expressions like "SUM(a) + b" has been implemented
  (queries like "SELECT SUM(a) + b FROM t" evaluated
  without a error in the ER_MIX_OF_GROUP_FUNC_AND_FIELDS
  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/44004

ChangeSet@1.2604, 2008-03-14 17:28:40+03:00, evgen@moonbone.local +7 -0
  Bug#27219: Aggregate functions in ORDER BY.
  
  Mixing of aggregate functions and non-grouping columns aren't allowed in the
  ONLY_FULL_GROUP_BY mode. However in some cases the error wasn't thrown because
  of insufficient check.
  
  In order to check more thoroughly the new algorithm employs two bitmaps
  lex->non_agg_fields_used and lex->agg_funcs_used. Each bit in them represents
  a level of nesting. When it is set to 1 it means that respectively a non
  aggregated field or an aggregate function were used on this level of select
  nesting. An error is thrown when bits are set in both variables for a nesting
  level. Bits are set by the Item_field::fix_fields and the
  Item_sum::check_sum_func functions.

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

ChangeSet@1.2604, 2008-03-25 00:13:43+03:00, evgen@moonbone.local +8 -0
  Bug#27219: Aggregate functions in ORDER BY.
  
  Mixing of aggregate functions and non-grouping columns aren't allowed in the
  ONLY_FULL_GROUP_BY mode. However in some cases the error wasn't thrown because
  of insufficient check.
  
  In order to check more thoroughly the new algorithm employs a list of outer
  fields used in a sum function and a SELECT_LEX::full_group_by_flag.
  Each non-outer field checked to be aggregated or not by the 
  Item_field::fix_fields function and the current select is marked accordingly.
  All outer fields that used under an aggregate function are added to the
  Item_sum::outer_fields list and later checked by the Item_sum::check_sum_func
  function.

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

ChangeSet@1.2604, 2008-03-26 20:18:10+03:00, evgen@moonbone.local +10 -0
  Bug#27219: Aggregate functions in ORDER BY.
  
  Mixing aggregate functions and non-grouping columns is not allowed in the
  ONLY_FULL_GROUP_BY mode. However in some cases the error wasn't thrown because
  of insufficient check.
  
  In order to check more thoroughly the new algorithm employs a list of outer
  fields used in a sum function and a SELECT_LEX::full_group_by_flag.
  Each non-outer field checked to find out whether it's aggregated or not by the 
  Item_field::fix_fields function and the current select is marked accordingly.
  All outer fields that are used under an aggregate function are added to the
  Item_sum::outer_fields list and later checked by the Item_sum::check_sum_func
  function.

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

ChangeSet@1.2604, 2008-03-27 01:02:49+03:00, evgen@moonbone.local +10 -0
  Bug#27219: Aggregate functions in ORDER BY.
  
  Mixing aggregate functions and non-grouping columns is not allowed in the
  ONLY_FULL_GROUP_BY mode. However in some cases the error wasn't thrown because
  of insufficient check.
  
  In order to check more thoroughly the new algorithm employs a list of outer
  fields used in a sum function and a SELECT_LEX::full_group_by_flag.
  Each non-outer field checked to find out whether it's aggregated or not and
  the current select is marked accordingly.
  All outer fields that are used under an aggregate function are added to the
  Item_sum::outer_fields list and later checked by the Item_sum::check_sum_func
  function.

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

ChangeSet@1.2601, 2008-03-28 13:53:24+04:00, gshchepa@host.loc +10 -0
  Bug#27219: Aggregate functions in ORDER BY.
  
  Mixing aggregate functions and non-grouping columns is not allowed in the
  ONLY_FULL_GROUP_BY mode. However in some cases the error wasn't thrown because
  of insufficient check.
  
  In order to check more thoroughly the new algorithm employs a list of outer
  fields used in a sum function and a SELECT_LEX::full_group_by_flag.
  Each non-outer field checked to find out whether it's aggregated or not and
  the current select is marked accordingly.
  All outer fields that are used under an aggregate function are added to the
  Item_sum::outer_fields list and later checked by the Item_sum::check_sum_func
  function.

Pushed into 5.0.60

Pushed into 5.1.24-rc

Noted in 5.0.60, 5.1.24 changelogs.

In ORDER BY clauses, mixing aggregate functions and non-grouping
columns is not allowed if the ONLY_FULL_GROUP_BY SQL mode is enabled.
However, in some cases, no error was thrown because of insufficient
checking. 

Resetting report to Patch queued waiting for push into 6.0.x.

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.