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

Commit7a0aa8d

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 parent1545ca9 commit7a0aa8d

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
@@ -419,7 +419,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
419419

420420
%type<node>fetch_argslimit_clauseselect_limit_value
421421
offset_clauseselect_offset_value
422-
select_offset_value2opt_select_fetch_first_value
422+
select_fetch_first_valueI_or_F_const
423423
%type<ival>row_or_rowsfirst_or_next
424424

425425
%type<list>OptSeqOptListSeqOptList
@@ -10414,15 +10414,23 @@ limit_clause:
1041410414
parser_errposition(@1)));
1041510415
}
1041610416
/* SQL:2008 syntax*/
10417-
|FETCHfirst_or_nextopt_select_fetch_first_valuerow_or_rowsONLY
10417+
/* to avoid shift/reduce conflicts, handle the optional value with
10418+
* a separate production rather than an opt_ expression. The fact
10419+
* that ONLY is fully reserved means that this way, we defer any
10420+
* decision about what rule reduces ROW or ROWS to the point where
10421+
* we can see the ONLY token in the lookahead slot.
10422+
*/
10423+
|FETCHfirst_or_nextselect_fetch_first_valuerow_or_rowsONLY
1041810424
{$$ =$3; }
10425+
|FETCHfirst_or_nextrow_or_rowsONLY
10426+
{$$ = makeIntConst(1, -1); }
1041910427
;
1042010428

1042110429
offset_clause:
1042210430
OFFSETselect_offset_value
1042310431
{$$ =$2; }
1042410432
/* SQL:2008 syntax*/
10425-
|OFFSETselect_offset_value2row_or_rows
10433+
|OFFSETselect_fetch_first_valuerow_or_rows
1042610434
{$$ =$2; }
1042710435
;
1042810436

@@ -10441,22 +10449,31 @@ select_offset_value:
1044110449

1044210450
/*
1044310451
* Allowing full expressions without parentheses causes various parsing
10444-
* problems with the trailing ROW/ROWS key words. SQL only calls for
10445-
* constants, so we allow the rest only with parentheses. If omitted,
10446-
* default to 1.
10452+
* problems with the trailing ROW/ROWS key words. SQL spec only calls for
10453+
* <simple value specification>, which is either a literal or a parameter (but
10454+
* an <SQL parameter reference> could be an identifier, bringing up conflicts
10455+
* with ROW/ROWS). We solve this by leveraging the presence of ONLY (see above)
10456+
* to determine whether the expression is missing rather than trying to make it
10457+
* optional in this rule.
10458+
*
10459+
* c_expr covers almost all the spec-required cases (and more), but it doesn't
10460+
* cover signed numeric literals, which are allowed by the spec. So we include
10461+
* those here explicitly. We need FCONST as well as ICONST because values that
10462+
* don't fit in the platform's "long", but do fit in bigint, should still be
10463+
* accepted here. (This is possible in 64-bit Windows as well as all 32-bit
10464+
* builds.)
1044710465
*/
10448-
opt_select_fetch_first_value:
10449-
SignedIconst{$$ = makeIntConst($1,@1); }
10450-
|'('a_expr')'{$$ =$2; }
10451-
|/*EMPTY*/{$$ = makeIntConst(1, -1); }
10466+
select_fetch_first_value:
10467+
c_expr{$$ =$1; }
10468+
|'+'I_or_F_const
10469+
{$$ = (Node *) makeSimpleA_Expr(AEXPR_OP,"+",NULL,$2,@1); }
10470+
|'-'I_or_F_const
10471+
{$$ = doNegate($2,@1); }
1045210472
;
1045310473

10454-
/*
10455-
* Again, the trailing ROW/ROWS in this case prevent the full expression
10456-
* syntax. c_expr is the best we can do.
10457-
*/
10458-
select_offset_value2:
10459-
c_expr{$$ =$1; }
10474+
I_or_F_const:
10475+
Iconst{$$ = makeIntConst($1,@1); }
10476+
|FCONST{$$ = makeFloatConst($1,@1); }
1046010477
;
1046110478

1046210479
/* noise words*/

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp