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

Commitdf0ea5a

Browse files
committed
Throw error if a <window definition> references a window that already has a
frame clause, as appears to be required by the fine print in the SQL spec.Per discussion with Pavel, not doing so risks user confusion.
1 parenteb27374 commitdf0ea5a

File tree

3 files changed

+29
-14
lines changed

3 files changed

+29
-14
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.115 2008/12/3100:08:35 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.116 2008/12/3123:42:56 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -583,7 +583,7 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
583583
[ <replaceable class="parameter">existing_window_name</replaceable> ]
584584
[ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
585585
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
586-
[ <replaceable class="parameter">framing_clause</replaceable> ]
586+
[ <replaceable class="parameter">frame_clause</replaceable> ]
587587
</synopsis>
588588
</para>
589589

@@ -594,7 +594,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
594594
as well as its ordering clause if any. In this case the new window cannot
595595
specify its own <literal>PARTITION BY</> clause, and it can specify
596596
<literal>ORDER BY</> only if the copied window does not have one.
597-
The framing clause is never copied from the existing window.
597+
The new window always uses its own frame clause; the copied window
598+
must not specify a frame clause.
598599
</para>
599600

600601
<para>
@@ -611,7 +612,7 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
611612
</para>
612613

613614
<para>
614-
The optional <replaceable class="parameter">framing_clause</> defines
615+
The optional <replaceable class="parameter">frame_clause</> defines
615616
the <firstterm>window frame</> for window functions that depend on the
616617
frame (not all do). It can be one of
617618
<synopsis>
@@ -1486,7 +1487,7 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
14861487

14871488
<para>
14881489
The SQL standard provides additional options for the window
1489-
<replaceable class="parameter">framing_clause</>.
1490+
<replaceable class="parameter">frame_clause</>.
14901491
<productname>PostgreSQL</productname> currently supports only the
14911492
options listed above.
14921493
</para>

‎doc/src/sgml/syntax.sgml

Lines changed: 12 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.128 2008/12/3100:08:35 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.129 2008/12/3123:42:56 tgl Exp $ -->
22

33
<chapter id="sql-syntax">
44
<title>SQL Syntax</title>
@@ -1588,12 +1588,12 @@ sqrt(2)
15881588
where <replaceable class="parameter">window_definition</replaceable>
15891589
has the syntax
15901590
<synopsis>
1591-
[ <replaceable class="parameter">window_name</replaceable> ]
1591+
[ <replaceable class="parameter">existing_window_name</replaceable> ]
15921592
[ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
15931593
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
1594-
[ <replaceable class="parameter">framing_clause</replaceable> ]
1594+
[ <replaceable class="parameter">frame_clause</replaceable> ]
15951595
</synopsis>
1596-
and the optional <replaceable class="parameter">framing_clause</replaceable>
1596+
and the optional <replaceable class="parameter">frame_clause</replaceable>
15971597
can be one of
15981598
<synopsis>
15991599
RANGE UNBOUNDED PRECEDING
@@ -1614,14 +1614,18 @@ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
16141614
Named window specifications are usually referenced with just
16151615
<literal>OVER</> <replaceable>window_name</replaceable>, but it is
16161616
also possible to write a window name inside the parentheses and then
1617-
optionally override its ordering clause and/or framing clause.
1617+
optionally supply an ordering clause and/or frame clause (the referenced
1618+
window must lack these clauses, if they are supplied here).
16181619
This latter syntax follows the same rules as modifying an existing
16191620
window name within the <literal>WINDOW</literal> clause; see the
16201621
<xref linkend="sql-select" endterm="sql-select-title"> reference
16211622
page for details.
16221623
</para>
16231624

16241625
<para>
1626+
The <replaceable class="parameter">frame_clause</replaceable> specifies
1627+
the set of rows constituting the <firstterm>window frame</>, for those
1628+
window functions that act on the frame instead of the whole partition.
16251629
The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
16261630
which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
16271631
CURRENT ROW</>; it selects rows up through the current row's last
@@ -1639,8 +1643,9 @@ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
16391643

16401644
<para>
16411645
The built-in window functions are described in <xref
1642-
linkend="functions-window-table">. Also, any built-in or
1643-
user-defined aggregate function can be used as a window function.
1646+
linkend="functions-window-table">. Other window functions can be added by
1647+
the user. Also, any built-in or user-defined aggregate function can be
1648+
used as a window function.
16441649
</para>
16451650

16461651
<para>

‎src/backend/parser/parse_clause.c

Lines changed: 11 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.183 2008/12/3100:08:37 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.184 2008/12/3123:42:56 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1554,7 +1554,10 @@ transformWindowDefinitions(ParseState *pstate,
15541554
* Per spec, a windowdef that references a previous one copies the
15551555
* previous partition clause (and mustn't specify its own). It can
15561556
* specify its own ordering clause. but only if the previous one
1557-
* had none. It always specifies its own framing clause.
1557+
* had none. It always specifies its own frame clause, and the
1558+
* previous one must not have a frame clause. (Yeah, it's bizarre
1559+
* that each of these cases works differently, but SQL:2008 says so;
1560+
* see 7.11 <window clause> syntax rule 10 and general rule 1.)
15581561
*/
15591562
if (refwc)
15601563
{
@@ -1592,6 +1595,12 @@ transformWindowDefinitions(ParseState *pstate,
15921595
wc->orderClause=orderClause;
15931596
wc->copiedOrder= false;
15941597
}
1598+
if (refwc&&refwc->frameOptions!=FRAMEOPTION_DEFAULTS)
1599+
ereport(ERROR,
1600+
(errcode(ERRCODE_WINDOWING_ERROR),
1601+
errmsg("cannot override frame clause of window \"%s\"",
1602+
windef->refname),
1603+
parser_errposition(pstate,windef->location)));
15951604
wc->frameOptions=windef->frameOptions;
15961605
wc->winref=winref;
15971606

0 commit comments

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