遇到一个 PostgreSQL 很奇葩的排序问题（BUG？）

[imherer]

我有一张 goods 表主要有 id 、name 、recommend 这 3 个字段，其中 id 为自增主键，name 为字符串，recommend 为 int

我往表里插了 37 条测试数据，id 从 1-37，其中 id=1 的 recommend 为 0，其他剩下 36 条全为 0

在插入 37 条测试数据后，我修改过部分数据的 name 值，于是当我使用如下 SQL 语句

SELECT * FROM goods ORDER BY recommend DESC;

返回的结果 id=1 的排在第一位，剩下的 36 按自增 id 随机排列。其中 id=2 的这条记录排在倒数第二位（重点就是这一条记录）。

现在遇到一个比较奇葩的问题，我分页获取的时候

SELECT * FROM mall.goods  ORDER BY recommend DESC LIMIT 10 OFFSET 0;

上面这条 SQL 语句居然第二条记录是 id=2 的数据

SELECT * FROM mall.goods  ORDER BY recommend DESC LIMIT 10 OFFSET 30;

同样，上面这条 SQL 语句里也出现了 id=2 的数据，不过它出现的位置是在倒数第二位，和第一条 SQL 语句排序出现的位置一致

这啥情况？

[reus]

id 又不在排序字段里，有什么问题？建议重修 SQL

[yali3da]

问题在哪里？

[imherer]

@reus
@zhaoce
问题是 OFFSET 0 和 30 都出现了 id=2 的数据， 按 recommend 了排序不应该只出现一次吗？

[Vegetable]

你的 recommend 都是 0? 是你发帖写错了吗?

[imherer]

@Vegetable id=1 的 recommend=1，剩余 36 条都是 0

[allAboutDbmss]

有数据集吗？我可以本地试一下？
`LIMIT`不一定保证顺序。你可以用`rank()`或者`dense_rank()`这俩窗口函数，我觉得能保证正确性。

[reus]

@imherer 两条语句，结果是互相独立的。

[bagel]

不可能是 bug，这种身经百战的产品能让你简单试出 bug 就见鬼了。

因为你 order by 没有指定全，SQL 规范里对这种情况的排序是未定义（指没有被 order by 指定 column 的顺序），也就是怎么排都可以，看实现者方便。同一个语句 PostgreSQL 这次返回一个排序，下次返回另一个排序都不算违反规范。你遇到的就是三个语句 PostgreSQL 内部实现返回了它自认为方便的顺序而已。具体为啥可以去翻源码，但问题本质就是我说的这个。

[Vegetable]

排序列不唯一的情况下,是不保证每次得到的顺序相同的.
常见的做法是使用 id 作为第二个 key 排序.

[Vegetable]

*唯一说的是 distinct

[imherer]

@reus
@bagel
@Vegetable
那看来是这样的了，我以为的第一条 SQL 排序的结果，后面分页的时候只要排序字段一样，理论上应该出现和第一条 SQL 语句一样的结果。

[imherer]

@allAboutDbmss
github.com/im-here/pgtest

不知道能不能复现。

我最开始的操作是插入了 37 条测试数据之后 Postico 里查看是按 id 顺序排列的，然后通过 Postico 修改了部分数据的 name，然后顺序就乱掉了。这时候查询的出来的数据就不对了。

[allAboutDbmss]

@imherer 如何导入？你是用 psql 命令行 COPY 的吗？

[imherer]

@allAboutDbmss 我用的 Postico

[allAboutDbmss]

@imherer 我是 linux 没有这个 Postico，我就不尝试复现了
不管你用的是哪一个具体的数据库，SQL 中如果你没有指定顺序，输出的顺序是无法理解的。
原因有很多:
- 关系 realtion 就是集合，集合是无序的。数据库在未指定顺序的时候，它不会花时间去排序
- 大数据集情况下，输出可能是用很多线程完成的。执行同一个 SQL 语句前后几次，输出可能因为多线程原因而不一样

你前后几条 SQL 只是指定了`recommend`的顺序，我们不能凭它们的输出去猜测 id=2 在哪里。

[index90]

楼主的意思是，这个 order 并不是“稳定排序”。这的确有点奇怪，假设 recommend 是 timestamp 字段，我按照时间排序（当然会有重复），然后分页返回，按道理第一页的数据和第二页的数据应该不相同才对。

可能 pg 需要你指定第二个排序键才能保证“稳定”啊

[index90]

搜到一篇文章，希望帮到你： https://www.jb51.net/article/159126.htm

[sagaxu]

粘包即视感

[reus]

@index90 你搜 "mysql 分页 重复", "sql server 分页 重复", "oracle 分页 重复"，都会发现同样的行为。对 SQL 没有充分的理解，就会犯这个错误，并不是 pg 和其他数据库系统有什么不同。

[HashV2]

我从 MySQL 迁移到 postgre 也遇到了这个问题

分页数据 update 了一个无关排序的字段, 刷新当前页数据 升级的字段的那条数据不见了

Mysql 上并没有这个问题 你是怎么解决的?

[imherer]

@HashV2 再加一个不会变的字段排序，例如主键

[HashV2]

@imherer 我是使用 Django ORM 来进行操作的 之前在 mysql 上没遇到这个问题

我很起怪 不变的排序字段条件且 update 的字段不在排序字段中 排序为什么有变化?

[imherer]

@HashV2 好像 postgresql 数据库数据在 table 里的排序不是固定的，有时候 update 了某个值之后排序就会变（没排序的情况下）

[HashV2]

@imherer 手动在排序参数最后额外添加一个 id/创建时间键 确实解决了这个问题

虽然很奇怪 但先这么用着吧 谢谢啦