Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcf516dc

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 asliterals. When this syntax was added all those years ago, it was doneinconsistently, with <x> and <y> being different subsets of thestandard syntax.Rectify that by making <x> and <y> accept the same thing, and allowingeither a (signed) numeric literal or a c_expr there, which allows forparameters, 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.ukDiscussion:http://postgr.es/m/152647780335.27204.16895288237122418685@wrigleys.postgresql.org
1 parent28782d7 commitcf516dc

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_argslimit_clauseselect_limit_value
449449
offset_clauseselect_offset_value
450-
select_offset_value2opt_select_fetch_first_value
450+
select_fetch_first_valueI_or_F_const
451451
%type<ival>row_or_rowsfirst_or_next
452452

453453
%type<list>OptSeqOptListSeqOptListOptParenthesizedSeqOptList
@@ -11185,15 +11185,23 @@ limit_clause:
1118511185
parser_errposition(@1)));
1118611186
}
1118711187
/* SQL:2008 syntax*/
11188-
|FETCHfirst_or_nextopt_select_fetch_first_valuerow_or_rowsONLY
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+
|FETCHfirst_or_nextselect_fetch_first_valuerow_or_rowsONLY
1118911195
{$$ =$3; }
11196+
|FETCHfirst_or_nextrow_or_rowsONLY
11197+
{$$ = makeIntConst(1, -1); }
1119011198
;
1119111199

1119211200
offset_clause:
1119311201
OFFSETselect_offset_value
1119411202
{$$ =$2; }
1119511203
/* SQL:2008 syntax*/
11196-
|OFFSETselect_offset_value2row_or_rows
11204+
|OFFSETselect_fetch_first_valuerow_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)
[8]ページ先頭
©2009-2025 Movatter.jp