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

[d***@126.com]

The following bug has been logged on the website:

Bug reference: 13760
Logged by: digoal
Email address: ***@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)

[Tom Lane]

Post by d***@126.com
when order by column has same values, there will return (0,1) all times
whatever offset x.

I don't see any bug here. ORDER BY does not promise anything about
the ordering of rows with equal keys.

regards, tom lane