Rechercher dans le manuel MySQL
12.20.3 MySQL Handling of GROUP BY
SQL-92 and earlier does not permit queries for which the select
list, HAVING
condition, or ORDER
BY
list refer to nonaggregated columns that are not
named in the GROUP BY
clause. For example,
this query is illegal in standard SQL-92 because the
nonaggregated name
column in the select list
does not appear in the GROUP BY
:
For the query to be legal in SQL-92, the name
column must be omitted from the select list or named in the
GROUP BY
clause.
SQL:1999 and later permits such nonaggregates per optional
feature T301 if they are functionally dependent on
GROUP BY
columns: If such a relationship
exists between name
and
custid
, the query is legal. This would be the
case, for example, were custid
a primary key
of customers
.
MySQL implements detection of functional dependence. If the
ONLY_FULL_GROUP_BY
SQL mode is
enabled (which it is by default), MySQL rejects queries for
which the select list, HAVING
condition, or
ORDER BY
list refer to nonaggregated columns
that are neither named in the GROUP BY
clause
nor are functionally dependent on them.
If ONLY_FULL_GROUP_BY
is
disabled, a MySQL extension to the standard SQL use of
GROUP BY
permits the select list,
HAVING
condition, or ORDER
BY
list to refer to nonaggregated columns even if the
columns are not functionally dependent on GROUP
BY
columns. This causes MySQL to accept the preceding
query. In this case, the server is free to choose any value from
each group, so unless they are the same, the values chosen are
nondeterministic, which is probably not what you want.
Furthermore, the selection of values from each group cannot be
influenced by adding an ORDER BY
clause.
Result set sorting occurs after values have been chosen, and
ORDER BY
does not affect which value within
each group the server chooses. Disabling
ONLY_FULL_GROUP_BY
is useful
primarily when you know that, due to some property of the data,
all values in each nonaggregated column not named in the
GROUP BY
are the same for each group.
You can achieve the same effect without disabling
ONLY_FULL_GROUP_BY
by using
ANY_VALUE()
to refer to the
nonaggregated column.
The following discussion demonstrates functional dependence, the error message MySQL produces when functional dependence is absent, and ways of causing MySQL to accept a query in the absence of functional dependence.
This query might be invalid with
ONLY_FULL_GROUP_BY
enabled
because the nonaggregated address
column in
the select list is not named in the GROUP BY
clause:
The query is valid if name
is a primary key
of t
or is a unique NOT
NULL
column. In such cases, MySQL recognizes that the
selected column is functionally dependent on a grouping column.
For example, if name
is a primary key, its
value determines the value of address
because
each group has only one value of the primary key and thus only
one row. As a result, there is no randomness in the choice of
address
value in a group and no need to
reject the query.
The query is invalid if name
is not a primary
key of t
or a unique NOT
NULL
column. In this case, no functional dependency
can be inferred and an error occurs:
- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
If you know that, for a given data set,
each name
value in fact uniquely determines
the address
value, address
is effectively functionally dependent on
name
. To tell MySQL to accept the query, you
can use the ANY_VALUE()
function:
Alternatively, disable
ONLY_FULL_GROUP_BY
.
The preceding example is quite simple, however. In particular, it is unlikely you would group on a single primary key column because every group would contain only one row. For addtional examples demonstrating functional dependence in more complex queries, see Section 12.20.4, “Detection of Functional Dependence”.
If a query has aggregate functions and no GROUP
BY
clause, it cannot have nonaggregated columns in the
select list, HAVING
condition, or
ORDER BY
list with
ONLY_FULL_GROUP_BY
enabled:
- #1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
Without GROUP BY
, there is a single group and
it is nondeterministic which name
value to
choose for the group. Here, too,
ANY_VALUE()
can be used, if it is
immaterial which name
value MySQL chooses:
ONLY_FULL_GROUP_BY
also affects handling of
queries that use DISTINCT
and ORDER
BY
. Consider the case of a table t
with three columns c1
, c2
,
and c3
that contains these rows:
c1 c2 c3
1 2 A
3 4 B
1 2 C
Suppose that we execute the following query, expecting the
results to be ordered by c3
:
To order the result, duplicates must be eliminated first. But to
do so, should we keep the first row or the third? This arbitrary
choice influences the retained value of c3
,
which in turn influences ordering and makes it arbitrary as
well. To prevent this problem, a query that has
DISTINCT
and ORDER BY
is
rejected as invalid if any ORDER BY
expression does not satisfy at least one of these conditions:
The expression is equal to one in the select list
All columns referenced by the expression and belonging to the query's selected tables are elements of the select list
Another MySQL extension to standard SQL permits references in
the HAVING
clause to aliased expressions in
the select list. For example, the following query returns
name
values that occur only once in table
orders
:
The MySQL extension permits the use of an alias in the
HAVING
clause for the aggregated column:
Standard SQL permits only column expressions in GROUP
BY
clauses, so a statement such as this is invalid
because FLOOR(value/100)
is a noncolumn
expression:
MySQL extends standard SQL to permit noncolumn expressions in
GROUP BY
clauses and considers the preceding
statement valid.
Standard SQL also does not permit aliases in GROUP
BY
clauses. MySQL extends standard SQL to permit
aliases, so another way to write the query is as follows:
The alias val
is considered a column
expression in the GROUP BY
clause.
In the presence of a noncolumn expression in the GROUP
BY
clause, MySQL recognizes equality between that
expression and expressions in the select list. This means that
with ONLY_FULL_GROUP_BY
SQL
mode enabled, the query containing GROUP BY id,
FLOOR(value/100)
is valid because that same
FLOOR()
expression occurs in the
select list. However, MySQL does not try to recognize functional
dependence on GROUP BY
noncolumn expressions,
so the following query is invalid with
ONLY_FULL_GROUP_BY
enabled,
even though the third selected expression is a simple formula of
the id
column and the
FLOOR()
expression in the
GROUP BY
clause:
A workaround is to use a derived table:
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-group-by-handling.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.