mysql错误 "ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query"

Question

我正在执行以下查询

SELECT DISTINCT n.nid AS entity_id
FROM node n
INNER JOIN og_membership om ON n.nid=om.etid AND om.entity_type='node'
INNER JOIN foster_animal_capacity fc ON n.nid=fc.person_id
LEFT OUTER JOIN field_data_field_attributes fa ON n.nid=fa.entity_id
LEFT OUTER JOIN foster_person_black_outs fb ON n.nid=fb.person_id
WHERE -- n.nid = 1441663 AND 
(om.gid = 464) AND (fc.animal_type_id = 3) 
AND ((fc.capacity - fc.occupied)>=1) 
AND ((fb.start_date IS NULL) OR (fb.end_date < 1523577600) OR (fb.start_date > 1522540800))
AND ((SELECT pid FROM `animal_history` WHERE `TYPE` = 'Foster Return'   
AND pid = n.nid ORDER BY DATE_FORMAT(FROM_UNIXTIME( UNIX_TIMESTAMP( ) - MAX( CAST(`time` AS UNSIGNED) ) ) ,'%e')));

查询在MySql 5.5.5中完美执行

但是在MySql 5.7.21中显示以下错误

ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

为什么会发生这种情况？我怎样才能克服这个问题？

我找不到错误代码 3029 的任何文档

Answer 1

正如 MySQL 文档中提到的:MySQL 5.7.5及以上版本实现了函数依赖检测。如果启用 ONLY_FULL_GROUP_BY SQL 模式(默认情况下)，MySQL 将拒绝选择列表、HAVING 条件或 ORDER BY 列表引用既未在 GROUP BY 子句中命名也不在功能上依赖于它们的非聚合列的查询。 (在 5.7.5 之前，MySQL 不检测函数依赖性，并且默认情况下不启用 ONLY_FULL_GROUP_BY。有关 5.7.5 之前的行为的说明，请参阅 MySQL 5.6

详情请查看: MySQL Handling of GROUP BY