PostgreSQL：ORDER BY和LIMIT/OFFSET的奇怪碰撞

Question

我正在尝试在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)

但是到底怎么回事？为什么我必须使用一种变通方法？

- Paul

Answer 1

骂人不会改变SQL标准所定义的这种行为。
除非在ORDER BY中指定了顺序，否则行的顺序未定义。关于此请参阅手册：

如果没有选择排序，行将以未指定的顺序返回。在这种情况下的实际顺序将取决于扫描和连接计划类型以及磁盘上的顺序，但不能依赖于它。只有在明确选择排序步骤时才能保证特定的输出顺序。

由于您没有为这两个对等项（在排序顺序中）定义顺序：

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

..你得到任意的排序 - 任何对于Postgres方便的都可以。使用LIMIT的查询通常使用不同的查询计划，这可能会解释不同的结果。

修复

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

或者（可能更有意义-也可能根据现有索引进行调整）：

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

（顺便说一下，这与在此处使用COLLATE“C”无关。）
不要为此目的连接字符串，这样更加昂贵，而且可能使使用索引变得不可能（除非您有一个针对该精确表达式的索引）。添加另一个表达式，以便在ORDER BY列表中的先前表达式产生歧义时发挥作用。

另外，由于您在那里有一个LEFT JOIN，没有在vm中匹配的m中的行对于所有当前ORDER BY表达式具有空值。它们最后出现，并且在其他方面是随意排序的。如果您想在整体上获得稳定的排序顺序，您也需要处理它。比如：

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

附言

为什么要存储双引号？看起来是昂贵的噪声。如果需要，您可以在输出时添加引号。

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

- Erwin Brandstetter

---

据我所记，在MSSQL中，排序也可能是任意的，但它不会从一个查询到另一个查询发生改变。 - Paul

@Paul：通常在Postgres中也不会这样，但你不能依赖于它。当未指定时，顺序取决于实现的具体细节和各个行在物理上保存的位置。 - Erwin Brandstetter