Skip to content

Commit cf516dc

Browse files
committed
Fix SQL:2008 FETCH FIRST syntax to allow parameters.
OFFSET <x> ROWS FETCH FIRST <y> ROWS ONLY syntax is supposed to accept <simple value specification>, which includes parameters as well as literals. When this syntax was added all those years ago, it was done inconsistently, with <x> and <y> being different subsets of the standard syntax. Rectify that by making <x> and <y> accept the same thing, and allowing either a (signed) numeric literal or a c_expr there, which allows for parameters, variables, and parenthesized arbitrary expressions. Per bug #15200 from Lukas Eder. Backpatch all the way, since this has been broken from the start. Discussion: https://postgr.es/m/877enz476l.fsf@news-spur.riddles.org.uk Discussion: http://postgr.es/m/152647780335.27204.16895288237122418685@wrigleys.postgresql.org
1 parent 28782d7 commit cf516dc

File tree

2 files changed

+41
-22
lines changed

2 files changed

+41
-22
lines changed

doc/src/sgml/ref/select.sgml

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1349,12 +1349,14 @@ OFFSET <replaceable class="parameter">start</replaceable>
13491349
OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
13501350
FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
13511351
</synopsis>
1352-
In this syntax, to write anything except a simple integer constant for
1353-
<replaceable class="parameter">start</> or <replaceable
1354-
class="parameter">count</replaceable>, you must write parentheses
1355-
around it.
1356-
If <replaceable class="parameter">count</> is
1357-
omitted in a <literal>FETCH</> clause, it defaults to 1.
1352+
In this syntax, the <replaceable class="parameter">start</replaceable>
1353+
or <replaceable class="parameter">count</replaceable> value is required by
1354+
the standard to be a literal constant, a parameter, or a variable name;
1355+
as a <productname>PostgreSQL</productname> extension, other expressions
1356+
are allowed, but will generally need to be enclosed in parentheses to avoid
1357+
ambiguity.
1358+
If <replaceable class="parameter">count</replaceable> is
1359+
omitted in a <literal>FETCH</literal> clause, it defaults to 1.
13581360
<literal>ROW</literal>
13591361
and <literal>ROWS</literal> as well as <literal>FIRST</literal>
13601362
and <literal>NEXT</literal> are noise words that don't influence

src/backend/parser/gram.y

Lines changed: 33 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -447,7 +447,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
447447

448448
%type <node> fetch_args limit_clause select_limit_value
449449
offset_clause select_offset_value
450-
select_offset_value2 opt_select_fetch_first_value
450+
select_fetch_first_value I_or_F_const
451451
%type <ival> row_or_rows first_or_next
452452

453453
%type <list> OptSeqOptList SeqOptList OptParenthesizedSeqOptList
@@ -11185,15 +11185,23 @@ limit_clause:
1118511185
parser_errposition(@1)));
1118611186
}
1118711187
/* SQL:2008 syntax */
11188-
| FETCH first_or_next opt_select_fetch_first_value row_or_rows ONLY
11188+
/* to avoid shift/reduce conflicts, handle the optional value with
11189+
* a separate production rather than an opt_ expression. The fact
11190+
* that ONLY is fully reserved means that this way, we defer any
11191+
* decision about what rule reduces ROW or ROWS to the point where
11192+
* we can see the ONLY token in the lookahead slot.
11193+
*/
11194+
| FETCH first_or_next select_fetch_first_value row_or_rows ONLY
1118911195
{ $$ = $3; }
11196+
| FETCH first_or_next row_or_rows ONLY
11197+
{ $$ = makeIntConst(1, -1); }
1119011198
;
1119111199

1119211200
offset_clause:
1119311201
OFFSET select_offset_value
1119411202
{ $$ = $2; }
1119511203
/* SQL:2008 syntax */
11196-
| OFFSET select_offset_value2 row_or_rows
11204+
| OFFSET select_fetch_first_value row_or_rows
1119711205
{ $$ = $2; }
1119811206
;
1119911207

@@ -11212,22 +11220,31 @@ select_offset_value:
1121211220

1121311221
/*
1121411222
* Allowing full expressions without parentheses causes various parsing
11215-
* problems with the trailing ROW/ROWS key words. SQL only calls for
11216-
* constants, so we allow the rest only with parentheses. If omitted,
11217-
* default to 1.
11223+
* problems with the trailing ROW/ROWS key words. SQL spec only calls for
11224+
* <simple value specification>, which is either a literal or a parameter (but
11225+
* an <SQL parameter reference> could be an identifier, bringing up conflicts
11226+
* with ROW/ROWS). We solve this by leveraging the presence of ONLY (see above)
11227+
* to determine whether the expression is missing rather than trying to make it
11228+
* optional in this rule.
11229+
*
11230+
* c_expr covers almost all the spec-required cases (and more), but it doesn't
11231+
* cover signed numeric literals, which are allowed by the spec. So we include
11232+
* those here explicitly. We need FCONST as well as ICONST because values that
11233+
* don't fit in the platform's "long", but do fit in bigint, should still be
11234+
* accepted here. (This is possible in 64-bit Windows as well as all 32-bit
11235+
* builds.)
1121811236
*/
11219-
opt_select_fetch_first_value:
11220-
SignedIconst { $$ = makeIntConst($1, @1); }
11221-
| '(' a_expr ')' { $$ = $2; }
11222-
| /*EMPTY*/ { $$ = makeIntConst(1, -1); }
11237+
select_fetch_first_value:
11238+
c_expr { $$ = $1; }
11239+
| '+' I_or_F_const
11240+
{ $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "+", NULL, $2, @1); }
11241+
| '-' I_or_F_const
11242+
{ $$ = doNegate($2, @1); }
1122311243
;
1122411244

11225-
/*
11226-
* Again, the trailing ROW/ROWS in this case prevent the full expression
11227-
* syntax. c_expr is the best we can do.
11228-
*/
11229-
select_offset_value2:
11230-
c_expr { $$ = $1; }
11245+
I_or_F_const:
11246+
Iconst { $$ = makeIntConst($1,@1); }
11247+
| FCONST { $$ = makeFloatConst($1,@1); }
1123111248
;
1123211249

1123311250
/* noise words */

0 commit comments

Comments
 (0)