MySQL Bugs: #8510: ONLY_FULL_GROUP_BY sql mode is overly restrictive

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

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

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

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.

"Remove ONLY_FULL_GROUP_BY mode from ANSI mode" fix pushed into 4.1.11 (and merged into 5.0.4).

Noted in 4.1.11 and 5.0.3 (not 5.0.4) changelogs.

Updated description for ANSI SQL mode.

the queries in this bug report work fine in MySQL 5.6. Looks like bug is gone.

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.