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)
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 |