The following bug has been logged on the website:
Bug reference: 13760
Logged by: digoal
Email address: digoal@126.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)
