PostgreSQL 15.5查询结果异常：添加字段为何改变结果

核心问题：无序窗口函数与子查询ORDER BY失效 #

你遇到的问题根源在于两个关键问题：

• ROW_NUMBER() OVER () 未指定ORDER BY子句：窗口函数ROW_NUMBER()在没有明确排序规则时，会根据PostgreSQL执行计划的行扫描顺序分配序号，这个顺序是不确定的——不同执行计划、不同场景下，序号分配结果可能完全不同。
• 子查询中的ORDER BY modified_at DESC不生效：在PostgreSQL中，不带LIMIT的子查询里的ORDER BY会被优化器忽略。因为关系模型的结果集是无序的，子查询返回的是一个无序集合，外层查询不会继承这个排序规则。

这就导致两个子查询a和b的row_number分配没有固定对应关系，哪怕是同一张视图，两次子查询的行顺序可能完全不一致。用row_number关联后，a和b对应的可能是完全不同的行，自然会出现a.km <> b.km的结果。

为什么移除a.*后结果符合预期？ #

这只是优化器执行计划巧合导致的：当查询字段很少时，优化器可能选择了一种刚好让两个子查询行顺序一致的执行路径（比如都按modified_at排序扫描），使得row_number能对应到同一行；但当查询a.*时，优化器可能切换了执行计划（比如为了获取所有字段选择了不同的索引或扫描顺序），导致两个子查询的row_number分配错位，从而出现不等的情况。这种结果是不可靠的，换个场景可能又会出现问题。

正确的解决方案 #

给ROW_NUMBER()的窗口明确指定唯一的排序键（比如主键id），确保每次执行时，每行的row_number都是固定且唯一的，这样两个子查询的row_number就能稳定关联到同一行：

SELECT a.row_number
     , a.*
     , a.km
     , b.km         AS km_alone
     , a.km <> b.km AS unequal_km
FROM (SELECT *
           , ROW_NUMBER() OVER (ORDER BY id) AS row_number
      FROM schema.my_view) AS a
         INNER JOIN (SELECT *
                          , ROW_NUMBER() OVER (ORDER BY id) AS row_number
                     FROM schema.my_view) AS b ON a.row_number = b.row_number
WHERE a.km <> b.km;

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