Bug #42322 5.1.30-community allows mixing of group and no group columns without group by

• Submitted: 24 Jan 2009 15:46
• Modified: 24 Jan 2009 15:56
• Reporter: Paco Zarabozo
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.30
• OS: Windows (SP3)
• CPU Architecture: Any
• Tags: GROUP BY, mixing of group columns with no group columns

[24 Jan 2009 15:46] Paco Zarabozo

Description:
Hello,

I recently installed MySQL 5.1.30-community and found out while trying some query that it doesn't throuw any error with the following query:

select host, count(user) from (select * from mysql.user) as test;

We have mySQL 5.0.51a-community-nt in another server, and there it correctly throws the following error:

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:
Using MySQL 5.1.30-community:

select host, count(user) from (select * from mysql.user) as test;

Suggested fix:
Throw error:

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

[24 Jan 2009 15:56] MySQL Verification Team

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional Info;

Please use the sql_mode ONLY_FULL_GROUP_BY. Thanks in advance.

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

"ONLY_FULL_GROUP_BY

Do not allow queries for which the SELECT list refers to non-aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

As of MySQL 5.1.11, this mode also restricts references to non-aggregated columns in the HAVING clause that are not named in the GROUP BY clause. "