Bug #8510 ONLY_FULL_GROUP_BY sql mode is overly restrictive
Submitted: 15 Feb 2005 2:48 Modified: 15 Mar 2005 20:58
Reporter: Tim Smith
Status: Closed
Category:Server Severity:S2 (Serious)
Version:4.1.10 OS:Any (any)
Assigned to: Sergey Petrunia Target Version:

[15 Feb 2005 2:48] Tim 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 8: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 8:33] Sergey Petrunia 
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 8:34] Sergey Petrunia 
"Remove ONLY_FULL_GROUP_BY mode from ANSI mode" fix pushed into 4.1.11 (and merged into
5.0.4).
[15 Mar 2005 20:58] Paul DuBois 
Noted in 4.1.11 and 5.0.3 (not 5.0.4) changelogs.

Updated description for ANSI SQL mode.