PostgreSQL：ORDER BY 和 LIMIT/OFFSET 的奇怪冲突

我正在尝试在 PostgreSQL 9.1 中执行此操作：

SELECT m.id, vm.id, vm.value
FROM m
LEFT JOIN vm ON vm.m_id = m.id and vm.variation_id = 1
ORDER BY lower(trim(vm.value)) COLLATE "C" ASC LIMIT 10 OFFSET 120

结果是：

 id |  id | value
----+-----+---------------
504 | 511 | "andr-223322"
506 | 513 | "andr-322223"
824 | 831 | "angHybrid"
866 | 873 | "Another thing"
493 | 500 | "App update required!"
837 | 844 | "App update required!"
471 | 478 | "April"
905 | 912 | "Are you sure you want to delete this thing?"
 25 |  29 | "Assignment"
196 | 201 | "AT ADDRESS"

好的，让我们执行相同的查询OFFSET 130:

 id |  id | value
----+-----+---------------
196 | 201 | "AT ADDRESS"
256 | 261 | "Att Angle"
190 | 195 | "Att Angle"
273 | 278 | "Att Angle:"
830 | 837 | "attAngle"
475 | 482 | "August"
710 | 717 | "Averages"
411 | 416 | "AVG"
692 | 699 | "AVG SHAPE"
410 | 415 | "AVGs"

我们看到我们的AT ADDRESS再次项目，但在开始！

事实是，vm表包含以下两项：

 id | m_id | value
----+------+---------------
201 |  196 | "AT ADDRESS"
599 |  592 | "At Address"

我通过解决方法解决了这种情况：

(lower(trim(vm.value)) || vm.id)

但到底是什么？？？！！！ 为什么我必须使用解决方法？

脏话不会改变定义此行为的 SQL 标准。
行的顺序是未定义，除非指定ORDER BY. 手册 https://www.postgresql.org/docs/current/queries-order.html:

如果未选择排序，则行将以未指定的形式返回 命令。在这种情况下的实际顺序将取决于扫描和连接 计划类型和磁盘上的顺序，但不得依赖它。 A 仅当显式选择排序步骤时才能保证特定的输出排序。

由于您没有为这两个对等点定义顺序（按照您的排序顺序）：

 id | m_id | value
----+------+---------------
201 |  196 | "AT ADDRESS"
599 |  592 | "At Address"

..你可以得到任意的排序——无论Postgres方便什么。一个查询LIMIT经常使用不同的查询计划，这可以解释不同的结果。

Fix

ORDER BY lower(trim(vm.value)) COLLATE "C", vm.id;  

或者（也许更有意义 - 也可能调整现有索引）：

ORDER BY lower(trim(vm.value)) COLLATE "C", vm.value, vm.id;  

（这与使用无关COLLATE "C"在这里，顺便说一句。）
不要串联为此目的，这要昂贵得多，并且可能无法使用index（除非您有该精确表达式的索引）。添加另一个表达式，当前面的表达式出现在ORDER BY列出留下歧义的内容。

另外，既然你有一个LEFT JOIN在那里，行m没有匹配vm所有当前的都有空值ORDER BY表达式。它们排在最后并且按其他方式任意排序。如果你想要一个稳定的整体排序顺序，你也需要处理这个问题。喜欢：

ORDER BY lower(trim(vm.value)) COLLATE "C", vm.id, m.id;  

Asides

为什么要存储双引号？似乎噪音成本很高。没有他们你可能会过得更好。如果需要，您可以随时在输出中添加引号。

许多客户端无法在一个结果中多次处理相同的列名。您至少需要一个列别名id列：SELECT m.id AS m_id, vm.id AS vm_id ...。说明为什么列的“id”首先是一种反模式。