MySQL高版本含known_flag is null条件的SQL无结果问题排查

问题背景 #

给定SQL语句：

SELECT t.id as row_random_number, t.*
from  
(
    SELECT a.*, b.known_flag FROM tms_vantage_latest_date_report as a 
    LEFT JOIN
    (
        SELECT parent_client_name, tms_location_name, true as known_flag
        from known_case
    ) as b
    ON (a.parent_client_name = b.parent_client_name AND a.tms_location_name = b.tms_location_name)
) as t
inner join
(
    select parent_client_name as distinct_parent_client_name, sum(credit_remaining_amt)
    from  
    (
        SELECT a.*, b.known_flag
        FROM tms_vantage_latest_date_report as a
        LEFT JOIN
        (
            SELECT parent_client_name, tms_location_name, true as known_flag
            from known_case
        ) as b
        ON (a.parent_client_name = b.parent_client_name AND a.tms_location_name = b.tms_location_name)
    ) as q
    where parent_client_name <> 'ALL OTHER CLIENTS' and credit_remaining_amt < 0 
    and known_flag is null
    and credit_remaining_amt < -250000  
    and ar_amt > 1 
    and ar_amt > (total_exposure_amt*0.09) 
    group by parent_client_name 
    order by sum(credit_remaining_amt)
) as p
on t.parent_client_name = p.distinct_parent_client_name  
where parent_client_name <> 'ALL OTHER CLIENTS' and credit_remaining_amt < 0 
and known_flag is null
and credit_remaining_amt < -250000  
and ar_amt > 1 
and ar_amt > (total_exposure_amt*0.09);

该SQL在MySQL 8.0.35中可正常返回结果，但在8.0.4、8.4.5等版本执行时无结果返回（无报错）；移除AND known_flag IS NULL条件后，高版本可正常返回结果。

核心原因 #

1. 优化器对子查询与LEFT JOIN的逻辑推导变化
   MySQL 8.0.4及之后的版本对查询优化器做了大幅迭代，其中针对LEFT JOIN后NULL条件的推导逻辑有调整。低版本中，LEFT JOIN未匹配生成的known_flag NULL值，会在后续关联子查询p时正常保留匹配；但高版本优化器可能会将known_flag IS NULL条件与LEFT JOIN的关联逻辑提前合并，错误地过滤掉了原本符合条件的行。

2. 布尔类型NULL值的处理差异
   SQL中known_flag是通过子查询生成的布尔值（true as known_flag），不同版本对布尔类型NULL的判断逻辑存在细微差别：低版本中IS NULL能准确识别LEFT JOIN未匹配时的NULL值；高版本中，优化器可能将布尔类型NULL与其他类型NULL做差异化处理，导致条件判断失效。

3. 重复子查询的合并优化问题
   主查询的t表和子查询p依赖完全相同的LEFT JOIN逻辑生成known_flag，高版本优化器会尝试合并这些重复子查询，但合并过程中known_flag IS NULL条件被重复应用，导致关联时原本应该匹配的parent_client_name无法成功关联。

解决办法 #

• 用CTE重构SQL，消除重复子查询
  将重复的LEFT JOIN逻辑提炼为公共表表达式（CTE），减少优化器的推导歧义，示例：

WITH base_data AS (
    SELECT a.*, b.known_flag
    FROM tms_vantage_latest_date_report as a
    LEFT JOIN (
        SELECT parent_client_name, tms_location_name, true as known_flag
        from known_case
    ) as b
    ON a.parent_client_name = b.parent_client_name 
    AND a.tms_location_name = b.tms_location_name
),
client_summary AS (
    SELECT parent_client_name as distinct_parent_client_name, sum(credit_remaining_amt)
    FROM base_data
    WHERE parent_client_name <> 'ALL OTHER CLIENTS' 
      AND credit_remaining_amt < -250000  
      AND ar_amt > 1 
      AND ar_amt > (total_exposure_amt*0.09)
      AND known_flag IS NULL
    GROUP BY parent_client_name 
    ORDER BY sum(credit_remaining_amt)
)
SELECT t.id as row_random_number, t.*
FROM base_data t
INNER JOIN client_summary p
    ON t.parent_client_name = p.distinct_parent_client_name
WHERE t.parent_client_name <> 'ALL OTHER CLIENTS' 
  AND t.credit_remaining_amt < -250000  
  AND t.ar_amt > 1 
  AND t.ar_amt > (t.total_exposure_amt*0.09)
  AND t.known_flag IS NULL;

• 替换NULL判断逻辑，避免布尔类型歧义
  直接通过判断关联表的主键/非空字段是否为NULL来替代known_flag IS NULL，比如：

-- 调整base_data中的LEFT JOIN部分
SELECT a.*, CASE WHEN b.parent_client_name IS NOT NULL THEN true ELSE NULL END as known_flag
FROM tms_vantage_latest_date_report as a
LEFT JOIN known_case b
    ON a.parent_client_name = b.parent_client_name 
    AND a.tms_location_name = b.tms_location_name

这样可以绕开布尔类型NULL的处理差异，让条件判断更可靠。

• 临时关闭子查询合并优化（不推荐长期使用）
  如果暂时无法重构SQL，可以执行SET optimizer_switch='derived_merge=off';关闭子查询合并优化，阻止优化器合并重复的子查询，但这会影响整体查询性能，仅作为临时解决方案。

内容的提问来源于stack exchange，提问作者Talha Bin Shakir