Search code examples
mysql

Is there any bad of disable FULL_GROUP_BY

What's the benifit of ONLY_FULL_GROUP_BY, And why not disable it?

Solution

  • ONLY_FULL_GROUP_BY is enabled by default in current versions of mysql and mariadb.

    It is a good thing, and can keep you from writing SQL that uses non-grouped by columns whose value is non-determinate and is not portable to other databases that enforce this.

    An example query that it would prevent:

    create table foo (bar int, baz int);
insert into foo values (1,1),(1,3),(2,2);

# count how many of each bar in foo
select bar, count(*)
from foo
group by bar
order by baz

    Here, each output row will have a bar and a count, and you are telling it to order those by baz, but since there can be multiple baz values for each bar, it is ambiguous which you mean. Often what you actually mean is to use the minimum or maximum value instead; ONLY_FULL_GROUP_BY forces you to clarify your intent:

    # count how many of each bar in foo
select bar, count(*)
from foo
group by bar
order by max(baz)

    Same thing if you are just selecting a non-grouped value:

    select bar, baz
from foo
group by bar

    Here, which baz should appear in the results is ambiguous, and you have to tell it which:

    select bar, min(baz) as baz
from foo
group by bar

    The only reasons to not have it enabled would be if you have too many existing queries that violate it to change (even though such queries are potentially not doing what you actually intend), or, for mariadb, if you are using functionally dependent columns (as allowed in the 2003 sql standard, but not the 1992 standard) which are not yet allowed (https://jira.mariadb.org/browse/MDEV-11588).