前沿

今天下午在查询Tidb数据库时候，突然发现报错了，但是同样的sql在上面执行还没有问题，最后是发现sql 模式的问题，Tidb对group by 返回字段约束力特别强。

正文

解决方式

当我们查询Tidb的时候，报下面的错误信息

[42000][1055] Expression #18 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ac_accounting.l.receipt_account_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by?

起初我将原sql 在外层包装了一下，使用 select * from (原sql)? group by? uniq_data_id

执行后错误信息变成了

?runtime error: index out of range [-1]

?查询资料是由于tidb版本的问题，但是我没有升级版本呀，难道是自动升级的，见鬼了。

若我们查询当前sql_mode ，执行下面的sql?

show VARIABLES LIKE 'sql_mode';

会发现是only_full_group_by ，如果仔细观察第一次报错信息，就会发现，信息最后已经提示说sql_mode =?only_full_group_by,只是当时我没注意到这点。

sql_mode 模式是?only_full_group_by,大体意思是我们在查询select * from a group by a.name 类似于这样的sql语句，是不满足only_full_group_by模式的，该模式要求 group by 的字段要和查询返回的字段要一致，这可能也不是tidb的问题，也是tidb在?only_full_group_by 模式下强约束的结果，我们只要关闭?only_full_group_by 模式即可，通过执行下面sql 临时关闭

set sql_mode = '';

?执行完后，我们在运行刚才同样的sql，就不会出现问题了

若想永久性关闭该模式，那么需要修改mysql的配置文件，找到配置文件/etc/my.cnf，在[mysqld]后追加

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

或者

sql_mode = ''

保存后存在mysql 即可。

延展

参考资料，在SQL 92 对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.

翻译：SELECT、HAVING、ORDER后的非聚合字段必须和GROUP BY后的字段保持完全一致

但是我们经常会看到在mysql中的sql 语句查询的字段和group by 的字段不一致的情况，这是因为在SQL99中做了新的规定，如下所示

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.

翻译：如果group by后面的字段是主键，而且非聚合字段是依赖group by后字段的，那么可以将这些非聚合字段放在SELECT、HAVING、ORDER BY的语句之后。

但是这个时候主角 ONLY_FULL_GROUP_BY出现了，该模式的意思类似是SQL 92 对group by 的限制。
?