V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
imherer
V2EX  ›  PostgreSQL

遇到一个 PostgreSQL 很奇葩的排序问题(BUG?)

  •  
  •   imherer · 2020-04-16 15:27:44 +08:00 · 4663 次点击
    这是一个创建于 1442 天前的主题,其中的信息可能已经有所发展或是发生改变。

    我有一张 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 语句排序出现的位置一致

    这啥情况?

    24 条回复    2020-09-16 15:49:37 +08:00
    reus
        1
    reus  
       2020-04-16 15:44:34 +08:00
    id 又不在排序字段里,有什么问题?建议重修 SQL
    yali3da
        2
    yali3da  
       2020-04-16 16:15:33 +08:00
    问题在哪里?
    imherer
        3
    imherer  
    OP
       2020-04-16 16:41:45 +08:00
    @reus
    @zhaoce
    问题是 OFFSET 0 和 30 都出现了 id=2 的数据, 按 recommend 了排序不应该只出现一次吗?
    Vegetable
        4
    Vegetable  
       2020-04-16 16:46:18 +08:00
    你的 recommend 都是 0? 是你发帖写错了吗?
    imherer
        5
    imherer  
    OP
       2020-04-16 16:47:46 +08:00
    @Vegetable id=1 的 recommend=1,剩余 36 条都是 0
    allAboutDbmss
        6
    allAboutDbmss  
       2020-04-16 16:54:54 +08:00
    有数据集吗?我可以本地试一下?
    `LIMIT`不一定保证顺序。你可以用`rank()`或者`dense_rank()`这俩窗口函数,我觉得能保证正确性。
    reus
        7
    reus  
       2020-04-16 17:08:10 +08:00
    @imherer 两条语句,结果是互相独立的。
    bagel
        8
    bagel  
       2020-04-16 17:11:09 +08:00   ❤️ 1
    不可能是 bug,这种身经百战的产品能让你简单试出 bug 就见鬼了。

    因为你 order by 没有指定全,SQL 规范里对这种情况的排序是未定义(指没有被 order by 指定 column 的顺序),也就是怎么排都可以,看实现者方便。同一个语句 PostgreSQL 这次返回一个排序,下次返回另一个排序都不算违反规范。你遇到的就是三个语句 PostgreSQL 内部实现返回了它自认为方便的顺序而已。具体为啥可以去翻源码,但问题本质就是我说的这个。
    Vegetable
        9
    Vegetable  
       2020-04-16 17:13:01 +08:00
    排序列不唯一的情况下,是不保证每次得到的顺序相同的.
    常见的做法是使用 id 作为第二个 key 排序.
    Vegetable
        10
    Vegetable  
       2020-04-16 17:13:46 +08:00
    *唯一说的是 distinct
    imherer
        11
    imherer  
    OP
       2020-04-16 17:19:11 +08:00
    @reus
    @bagel
    @Vegetable
    那看来是这样的了,我以为的第一条 SQL 排序的结果,后面分页的时候只要排序字段一样,理论上应该出现和第一条 SQL 语句一样的结果。
    imherer
        12
    imherer  
    OP
       2020-04-16 17:33:14 +08:00
    @allAboutDbmss
    github.com/im-here/pgtest

    不知道能不能复现。

    我最开始的操作是插入了 37 条测试数据之后 Postico 里查看是按 id 顺序排列的,然后通过 Postico 修改了部分数据的 name,然后顺序就乱掉了。这时候查询的出来的数据就不对了。
    allAboutDbmss
        13
    allAboutDbmss  
       2020-04-16 18:08:08 +08:00
    @imherer 如何导入?你是用 psql 命令行 COPY 的吗?
    imherer
        14
    imherer  
    OP
       2020-04-16 18:48:00 +08:00
    @allAboutDbmss 我用的 Postico
    allAboutDbmss
        15
    allAboutDbmss  
       2020-04-16 19:12:07 +08:00
    @imherer 我是 linux 没有这个 Postico,我就不尝试复现了
    不管你用的是哪一个具体的数据库,SQL 中如果你没有指定顺序,输出的顺序是无法理解的。
    原因有很多:
    - 关系 realtion 就是集合,集合是无序的。数据库在未指定顺序的时候,它不会花时间去排序
    - 大数据集情况下,输出可能是用很多线程完成的。执行同一个 SQL 语句前后几次,输出可能因为多线程原因而不一样

    你前后几条 SQL 只是指定了`recommend`的顺序,我们不能凭它们的输出去猜测 id=2 在哪里。
    index90
        16
    index90  
       2020-04-16 19:24:00 +08:00
    楼主的意思是,这个 order 并不是“稳定排序”。这的确有点奇怪,假设 recommend 是 timestamp 字段,我按照时间排序(当然会有重复),然后分页返回,按道理第一页的数据和第二页的数据应该不相同才对。

    可能 pg 需要你指定第二个排序键才能保证“稳定”啊
    index90
        17
    index90  
       2020-04-16 19:30:09 +08:00
    搜到一篇文章,希望帮到你: https://www.jb51.net/article/159126.htm
    sagaxu
        18
    sagaxu  
       2020-04-16 19:48:25 +08:00 via Android
    粘包即视感
    reus
        19
    reus  
       2020-04-16 21:58:02 +08:00   ❤️ 1
    @index90 你搜 "mysql 分页 重复", "sql server 分页 重复", "oracle 分页 重复",都会发现同样的行为。对 SQL 没有充分的理解,就会犯这个错误,并不是 pg 和其他数据库系统有什么不同。
    HashV2
        20
    HashV2  
       2020-09-16 14:53:44 +08:00
    我从 MySQL 迁移到 postgre 也遇到了这个问题

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

    Mysql 上并没有这个问题 你是怎么解决的?
    imherer
        21
    imherer  
    OP
       2020-09-16 14:57:22 +08:00
    @HashV2 再加一个不会变的字段排序,例如主键
    HashV2
        22
    HashV2  
       2020-09-16 15:04:34 +08:00
    @imherer 我是使用 Django ORM 来进行操作的 之前在 mysql 上没遇到这个问题

    我很起怪 不变的排序字段条件且 update 的字段不在排序字段中 排序为什么有变化?
    imherer
        23
    imherer  
    OP
       2020-09-16 15:40:02 +08:00   ❤️ 1
    @HashV2 好像 postgresql 数据库数据在 table 里的排序不是固定的,有时候 update 了某个值之后排序就会变(没排序的情况下)
    HashV2
        24
    HashV2  
       2020-09-16 15:49:37 +08:00
    @imherer 手动在排序参数最后额外添加一个 id/创建时间键 确实解决了这个问题

    虽然很奇怪 但先这么用着吧 谢谢啦
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3286 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 14:08 · PVG 22:08 · LAX 07:08 · JFK 10:08
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.