Re: Limit+Offset query wrong result in Postgres 9.0.3 ?

On Fri, Oct 12, 2012 at 3:33 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
> On 12 October 2012 04:55, urkpostenardr <urkpostenardr(at)gmail(dot)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