Redshift外层查询加ORDER BY时子查询SELECT DISTINCT去重失效问题

这个异常本质是数据库查询优化器的执行计划漂移，叠加系统视图统计信息失真共同导致的，和SQL本身的逻辑正确性无关。

执行路径的前后差异 #

• 未加ORDER BY时的正常执行逻辑：
  优化器识别到base子查询的SELECT DISTINCT column_name需要先完成去重，拿到仅包含c1、c2两行的结果集后，再依次和三个表的列信息做LEFT JOIN计算，最终返回符合预期的2条结果。
• 加上ORDER BY 1后的异常执行逻辑：
  优化器识别到最终结果需要按第一列（也就是base.column_name）排序，出于降低执行成本的考量，尝试合并去重、关联、排序的执行步骤。但因为CTE的数据源是information_schema系统视图：
  • 绝大多数数据库不会为系统视图收集动态的行数、字段基数统计信息，更不会因为你创建了临时表就实时更新视图的统计元数据
  • 优化器基于失真的固定统计值做成本估算时，会错误判定子查询内部执行DISTINCT是冗余操作：它认为既然最后要按column_name排序，排序过程中自然可以顺带去重，提前做去重反而多一次全量扫描，因此直接把base子查询的去重步骤消除，让base退化为无去重的SELECT column_name FROM table_cols
    这时候table_cols里c1会因为同时存在于table_0、table_2被读取两次，对应的两条c1记录经过LEFT JOIN计算后，因为布尔值标记完全一致，就输出了两条一模一样的c1行，形成观测到的重复结果。

为什么加外层DISTINCT能临时修复 #

你在外层查询加的DISTINCT相当于给优化器打了明确标记：要求必须在最终结果返回前做全量去重。这就强制优化器绕过了之前错误估算带来的“省掉去重步骤”的逻辑，因此能让结果恢复正常。

同类问题的典型排查特征 #

后续碰到类似“加排序就出重复、去掉排序就正常”的问题，可以对照几个共性判断是否属于同类优化器偏差：

• 无明显逻辑错误的前提下，查询加/不加ORDER BY时返回的行数不一致
• 数据源依赖information_schema、pg_catalog这类无动态统计信息的系统视图
• 去重逻辑写在子查询/CTE内部，且去重的字段同时是后续JOIN、ORDER BY的关联/排序字段
• 自己构造普通测试表做复现时永远无法重现问题，只有在系统视图数据源下才会触发异常

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