为何同一条SQL查询在MySQL 5运行正常，在MySQL 8中结果不符合预期？

场景说明 #

业务需求为查找被标记为无乳腺筛查相关数据，但系统中实际存在对应数据的记录：即无钼靶（mammogram）记录，但存在门诊记录或MRI记录的筛查周期，仅纳入门诊检查CBE结果符合指定范围的记录，合规结果列表从结果字典表t_lk_cbe_result中查询获取。
主表为筛查周期表t_cycle，其余关联表均关联到该表的主键。

原问题查询语句 #

该语句在MySQL 5中运行正常，返回约20条符合预期的记录；在MySQL 8.0.18中返回超过7000条记录，大部分记录的门诊CBE结果不在合规范围内：

SELECT 
               cyc.f_enroll_id,
               cyc.f_cycle_number,
               ov.f_cbe_result,
               mri.f_uid as 'MRI'
FROM
               t_cycle cyc LEFT JOIN
               t_mam_rpt mam ON cyc.f_uid = mam.f_cycle_uid LEFT JOIN
               t_office_visit ov ON cyc.f_uid = ov.f_cycle_uid AND ov.f_cbe_result IN (SELECT f_code FROM t_lk_cbe_result WHERE f_mde_code IN (1,2)) LEFT JOIN 
               t_mri mri ON cyc.f_uid = mri.f_cycle_uid 
WHERE
               cyc.f_mam_indication = 5 AND
               mam.f_uid IS NULL AND
               (ov.f_uid IS NOT NULL OR
               mri.f_uid IS NOT NULL);

不可落地的临时验证方案 #

方案1：移除MRI存在的判断条件 #

查询结果可恢复正常，但不符合业务需要同时覆盖门诊、MRI两类记录的要求，无法落地：

/* 移除OR mri.f_uid IS NOT NULL后查询结果正常 */
SELECT 
               cyc.f_enroll_id,
               cyc.f_cycle_number,
               ov.f_cbe_result,
               mri.f_uid as 'MRI'
FROM
               t_cycle cyc LEFT JOIN
               t_mam_rpt mam ON cyc.f_uid = mam.f_cycle_uid LEFT JOIN
               t_office_visit ov ON cyc.f_uid = ov.f_cycle_uid AND ov.f_cbe_result IN (SELECT f_code FROM t_lk_cbe_result WHERE f_mde_code IN (1,2)) LEFT JOIN 
               t_mri mri ON cyc.f_uid = mri.f_cycle_uid 
WHERE
               cyc.f_mam_indication = 5 AND
               mam.f_uid IS NULL AND
              ov.f_uid IS NOT NULL ;

方案2：硬编码合规结果值 #

查询结果可恢复正常，但合规结果列表为动态配置项，无法硬编码维护，无法落地：

/* 将JOIN条件中子查询替换为硬编码常量后查询结果正常 */
SELECT 
               cyc.f_enroll_id,
               cyc.f_cycle_number,
               ov.f_cbe_result,
               mri.f_uid as 'MRI'
FROM
               t_cycle cyc LEFT JOIN
               t_mam_rpt mam ON cyc.f_uid = mam.f_cycle_uid LEFT JOIN
               t_office_visit ov ON cyc.f_uid = ov.f_cycle_uid AND ov.f_cbe_result IN (2,30,31,33,34,35,38,39,40,42,43,44,45,46,47) LEFT JOIN 
               t_mri mri ON cyc.f_uid = mri.f_cycle_uid 
WHERE
               cyc.f_mam_indication = 5 AND
               mam.f_uid IS NULL AND
               (ov.f_uid IS NOT NULL OR
               mri.f_uid IS NOT NULL);

生产可用修复方案 #

将JOIN条件中的子查询改为单独关联字典表，再将结果校验逻辑放到WHERE条件中，查询结果可符合预期：

SELECT 
   cyc.f_enroll_id,
   cyc.f_cycle_number,
   ov.f_cbe_result,
   mri.f_uid as 'MRI'
FROM
   t_cycle cyc LEFT JOIN
   t_office_visit ov ON cyc.f_uid = ov.f_cycle_uid LEFT JOIN
   t_lk_cbe_result lk ON ov.f_cbe_result = lk.f_code LEFT JOIN 
   t_mam_rpt mam ON cyc.f_uid = mam.f_cycle_uid LEFT JOIN
   t_mri mri ON cyc.f_uid = mri.f_cycle_uid 
WHERE
   cyc.f_mam_indication = 5 AND
   mam.f_uid IS NULL AND 
   ((ov.f_uid IS NOT NULL AND lk.f_mde_code IN (1,2)) OR
   mri.f_uid IS NOT NULL)

原因推测 #

该故障仅在门诊表数据量较大时可复现，结合当前使用的MySQL 8.0.18版本特性，高度怀疑为该版本的已知优化器Bug，后续可通过升级MySQL版本验证修复。

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