报错:
1 of ORDER BY clause is not in SELECT list, references column 'xxx' which is not in SELECT list
最近发现有个sql
执行报错:
xpression #1 of ORDER BY clause is not in SELECT list, references column ‘database.table.column’ which is not in SELECT list; this is incompatible with DISTINCT
以下是程序执行的原sql:
select distinct sub_order_no , item_snapshot_idfrom tab_od_item toiwhere toi.sub_order_no in('111','222') order by id
复制
查阅资料发现在mysql5.7.5(Tidb的mysql模式)及以上版本实现了对功能依赖的检测。默认启用了ONLY_FULL_GROUP_BY SQL模式。在该模式下:
1、当使用GROUP BY查询时,出现在SELECT字段后面的只能是GROUP BY后面的分组字段,或使用聚合函数包裹着的字段,否则会报错如下信息:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
2、当使用ORDER BY查询时,不能使用SELECT DISTINCT去重查询。否则会报错如下信息:Expression #1 of ORDER BY clause is not in SELECT list, references column 'database.table.column' which is not in SELECT list; this is incompatible with DISTINCT
解决办法
关闭sql_mode=ONLY_FULL_GROUP_BY模式即可
1、查看是否开启了ONLY_FULL_GROUP_BY规则校验,在数据库中执行如下sql
SELECT VERSION(), @@sql_mode;
复制
2、关闭ONLY_FULL_GROUP_BY的规则校验,关闭规则校验需要执行如下sql。以下两个sql单引号中的内容是第一步查询的结果去掉“ONLY_FULL_GROUP_BY,”之后的值
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
复制