Description
1. What did you do?
SQL92 and earlier does not permit queries for which the SELECT list, HAVING condition, or ORDER BY list refer to non-aggregated columns that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL92 because the non-aggregated column "b" in the SELECT list does not appear in the GROUP BY:
drop table if exists t;
create table t(a bigint, b bigint, c bigint);
insert into t values(1, 2, 3), (2, 2, 3), (3, 2, 3);
select a, b, sum(c) from t group by a;SQL99 and later permits such non-aggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between "b" and "a", the query is legal. For example, when "a" is a primary key of table "t", this query is legal:
drop table if exists t;
create table t(a bigint primary key, b bigint, c bigint);
insert into t values(1, 2, 3), (2, 2, 3), (3, 2, 3);
select a, b, sum(c) from t group by a;Since MySQL 5.7.5, sql mode ONLY_FULL_GROUP_BY is set as default. This mode rejects queries for which the SELECT list, HAVING condition, or ORDER BY list refer to non-aggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
TiDB performs equivalent to MySQL with ONLY_FULL_GROUP_BY being disabled: permits the SELECT list, HAVING condition, or ORDER BY list to refer to non-aggregated columns even if the columns are not functionally dependent on GROUP BY columns.
In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what users 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.
MySQL enabled ONLY_FULL_GROUP_BY sql mode by default and provides a ANY_VALUE() function to achieve the same effect.
2. What did you expect to see?
drop table if exists t;
create table t(a bigint, b bigint, c bigint);
insert into t values(1, 2, 3), (2, 2, 3), (3, 2, 3);MySQL > select a, b, sum(c) from t group by a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by3. What did you see instead?
drop table if exists t;
create table t(a bigint, b bigint, c bigint);
insert into t values(1, 2, 3), (2, 2, 3), (3, 2, 3);TiDB > select a, b, sum(c) from t group by a;
+------+------+--------+
| a | b | sum(c) |
+------+------+--------+
| 1 | 2 | 3 |
| 2 | 2 | 3 |
| 3 | 2 | 3 |
+------+------+--------+
3 rows in set (0.00 sec)4. What version of TiDB are you using (tidb-server -V)?
TiDB > select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: 0.8.0
Git Commit Hash: 3e1728b4b853c224daa969fcc3d03be5d0860ef4
Git Branch: master
UTC Build Time: 2017-08-19 12:33:06 |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)