Thread: Postgres offset and limit bug

Postgres offset and limit bug

From
Ming
Date:
Hello,

We recently found a strange behavior on Postgres SQL. The offset function will not acting properly when having limit in the query. I want to confirm that it's an existing bug or it's my syntax is wrong.  

Please find the attachments for details. 

Client: PgAdmin
Client System: Windows 11
Instance: AWS RDS PostgreSQL 13.4


Mingxuan Chen
Software Engineera: w:
CleanStormWaterp: 0461602352e: mingxuan_chen@outlook.com
Attachment

Re: Postgres offset and limit bug

From
David Rowley
Date:
On Thu, 8 Sept 2022 at 19:20, Ming <ming@cleanstormwater.com.au> wrote:
> We recently found a strange behavior on Postgres SQL. The offset function will not acting properly when having limit
inthe query. I want to confirm that it's an existing bug or it's my syntax is wrong.
 

Nothing looks wrong from here. Can you describe what behaviour you're expecting?

David



Re: Postgres offset and limit bug

From
Ming
Date:
Hello David, 

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. 

Regards, 
Ming 

From: David Rowley <dgrowleyml@gmail.com>
Sent: Thursday, September 8, 2022 6:36:04 PM
To: Ming <ming@cleanstormwater.com.au>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres offset and limit bug
 
On Thu, 8 Sept 2022 at 19:20, Ming <ming@cleanstormwater.com.au> wrote:
> We recently found a strange behavior on Postgres SQL. The offset function will not acting properly when having limit in the query. I want to confirm that it's an existing bug or it's my syntax is wrong.

Nothing looks wrong from here. Can you describe what behaviour you're expecting?

David

Re: Postgres offset and limit bug

From
David Rowley
Date:
On Thu, 8 Sept 2022 at 20:49, Ming <ming@cleanstormwater.com.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’sordered by path, the of is actually ordered as well.) But without the limit 1 the results will be correct. And when
Iplus 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
willbe 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



Re: Postgres offset and limit bug

From
Ming
Date:
Thanks David~

From: David Rowley <dgrowleyml@gmail.com>
Sent: Thursday, September 8, 2022 7:03:33 PM
To: Ming <ming@cleanstormwater.com.au>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres offset and limit bug
 
On Thu, 8 Sept 2022 at 20:49, Ming <ming@cleanstormwater.com.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