MySQL 5.7 Left Join未在WHERE使用关联表却出现结果行丢失问题

你遇到的是MySQL 5.7的已知优化器Bug，核心触发逻辑如下：

• 当ORDER BY后的字段无法唯一确定排序顺序时，带LIMIT 1的依赖子查询会产生非确定性结果，MySQL 5.7的优化器在处理这类子查询时，存在概率性将原本有匹配结果的子查询错误返回空值的问题
• 你将该子查询的返回值作为LEFT JOIN ON条件的等值匹配项，当子查询错误返回空值时，优化器会错误判定整行不符合连接规则，直接丢弃左表的原有记录，而不是按照LEFT JOIN的标准逻辑保留左表行、右表字段置空，最终表现为结果行数随机减少
• MySQL 5.6不存在该问题是因为5.6的子查询优化逻辑与5.7不同，该Bug已经在MySQL 8.0.11及以上版本修复。

不需要修改整体查询结构，只要给子查询的ORDER BY追加唯一排序字段即可让排序变为确定性，即可解决问题，例如追加主键p.id：

SELECT p.id
FROM taxrecordpayment trpi
    JOIN payments p ON p.id = trpi.paymentId
WHERE trpi.taxRecordId = tr.Tax_ID AND p.isFullYear = 0
ORDER BY p.dueDate, p.paymentSendTo, p.id -- 追加唯一键保证排序确定性
LIMIT 1

该修改只调整了子查询的排序规则，不会影响业务逻辑，也不需要对海量数据做额外的维护操作，改造成本极低。

• 升级MySQL实例到8.0.11及以上版本，从底层修复该Bug
• 将子查询改写为非依赖的派生表提前固化结果，避免外层查询多次调用子查询时产生非确定性结果

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