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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.