MySQL 8.0中row_number子查询关联表过滤条件失效问题排查

问题背景 #

需要查询epm_eval_activity_user表中每个用户排名第一的数据，编写了包含row_number() over(partition by user_code order by status)的子查询并与epm_user表内连接，出现以下异常：

• 单独执行子查询能获取目标用户（user_code='764451'）的记录
• epm_user表中存在该user_code的记录
• 使用WHERE au.user_code='764451'过滤时返回0行
• 使用WHERE p.user_code='764451'过滤时正常返回结果

可能的原因及验证方法 #

1. 字符匹配差异（不可见字符/空格/编码细微差别） #

表面上两个表的user_code都是'764451'，但实际存储可能存在不可见字符（如换行符、空字节）或末尾空格，导致直接过滤子查询的user_code时不匹配，但内连接时因数据关联逻辑偶然匹配。

验证方法：
分别查询两个表中目标user_code的十六进制值，对比是否一致：

-- 查询子查询中user_code的十六进制
SELECT user_code, HEX(user_code)
FROM (
    SELECT *, row_number() over(partition by user_code order by status) rn
    FROM epm_eval_activity_user
) au
WHERE user_code LIKE '%764451%';

-- 查询epm_user中user_code的十六进制
SELECT user_code, HEX(user_code)
FROM epm_user
WHERE user_code = '764451';

若HEX值不同，说明存在字符差异，需清洗数据（如去除空格、不可见字符）。

2. MySQL优化器执行计划差异（条件下推导致逻辑异常） #

MySQL 8.0.22的优化器可能将au.user_code='764451'的过滤条件提前下推到子查询中，在计算row_number()之前就过滤记录，破坏了窗口函数的分区逻辑；而使用p.user_code='764451'时，优化器会先从epm_user获取目标记录，再关联子查询，窗口函数逻辑正常执行。

验证方法：
对比两种过滤条件的执行计划：

-- 查看au.user_code过滤的执行计划
EXPLAIN
SELECT *
FROM epm_user p
JOIN (
    SELECT *, row_number() over(partition by user_code order by status) rn
    FROM epm_eval_activity_user
) au ON p.user_code = au.user_code
WHERE au.user_code = '764451';

-- 查看p.user_code过滤的执行计划
EXPLAIN
SELECT *
FROM epm_user p
JOIN (
    SELECT *, row_number() over(partition by user_code order by status) rn
    FROM epm_eval_activity_user
) au ON p.user_code = au.user_code
WHERE p.user_code = '764451';

若第一个执行计划显示过滤条件被下推到子查询阶段，可尝试禁用条件下推测试：

SET optimizer_switch='condition_pushdown=off';
-- 重新执行au.user_code过滤的SQL
SELECT *
FROM epm_user p
JOIN (
    SELECT *, row_number() over(partition by user_code order by status) rn
    FROM epm_eval_activity_user
) au ON p.user_code = au.user_code
WHERE au.user_code = '764451';

若此时返回结果，说明是优化器bug，建议升级到MySQL 8.0.28及以上版本修复。

3. 子查询窗口函数逻辑遗漏 #

若需求是获取每个用户排名第一的数据，但子查询未添加rn=1的过滤条件，可能导致子查询返回该用户的多条记录，进而引发优化器处理异常。正确写法需明确筛选排名第一的记录：

-- 子查询中过滤排名第一
SELECT *
FROM epm_user p
JOIN (
    SELECT *, row_number() over(partition by user_code order by status) rn
    FROM epm_eval_activity_user
) au ON p.user_code = au.user_code
WHERE au.rn = 1 AND au.user_code = '764451';

解决建议 #

1. 优先检查字符匹配问题，清洗数据中的异常字符；
2. 若为优化器bug，升级MySQL版本；
3. 确保窗口函数逻辑正确，添加rn=1的过滤条件以实现需求。

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