Behavior of negative OFFSET

On Mon, Nov 7, 2011 at 3:47 PM, Robert James <srobertjames@gmail.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

When I run the same query on Postgres 9.1, I get an error:
  ERROR: OFFSET must not be negative

Question:
1. Was this changed in a version of Postgres? Is this configurable?
2. How do I get the original behavior of negative offsets?

the original behavior was undefined. to kinda sorta get it,
create function oldoffset(int) returns int as
$$
select case when $1 < 0 then 0 else $1 end;
$$ language sql immutable;

select v from generate_series(1,15) v limit 15 offset oldoffset(-15);

merlin

On 11/7/11, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Nov 7, 2011 at 3:47 PM, Robert James <srobertjames@gmail.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.

to kinda sorta get it,
create function oldoffset(int) returns int as
$$
select case when $1 < 0 then 0 else $1 end;
$$ language sql immutable;

select v from generate_series(1,15) v limit 15 offset oldoffset(-15);

That sounds like if OFFSET was negative, it would be simply ignored.
Is that correct? When was the behavior of OFFSET changed?

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

Robert James <srobertjames@gmail.com> writes:

On 11/7/11, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Nov 7, 2011 at 3:47 PM, Robert James <srobertjames@gmail.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