Thread: BUG #6315: FETCH NEXT :next ROWS ONLY fails
The following bug has been logged on the website: Bug reference: 6315 Logged by: Bernhard Reutner-Fischer Email address: rep.dot.nop@gmail.com PostgreSQL version: 9.1.1 Operating system: linux Description:=20=20=20=20=20=20=20=20 Binding for "OFFSET :offset" works fine but binding for a "F= ETCH NEXT :next ROWS ONLY" raises: syntax error at or near "$2" SELECT * FROM foo OFFSET $1 ROWS FETCH NEXT $2 ROWS ONLY
2011-12-01 20:09 keltez=C3=A9ssel, rep.dot.nop@gmail.com =C3=ADrta: > The following bug has been logged on the website: > > Bug reference: 6315 > Logged by: Bernhard Reutner-Fischer > Email address: rep.dot.nop@gmail.com > PostgreSQL version: 9.1.1 > Operating system: linux > Description:=20=20=20=20=20=20=20=20 > > Binding for "OFFSET :offset" works fine but binding for a "= ;FETCH NEXT :next > ROWS ONLY" raises: > syntax error at or near "$2" > SELECT * FROM foo OFFSET $1 ROWS FETCH NEXT $2 ROWS ONLY This is not a supported syntax. Consider using SELECT * FROM foo OFFSET $1 LIMIT $2 instead. --=20 ---------------------------------- Zolt=C3=A1n B=C3=B6sz=C3=B6rm=C3=A9nyi Cybertec Sch=C3=B6nig & Sch=C3=B6nig GmbH Gr=C3=B6hrm=C3=BChlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/
Boszormenyi Zoltan <zb@cybertec.at> writes:
> 2011-12-01 20:09 keltezéssel, rep.dot.nop@gmail.com Ãrta:
>> Binding for "OFFSET :offset" works fine but binding for a "FETCH NEXT :next
>> ROWS ONLY" raises:
>> syntax error at or near "$2"
>> SELECT * FROM foo OFFSET $1 ROWS FETCH NEXT $2 ROWS ONLY
> This is not a supported syntax. Consider using
> SELECT * FROM foo OFFSET $1 LIMIT $2
> instead.
Well, actually it is supported, but you missed the fine print where it
says that you have to add parentheses if the offset or count isn't a
simple integer constant. I'll apply a patch to make that a bit more
obvious.
regards, tom lane
Excerpts from Tom Lane's message of jue dic 01 18:32:58 -0300 2011:
> Boszormenyi Zoltan <zb@cybertec.at> writes:
> > 2011-12-01 20:09 keltez=C3=A9ssel, rep.dot.nop@gmail.com =C3=ADrta:
> >> Binding for "OFFSET :offset" works fine but binding for a &q=
uot;FETCH NEXT :next
> >> ROWS ONLY" raises:
> >> syntax error at or near "$2"
> >> SELECT * FROM foo OFFSET $1 ROWS FETCH NEXT $2 ROWS ONLY
>=20
> > This is not a supported syntax. Consider using
> > SELECT * FROM foo OFFSET $1 LIMIT $2
> > instead.
>=20
> Well, actually it is supported, but you missed the fine print where it
> says that you have to add parentheses if the offset or count isn't a
> simple integer constant. I'll apply a patch to make that a bit more
> obvious.
Hmm, it works with parens only in the "fetch next" clause, they don't
seem necessary in the limit. FWIW.
alvherre=3D# prepare foo(int, int) as select * from generate_series(1, 200)=
offset $1 fetch next ($2) rows only;
PREPARE
alvherre=3D# execute foo(2+3, 1+2);
generate_series=20
-----------------
6
7
8
(3 filas)
--=20
=C3=81lvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Tom Lane's message of jue dic 01 18:32:58 -0300 2011:
>> Well, actually it is supported, but you missed the fine print where it
>> says that you have to add parentheses if the offset or count isn't a
>> simple integer constant. I'll apply a patch to make that a bit more
>> obvious.
> Hmm, it works with parens only in the "fetch next" clause, they don't
> seem necessary in the limit. FWIW.
ITYM "offset"? You do need the parens if you want to spell it
SQL:2008's way, with the ROW/ROWS noise word.
regression=# select * from int8_tbl offset 2+2;
q1 | q2
------------------+-------------------
4567890123456789 | -4567890123456789
(1 row)
regression=# select * from int8_tbl offset 2+2 rows;
ERROR: syntax error at or near "rows"
LINE 1: select * from int8_tbl offset 2+2 rows;
^
regression=# select * from int8_tbl offset (2+2) rows;
q1 | q2
------------------+-------------------
4567890123456789 | -4567890123456789
(1 row)
The comment in gram.y says there are parsing conflicts if we try to not
require the parens, and that SQL:2008 doesn't actually require anything
beyond a simple integer constant here.
regards, tom lane
On 1 December 2011 22:50, Alvaro Herrera <alvherre@commandprompt.com> wrote: > > Excerpts from Tom Lane's message of jue dic 01 18:32:58 -0300 2011: >> Boszormenyi Zoltan <zb@cybertec.at> writes: >> > 2011-12-01 20:09 keltez=E9ssel, rep.dot.nop@gmail.com =EDrta: >> >> Binding for "OFFSET :offset" works fine but binding for a &= quot;FETCH NEXT :next >> >> ROWS ONLY" raises: >> >> syntax error at or near "$2" >> >> SELECT * FROM foo OFFSET $1 ROWS FETCH NEXT $2 ROWS ONLY >> >> > This is not a supported syntax. Consider using >> > SELECT * FROM foo OFFSET $1 LIMIT $2 >> > instead. I think it's SQL2008 and is the "new" way of stating OFFSET $1 LIMIT $2 (see docs). >> >> Well, actually it is supported, but you missed the fine print where it >> says that you have to add parentheses if the offset or count isn't a >> simple integer constant. =A0I'll apply a patch to make that a bit more >> obvious. > > Hmm, it works with parens only in the "fetch next" clause, they don't > seem necessary in the limit. =A0FWIW. Exactly. That's why I find the need to quote the latter a bit counter intuitive :) cheers, > > alvherre=3D# prepare foo(int, int) as select * from generate_series(1, 20= 0) offset $1 fetch next ($2) rows only; > PREPARE > alvherre=3D# execute foo(2+3, 1+2); > =A0generate_series > ----------------- > =A0 =A0 =A0 =A0 =A0 =A0 =A0 6 > =A0 =A0 =A0 =A0 =A0 =A0 =A0 7 > =A0 =A0 =A0 =A0 =A0 =A0 =A0 8 > (3 filas)