Обсуждение: BUG #13760: order by . offset .. limit bug? when order by column has same value
BUG #13760: order by . offset .. limit bug? when order by column has same value
От
digoal@126.com
Дата:
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)
digoal@126.com writes: > 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