| Lists: | pgsql-general |
|---|
| From: | Robert James <srobertjames(at)gmail(dot)com> |
|---|---|
| To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Behavior of negative OFFSET |
| Date: | 2011-11-07 21:47:20 |
| Message-ID: | CAGYyBggx9_ajO3L5Lmr-6rNK238J+zZdxL=QFEarjTG17cRTtg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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?
| From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
|---|---|
| To: | Robert James <srobertjames(at)gmail(dot)com> |
| Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Behavior of negative OFFSET |
| Date: | 2011-11-07 22:04:40 |
| Message-ID: | CAHyXU0x1ovWSn1F3a7n3F3wW8Z0P7-knnOGCim7gFmvKCBX8RQ@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
>
> 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
| From: | Robert James <srobertjames(at)gmail(dot)com> |
|---|---|
| To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
| Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Behavior of negative OFFSET |
| Date: | 2011-11-08 00:08:19 |
| Message-ID: | CAGYyBggFn5JhC3XGfH411UTvSKdcHvCL+gf8KgL4ymv4-eYJ2w@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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.
> 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.
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Robert James <srobertjames(at)gmail(dot)com> |
| Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Behavior of negative OFFSET |
| Date: | 2011-11-08 04:30:46 |
| Message-ID: | 10194.1320726646@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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.
> 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