BUG #6315: FETCH NEXT :next ROWS ONLY fails

2011-12-01 20:09 keltezéssel, rep.dot.nop@gmail.com írta:

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:

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.

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 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 &quot;OFFSET :offset&quot; works fine but binding for a &quot;FETCH NEXT :next
ROWS ONLY&quot; raises:
syntax error at or near &quot;$2&quot;
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éssel, rep.dot.nop@gmail.com írta:

Binding for &quot;OFFSET :offset&quot; works fine but binding for a &quot;FETCH NEXT :next
ROWS ONLY&quot; raises:
syntax error at or near &quot;$2&quot;
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.

Hmm, it works with parens only in the "fetch next" clause, they don't
seem necessary in the limit. FWIW.

alvherre=# prepare foo(int, int) as select * from generate_series(1, 200) offset $1 fetch next ($2) rows only;
PREPARE
alvherre=# execute foo(2+3, 1+2);
generate_series
-----------------
6
7
8
(3 filas)

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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éssel, rep.dot.nop@gmail.com írta:

Binding for &quot;OFFSET :offset&quot; works fine but binding for a &quot;FETCH NEXT :next
ROWS ONLY&quot; raises:
syntax error at or near &quot;$2&quot;
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.  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.

Exactly. That's why I find the need to quote the latter a bit counter
intuitive :)
cheers,

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