SQL聚合查询中is_muted字段预期返回0实际返回1问题求助

问题根因 #

这是MySQL独有的非标准SQL执行逻辑导致的问题，核心是你关闭了ONLY_FULL_GROUP_BY SQL模式：

• 按照标准SQL规范，使用GROUP BY后，SELECT子句仅允许出现两类内容：GROUP BY指定的分组字段、被聚合函数包裹的计算值。你这里仅分组了devices.id，但SELECT里的devices.hostname、devices.last_seen_at、devices.is_muted都不属于上述两类，本身就不符合SQL规范。
• MySQL关闭ONLY_FULL_GROUP_BY时会允许这类不规范的查询执行，但不会做字段合法性校验，而是直接从每个分组的多行结果里随机取第一行的对应字段值返回，取值完全依赖执行计划生成的结果集排序，没有确定性。
• 不同的查询写法会生成不同的执行计划，JOIN后的结果集排序存在差异，才会出现不同的返回结果：移除hostname或者移除JOIN时，每个分组的第一行正好是devices表的原始行，取值正确；同时保留hostname和JOIN时，分组第一行不是devices原始行，返回了错误值。
• 你用IF函数包裹is_muted后返回正常，本质是触发了MySQL的字段取值校验，强制从devices表原始行读取数据，规避了随机取行的问题。

修复方案 #

1. 开启ONLY_FULL_GROUP_BY模式，避免写出不符合规范的查询，从根源杜绝这类随机取值问题。
2. 对所有非分组的devices表字段用MAX或MIN聚合函数包裹，保证取值确定性，修改后的查询参考：

SELECT
    devices.id,
    MAX(devices.hostname) AS hostname,
    MAX(devices.last_seen_at) AS last_seen_at,
    MAX(devices.is_muted) AS is_muted,
    SUM(CASE WHEN events.is_healthy = 0 THEN 1 ELSE 0 END) AS num_checks_unhealthy
FROM devices
LEFT JOIN events ON events.devices_id = devices.id
GROUP BY devices.id

因为devices.id是表主键，同一个id对应的hostname、last_seen_at、is_muted都是唯一值，用MAX/MIN不会改变原始取值，同时符合SQL规范。

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