Обсуждение: 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