sql - 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 中指定，否则行的顺序是未定义的。 The manual :

If sorting is not chosen, the rows will be returned in an unspecifiedorder. The actual order in that case will depend on the scan and joinplan types and the order on disk, but it must not be relied on. Aparticular output ordering can only be guaranteed if the sort step is explicitly chosen.

因为您没有为这两个节点定义顺序(按照您的排序顺序):

 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”是一种反模式。