Re: Postgres offset and limit bug

From: Ming <ming(at)cleanstormwater(dot)com(dot)au>
To: David Rowley <dgrowleyml(at)gmail(dot)com>
Cc: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Postgres offset and limit bug
Date: 2022-09-08 10:45:15
Message-ID: SYZP282MB3110F3E7F9C26764A0697BA9E5409@SYZP282MB3110.AUSP282.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Thanks David~

Get Outlook for iOS<https://aka.ms/o0ukef>
________________________________
From: David Rowley <dgrowleyml(at)gmail(dot)com>
Sent: Thursday, September 8, 2022 7:03:33 PM
To: Ming <ming(at)cleanstormwater(dot)com(dot)au>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Postgres offset and limit bug

On Thu, 8 Sept 2022 at 20:49, Ming <ming(at)cleanstormwater(dot)com(dot)au> wrote:
> The issue is when I have limit 1 with the offset, the first record id will be 45 (which should be 46) . (Although, it’s ordered by path, the of is actually ordered as well.) But without the limit 1 the results will be correct. And when I plus one to the offset value, the actual offset will increase by 2.
>
> In general, the offset value with 45 is behave as offset 44. But when change the value to other thing, the results will be all good.

That's probably because the path for 45 and 46 are the same and you've
only specified that you want the results ordered by path. If there are
duplicates then the order that duplicates appear in will be
non-deterministic. Maybe you'll want to consider adding
source_template_id to the ORDER BY.

David

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2022-09-08 11:33:17 BUG #17611: SJIS conversion rule about duplicated characters differ from Windows
Previous Message PG Bug reporting form 2022-09-08 10:19:30 BUG #17610: Use of multiple composite types incompatible with record-typed function parameter