BUG #13760: order by . offset .. limit bug? when order by column has same value
От | digoal@126.com |
---|---|
Тема | BUG #13760: order by . offset .. limit bug? when order by column has same value |
Дата | |
Msg-id | 20151109081050.2569.72184@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #13760: order by . offset .. limit bug? when order by column has same value
|
Список | pgsql-bugs |
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)
В списке pgsql-bugs по дате отправления: