Thread: BUG #15200: Support ANSI OFFSET .. FETCH syntax with bind variables

The following bug has been logged on the website:

Bug reference:      15200
Logged by:          Lukas Eder
Email address:      lukas.eder@gmail.com
PostgreSQL version: 10.4
Operating system:   Windows
Description:

The manual states [1]:

> SQL:2008 introduced a different syntax to achieve the same result, which
PostgreSQL also supports. It is:
> 
> OFFSET start { ROW | ROWS }
> FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY
> 
> In this syntax, to write anything except a simple integer constant for
start or count, you must write parentheses around it.

And as shown in this Stack Overflow question [2], it can be shown that the
standard syntax doesn't work with anything but constant literals, including
bind variables (which to me, are a kind of constant literal). This is
regrettable, the workaround when using this syntax from Java is to write:

OFFSET (?) ROWS FETCH FIRST (?) ROWS ONLY

Instead of (as in other databases):

OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

This is also inconsistent with OFFSET .. LIMIT. The following works just
fine:

OFFSET ? LIMIT ?

I suggest relaxing this syntactic limitation and allowing for at least
constant literals AND bind variables in this syntax

[1] https://www.postgresql.org/docs/10/static/sql-select.html#SQL-LIMIT
[2] https://stackoverflow.com/q/50371757/521799

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> And as shown in this Stack Overflow question [2], it can be shown
 PG> that the standard syntax doesn't work with anything but constant
 PG> literals, including bind variables (which to me, are a kind of
 PG> constant literal).

This does seem to be a defect in the code - the comments in the parser
say that the spec only allows constants, which is clearly wrong (what
the spec actually allows is <simple value specification>, which is
either a literal or a parameter/host variable/embedded variable).

 PG> This is regrettable, the workaround when using this syntax from
 PG> Java is to write:

 PG> OFFSET (?) ROWS FETCH FIRST (?) ROWS ONLY

Having to use a workaround rather defeats the point of supporting
standard syntax in the first place. :-(

 PG> This is also inconsistent with OFFSET .. LIMIT. The following works
 PG> just fine:

 PG> OFFSET ? LIMIT ?

This syntax doesn't have trailing ROW or ROWS keywords to contend with,
so it can allow arbitrary expressions without confusing the parser.

 PG> I suggest relaxing this syntactic limitation and allowing for at
 PG> least constant literals AND bind variables in this syntax

I think this is fixable with a fairly simple tweak to gram.y. The tricky
part is that FETCH FIRST allows the expression to be omitted entirely,
which causes shift/reduce conflicts (if we see ROW, is that the first
token in the expression, or an empty expression?). But since ONLY is a
fully reserved word, I think this can be solved by splitting into two
productions (one with the expression and one without), since by the time
we need to decide whether to reduce ROW/ROWS to anything, we can see
whether ONLY is the lookahead token.

Going to poke at this a bit to see if I can make it work right.

-- 
Andrew (irc:RhodiumToad)