MySQL 5.7与8的only_full_group_by行为差异咨询

问题场景 #

开启ONLY_FULL_GROUP_BY模式时：

• MySQL 5.7执行以下查询会触发报错：

select
 p.oid, p.idsex, c.codetext
 from p_person as p
 join codes as c on p.idsex = c.proid and c.dictcode = 0
 group by p.oid
 ;

• MySQL 8.0中该查询可正常运行；移除and c.dictcode = 0后，MySQL 8.0也会如预期报错。
• 前提信息：oid是p_person表的主键，codes表在proid+dictcode上有唯一索引。

原因说明 #

这确实是MySQL 8.0针对ONLY_FULL_GROUP_BY模式的函数依赖识别增强特性，具体逻辑如下：

• 由于p.oid是p_person的主键，MySQL 8.0能识别出p.idsex完全依赖于主键p.oid——主键唯一确定表中一行，每个p.oid对应的p.idsex值是唯一的。
• 再结合codes表的proid+dictcode唯一索引，以及查询中p.idsex = c.proid and c.dictcode = 0的关联条件，MySQL 8.0可以推导得出：每个p.oid分组对应的c.codetext只会有唯一值，不存在非聚合列的不确定性。
• MySQL 5.7的函数依赖识别逻辑没有覆盖这种跨表关联的唯一索引推导场景，因此会判定查询违反ONLY_FULL_GROUP_BY规则而报错。

当移除and c.dictcode = 0时，仅通过p.idsex = c.proid无法保证c.codetext的唯一性（一个proid可能对应多行codes数据），此时MySQL 8.0会严格遵循ONLY_FULL_GROUP_BY规则，触发预期的报错。

内容的提问来源于stack exchange，提问作者Tom Melly