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)

但是什么地狱??? !!! 为什么必须使用替代方法？

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 那儿，所有m不匹配的行vm的所有当前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”首先是反模式。