BUG #13760: order by . offset .. limit bug? when order by column has same value

From: digoal(at)126(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13760: order by . offset .. limit bug? when order by column has same value
Date: 2015-11-09 08:10:50
Message-ID: 20151109081050.2569.72184@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13760
Logged by: digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.4.5
Operating system: CentOS 6.x x64
Description:

when order by column has same values, there will return (0,1) all times
whatever offset x.

postgres=# create table t(id int);
CREATE TABLE
postgres=# insert into t select 1 from generate_series(1,500);
INSERT 0 500
postgres=# select row_number() over(),ctid,* from t order by id desc offset
0 limit 5;
row_number | ctid | id
------------+-------+----
2 | (0,2) | 1
3 | (0,3) | 1
4 | (0,4) | 1
5 | (0,5) | 1
1 | (0,1) | 1
(5 rows)

postgres=# select row_number() over(),ctid,* from t order by id desc offset
1 limit 5;
row_number | ctid | id
------------+-------+----
3 | (0,3) | 1
4 | (0,4) | 1
5 | (0,5) | 1
6 | (0,6) | 1
1 | (0,1) | 1
(5 rows)

postgres=# select row_number() over(),ctid,* from t order by id desc offset
100 limit 5;
row_number | ctid | id
------------+---------+----
102 | (0,102) | 1
103 | (0,103) | 1
104 | (0,104) | 1
105 | (0,105) | 1
1 | (0,1) | 1
(5 rows)

postgres=# explain select row_number() over(),ctid,* from t order by id desc
offset 100 limit 5;
QUERY PLAN
----------------------------------------------------------------------
Limit (cost=33.79..33.80 rows=5 width=10)
-> Sort (cost=33.54..34.79 rows=500 width=10)
Sort Key: id DESC
-> WindowAgg (cost=0.00..14.25 rows=500 width=10)
-> Seq Scan on t (cost=0.00..8.00 rows=500 width=10)
(5 rows)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message funnyxj 2015-11-09 10:41:25 BUG #13762: server will crash after superuser alter function and set client_encoding
Previous Message sumit_ghosh 2015-11-09 04:29:10 BUG #13759: Getting error while installing through Stackbuilder 3.1.1