Limit+Offset query wrong result in Postgres 9.0.3 ?

urkpostenardr wrote:

Is this bug in Postgres ?
If yes, is it fixed in latest release ?
Second query should return 2 rows instead of 1 ?

create table t(i int);
insert into t values(1);
insert into t values(2);
insert into t values(3);
pgdb=# select i from t order by i limit 9223372036854775806 offset 1;
select i from t order by i limit 9223372036854775806 offset 1;
i
2
3
(2 rows)
pgdb=# select i from t order by i limit 9223372036854775807 offset 1;
select i from t order by i limit 9223372036854775807 offset 1;
i
2
(1 row)
pgdb=#

My server Version is postgres (PostgreSQL) 9.0.3

That looks like a bug somewhere; at least on my 9.2.0 on Linux
I get the result that you would expect.

Which operating system and architecture is that?

Yours,
Laurenz Albe

On 12 October 2012 04:55, urkpostenardr <urkpostenardr@gmail.com> wrote:

Hi,

Is this bug in Postgres ?
If yes, is it fixed in latest release ?
Second query should return 2 rows instead of 1 ?

create table t(i int);
insert into t values(1);
insert into t values(2);
insert into t values(3);
pgdb=# select i from t order by i limit 9223372036854775806 offset 1;
select i from t order by i limit 9223372036854775806 offset 1;
i
2
3
(2 rows)
pgdb=# select i from t order by i limit 9223372036854775807 offset 1;
select i from t order by i limit 9223372036854775807 offset 1;
i
2
(1 row)
pgdb=#

You seem to have hit the end of a 32-bit signed integer and it wraps
around. There's probably some internal code that modifies limit-values
<1 to 1, or you wouldn't have gotten any results at all...

It does seem a fairly insane number to use for limit, it's probably
better to leave it out if you're going to accept that many results.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Fri, Oct 12, 2012 at 3:33 AM, Alban Hertroys <haramrae@gmail.com> wrote:

On 12 October 2012 04:55, urkpostenardr <urkpostenardr@gmail.com> wrote:

Hi,

Is this bug in Postgres ?
If yes, is it fixed in latest release ?
Second query should return 2 rows instead of 1 ?

create table t(i int);
insert into t values(1);
insert into t values(2);
insert into t values(3);
pgdb=# select i from t order by i limit 9223372036854775806 offset 1;
select i from t order by i limit 9223372036854775806 offset 1;
i
2
3
(2 rows)
pgdb=# select i from t order by i limit 9223372036854775807 offset 1;
select i from t order by i limit 9223372036854775807 offset 1;
i
2
(1 row)
pgdb=#

You seem to have hit the end of a 32-bit signed integer and it wraps
around. There's probably some internal code that modifies limit-values
<1 to 1, or you wouldn't have gotten any results at all...

It does seem a fairly insane number to use for limit, it's probably
better to leave it out if you're going to accept that many results.

This was previously reported as bug #6139, and fixed in 89df948ec26679e09.

Josh