Обсуждение: Behavior of negative OFFSET

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?

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