Bug #8510 ONLY_FULL_GROUP_BY sql mode is overly restrictive
Submitted: 15 Feb 2005 1:48 Modified: 15 Mar 2005 19:58
Reporter: Timothy Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.10 OS:Any (any)
Assigned to: Sergey Petrunya CPU Architecture:Any

[15 Feb 2005 1:48] Timothy Smith
Description:
Legitimate queries fail with:

ERROR 1055 (42000): 'round(sum(a))' isn't in GROUP BY

when "set sql_mode = ONLY_FULL_GROUP_BY" is on.

How to repeat:
create table foo (a int, b int);

insert into foo values (1, 2), (1, 3), (null, null);

select sum(a), count(*) from foo group by a;  -- This one works

select round(sum(a)), count(*) from foo group by a;  -- This fails

select ifnull(a, 'xyz') from foo group by a;  -- This fails
[15 Mar 2005 7:25] Bugs System
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/internals/23020
[15 Mar 2005 7:33] Sergey Petrunya
The bug statement is correct - currently ONLY_FULL_GROUP_BY mode is overly restrictive. Queries that are allowed by the standard produce errors. 

Fixing ONLY_FULL_GROUP_BY mode requires good amount of work and will not be in the scope of this bugfix. We intend to make proper ONLY_FULL_GROUP_BY in the future (no date/server version # given).

For now, the following was done:
ONLY_FULL_GROUP_BY mode was removed from ANSI mode, so ANSI mode doesn't produce errors for queries allowed by the standard.
[15 Mar 2005 7:34] Sergey Petrunya
"Remove ONLY_FULL_GROUP_BY mode from ANSI mode" fix pushed into 4.1.11 (and merged into 5.0.4).
[15 Mar 2005 19:58] Paul DuBois
Noted in 4.1.11 and 5.0.3 (not 5.0.4) changelogs.

Updated description for ANSI SQL mode.
[5 Dec 2011 13:58] Guilhem Bichot
the queries in this bug report work fine in MySQL 5.6. Looks like bug is gone.
[16 May 2012 7:12] Roland Bouman
I can run example queries without issue on 5.5.20 too. Would be interesting to know whether this fix was intentional or a side effect of something else.