Re: Behavior of negative OFFSET

Robert James <srobertjames(at)gmail(dot)com> writes:
> On 11/7/11, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Mon, Nov 7, 2011 at 3:47 PM, Robert James <srobertjames(at)gmail(dot)com> wrote:
>>> I've been using a query on Postgres 8.4 with a negative OFFSET, which
>>> works fine:
>>> SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name
>>> ASC LIMIT 15 OFFSET -15

>> the original behavior was undefined.

> What do it do in reality? I'm debugging a legacy app which used it.

It used to treat negative offsets/limits as zero.

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bfce56eea45b1369b7bb2150a150d1ac109f5073

> Also: Is there any reference in the docs to this? I wasn't able to find this.

The 8.4 release notes mention

* Disallow negative LIMIT or OFFSET values, rather than treating them as zero (Simon)

I'm pretty sure this changed in 8.4, not since then.

regards, tom lane