Bug #36798 aggregate functions with columns and aggregate functions works (shouldn't?)
Submitted: 19 May 2008 13:05 Modified: 12 Jun 2008 15:33
Reporter: Tobias Asplund Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.1.23/5.1.24 OS:Any
Assigned to: CPU Architecture:Any

[19 May 2008 13:05] Tobias Asplund
Description:
Change of behaviour for GROUP BY-functions when not using a GROUP BY clause when having mixed column expressions with both aggregate functions and columns.

5.1.22:
mysql> SELECT Continent, SUM(Population) FROM Country;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

5.1.24:
mysql> SELECT Continent, SUM(Population) FROM Country;
+-----------+-----------------+
| Continent | SUM(Population) |
+-----------+-----------------+
| Asia      |      6078749450 | 
+-----------+-----------------+
1 row in set (0.03 sec)

mysql> SET sql_mode = 'only_full_group_by';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Continent, SUM(Population) FROM Country;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

How to repeat:
Install world.sql from manual pages and run:
SELECT Continent, SUM(Population) FROM Country;
[10 Jun 2008 17:28] Paul DuBois
Accounted for by this change?

"
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. (Bug#27219)
"

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-24.html