Movatterモバイル変換

SQL-92 and earlier does not permit queries for which the select list,HAVING condition, orORDER BY list refer to nonaggregated columns that are not named in theGROUP BY clause. For example, this query is illegal in standard SQL-92 because the nonaggregatedname column in the select list does not appear in theGROUP BY:

SELECT o.custid, c.name, MAX(o.payment)  FROM orders AS o, customers AS c  WHERE o.custid = c.custid  GROUP BY o.custid;

For the query to be legal in SQL-92, thename column must be omitted from the select list or named in theGROUP BY clause.

SQL:1999 and later permits such nonaggregates per optional feature T301 if they are functionally dependent onGROUP BY columns: If such a relationship exists betweenname andcustid, the query is legal. This would be the case, for example, werecustid a primary key ofcustomers.

MySQL 5.7.5 and later implements detection of functional dependence. If theONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list,HAVING condition, orORDER BY list refer to nonaggregated columns that are neither named in theGROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency andONLY_FULL_GROUP_BY is not enabled by default.)

MySQL 5.7.5 and later also permits a nonaggregate column not named in aGROUP BY clause whenONLY_FULL_GROUP_BY SQL mode is enabled, provided that this column is limited to a single value, as shown in the following example:

mysql> CREATE TABLE mytable (    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,    ->    a VARCHAR(10),    ->    b INT    -> );mysql> INSERT INTO mytable    -> VALUES (1, 'abc', 1000),    ->        (2, 'abc', 2000),    ->        (3, 'def', 4000);mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';+------+--------+| a    | SUM(b) |+------+--------+| abc  |   3000 |+------+--------+

It is also possible to have more than one nonaggregate column in theSELECT list when employingONLY_FULL_GROUP_BY. In this case, every such column must be limited to a single value, and all such limiting conditions must be joined by logicalAND, as shown here:

mysql> DROP TABLE IF EXISTS mytable;mysql> CREATE TABLE mytable (    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,    ->    a VARCHAR(10),    ->    b VARCHAR(10),    ->    c INT    -> );mysql> INSERT INTO mytable    -> VALUES (1, 'abc', 'qrs', 1000),    ->        (2, 'abc', 'tuv', 2000),    ->        (3, 'def', 'qrs', 4000),    ->        (4, 'def', 'tuv', 8000),    ->        (5, 'abc', 'qrs', 16000),    ->        (6, 'def', 'tuv', 32000);mysql> SELECT @@session.sql_mode;+---------------------------------------------------------------+| @@session.sql_mode                                            |+---------------------------------------------------------------+| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |+---------------------------------------------------------------+mysql> SELECT a, b, SUM(c) FROM mytable    ->     WHERE a = 'abc' AND b = 'qrs';+------+------+--------+| a    | b    | SUM(c) |+------+------+--------+| abc  | qrs  |  17000 |+------+------+--------+

IfONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use ofGROUP BY permits the select list,HAVING condition, orORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent onGROUP 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 anORDER BY clause. Result set sorting occurs after values have been chosen, andORDER BY does not affect which value within each group the server chooses. DisablingONLY_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 theGROUP BY are the same for each group.

You can achieve the same effect without disablingONLY_FULL_GROUP_BY by usingANY_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 withONLY_FULL_GROUP_BY enabled because the nonaggregatedaddress column in the select list is not named in theGROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

The query is valid ifname is a primary key oft or is a uniqueNOT NULL column. In such cases, MySQL recognizes that the selected column is functionally dependent on a grouping column. For example, ifname is a primary key, its value determines the value ofaddress 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 ofaddress value in a group and no need to reject the query.

The query is invalid ifname is not a primary key oft or a uniqueNOT NULL column. In this case, no functional dependency can be inferred and an error occurs:

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUPBY clause and contains nonaggregated column 'mydb.t.address' whichis not functionally dependent on columns in GROUP BY clause; thisis incompatible with sql_mode=only_full_group_by

If you know that,for a given data set, eachname value in fact uniquely determines theaddress value,address is effectively functionally dependent onname. To tell MySQL to accept the query, you can use theANY_VALUE() function:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Alternatively, disableONLY_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, seeSection 12.19.4, “Detection of Functional Dependence”.

If a query has aggregate functions and noGROUP BY clause, it cannot have nonaggregated columns in the select list,HAVING condition, orORDER BY list withONLY_FULL_GROUP_BY enabled:

mysql> SELECT name, MAX(age) FROM t;ERROR 1140 (42000): In aggregated query without GROUP BY, expression#1 of SELECT list contains nonaggregated column 'mydb.t.name'; thisis incompatible with sql_mode=only_full_group_by

WithoutGROUP BY, there is a single group and it is nondeterministic whichname value to choose for the group. Here, too,ANY_VALUE() can be used, if it is immaterial whichname value MySQL chooses:

SELECT ANY_VALUE(name), MAX(age) FROM t;

In MySQL 5.7.5 and later,ONLY_FULL_GROUP_BY also affects handling of queries that useDISTINCT andORDER BY. Consider the case of a tablet with three columnsc1,c2, andc3 that contains these rows:

c1 c2 c31  2  A3  4  B1  2  C

Suppose that we execute the following query, expecting the results to be ordered byc3:

SELECT DISTINCT c1, c2 FROM t ORDER 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 ofc3, which in turn influences ordering and makes it arbitrary as well. To prevent this problem, a query that hasDISTINCT andORDER BY is rejected as invalid if anyORDER BY expression does not satisfy at least one of these conditions:

Another MySQL extension to standard SQL permits references in theHAVING clause to aliased expressions in the select list. For example, the following query returnsname values that occur only once in tableorders:

SELECT name, COUNT(name) FROM orders  GROUP BY name  HAVING COUNT(name) = 1;

The MySQL extension permits the use of an alias in theHAVING clause for the aggregated column:

SELECT name, COUNT(name) AS c FROM orders  GROUP BY name  HAVING c = 1;

Standard SQL permits only column expressions inGROUP BY clauses, so a statement such as this is invalid becauseFLOOR(value/100) is a noncolumn expression:

SELECT id, FLOOR(value/100)  FROMtbl_name  GROUP BY id, FLOOR(value/100);

MySQL extends standard SQL to permit noncolumn expressions inGROUP BY clauses and considers the preceding statement valid.

Standard SQL also does not permit aliases inGROUP BY clauses. MySQL extends standard SQL to permit aliases, so another way to write the query is as follows:

SELECT id, FLOOR(value/100) AS val  FROMtbl_name  GROUP BY id, val;

The aliasval is considered a column expression in theGROUP BY clause.

In the presence of a noncolumn expression in theGROUP BY clause, MySQL recognizes equality between that expression and expressions in the select list. This means that withONLY_FULL_GROUP_BY SQL mode enabled, the query containingGROUP BY id, FLOOR(value/100) is valid because that sameFLOOR() expression occurs in the select list. However, MySQL does not try to recognize functional dependence onGROUP BY noncolumn expressions, so the following query is invalid withONLY_FULL_GROUP_BY enabled, even though the third selected expression is a simple formula of theid column and theFLOOR() expression in theGROUP BY clause:

SELECT id, FLOOR(value/100), id+FLOOR(value/100)  FROMtbl_name  GROUP BY id, FLOOR(value/100);

A workaround is to use a derived table:

SELECT id, F, id+F  FROM    (SELECT id, FLOOR(value/100) AS F     FROMtbl_name     GROUP BY id, FLOOR(value/100)) AS dt;