MySQL 8日期对比查询异常：同语句在MySQL 5.6运行正常问题排查

问题根因 #

该差异由MySQL 8.0查询优化器的逻辑变更导致，核心规则是无LIMIT的子查询内部ORDER BY语句会被优化器直接忽略。
MySQL 5.6版本中，子查询内部的ORDER BY n会生效，最内层t表返回的n是从0开始的有序递增序列，后续date_add计算出的日期也按顺序递增，外层筛选后得到的结果符合预期。
MySQL 8.0版本中，优化器默认认为不带分页（无LIMIT）的子查询内部排序没有语义价值，会直接跳过ORDER BY n的执行逻辑。再结合子查询中冗余的DISTINCT逻辑，优化器处理去重时返回的n值是随机无序的，你当前环境命中的情况是返回了3个重复的n=10（2021-08-20加10天刚好是2021-08-30），所以输出了3行相同的错误日期。

修复方案 #

可任选以下任意一种方案解决问题：

• 给最内层排序的子查询增加LIMIT，限制优化器删除排序逻辑，LIMIT值设置为大于你实际需要的最大n值即可：

SELECT date(m.dt) 
FROM (SELECT date_add(date("2021-08-20 00:00:00"), INTERVAL t.n DAY) dt 
      FROM (SELECT DISTINCT Tens.N * 10 + Ones.N AS n 
            FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) Tens, 
                 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) Ones 
            ORDER BY n LIMIT 100) t 
      WHERE t.n <= timestampdiff(DAY, "2021-08-20 00:00:00", "2021-09-26 23:59:59"))m 
WHERE date(m.dt) >= date('2021-09-24');

• 直接删除冗余的DISTINCT关键字：你的子查询中Tens和Ones的笛卡尔积生成的n值本身就是全局唯一的，不需要去重，删除DISTINCT后排序逻辑可正常生效。
• 临时关闭对应优化器开关，执行语句SET optimizer_switch = 'derived_merge=off';，禁用子查询合并优化，原有查询可直接返回正确结果。

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