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

Commit3dd1310

Browse files
committed
Improve the error message given for modifying a window with frame clause.
For rather inscrutable reasons, SQL:2008 disallows copying-and-modifying awindow definition that has any explicit framing clause. The error messagewe gave for this only made sense if the referencing window definitionitself contains an explicit framing clause, which it might well not.Moreover, in the context of an OVER clause it's not exactly obvious that"OVER (windowname)" implies copy-and-modify while "OVER windowname" doesnot. This has led to multiple complaints, eg bug #5199 from IliyaKrapchatov. Change to a hopefully more intelligible error message, andin the case where we have just "OVER (windowname)", add a HINT suggestingthat omitting the parentheses will fix it. Also improve the relateddocumentation. Back-patch to all supported branches.
1 parentda174fb commit3dd1310

File tree

2 files changed

+36
-16
lines changed

2 files changed

+36
-16
lines changed

‎doc/src/sgml/syntax.sgml

Lines changed: 10 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1679,10 +1679,10 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
16791679
The syntax of a window function call is one of the following:
16801680

16811681
<synopsis>
1682-
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER ( <replaceable class="parameter">window_definition</replaceable> )
16831682
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER <replaceable>window_name</replaceable>
1684-
<replaceable>function_name</replaceable> ( *) OVER ( <replaceable class="parameter">window_definition</replaceable> )
1683+
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER ( <replaceable class="parameter">window_definition</replaceable> )
16851684
<replaceable>function_name</replaceable> ( * ) OVER <replaceable>window_name</replaceable>
1685+
<replaceable>function_name</replaceable> ( * ) OVER ( <replaceable class="parameter">window_definition</replaceable> )
16861686
</synopsis>
16871687
where <replaceable class="parameter">window_definition</replaceable>
16881688
has the syntax
@@ -1719,15 +1719,14 @@ UNBOUNDED FOLLOWING
17191719
names or numbers.
17201720
<replaceable>window_name</replaceable> is a reference to a named window
17211721
specification defined in the query's <literal>WINDOW</literal> clause.
1722-
Named window specifications are usually referenced with just
1723-
<literal>OVER</> <replaceable>window_name</replaceable>, but it is
1724-
also possible to write a window name inside the parentheses and then
1725-
optionally supply an ordering clause and/or frame clause (the referenced
1726-
window must lack these clauses, if they are supplied here).
1727-
This latter syntax follows the same rules as modifying an existing
1728-
window name within the <literal>WINDOW</literal> clause; see the
1729-
<xref linkend="sql-select"> reference
1730-
page for details.
1722+
Alternatively, a full <replaceable>window_definition</replaceable> can
1723+
be given within parentheses, using the same syntax as for defining a
1724+
named window in the <literal>WINDOW</literal> clause; see the
1725+
<xref linkend="sql-select"> reference page for details. It's worth
1726+
pointing out that <literal>OVER wname</> is not exactly equivalent to
1727+
<literal>OVER (wname)</>; the latter implies copying and modifying the
1728+
window definition, and will be rejected if the referenced window
1729+
specification includes a frame clause.
17311730
</para>
17321731

17331732
<para>

‎src/backend/parser/parse_clause.c

Lines changed: 26 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1648,11 +1648,16 @@ transformWindowDefinitions(ParseState *pstate,
16481648
/*
16491649
* Per spec, a windowdef that references a previous one copies the
16501650
* previous partition clause (and mustn't specify its own). It can
1651-
* specify its own ordering clause. but only if the previous one had
1651+
* specify its own ordering clause, but only if the previous one had
16521652
* none. It always specifies its own frame clause, and the previous
1653-
* one must not have a frame clause.(Yeah, it's bizarre that each of
1653+
* one must not have a frame clause. Yeah, it's bizarre that each of
16541654
* these cases works differently, but SQL:2008 says so; see 7.11
1655-
* <window clause> syntax rule 10 and general rule 1.)
1655+
* <window clause> syntax rule 10 and general rule 1. The frame
1656+
* clause rule is especially bizarre because it makes "OVER foo"
1657+
* different from "OVER (foo)", and requires the latter to throw an
1658+
* error if foo has a nondefault frame clause.Well, ours not to
1659+
* reason why, but we do go out of our way to throw a useful error
1660+
* message for such cases.
16561661
*/
16571662
if (refwc)
16581663
{
@@ -1691,11 +1696,27 @@ transformWindowDefinitions(ParseState *pstate,
16911696
wc->copiedOrder= false;
16921697
}
16931698
if (refwc&&refwc->frameOptions!=FRAMEOPTION_DEFAULTS)
1699+
{
1700+
/*
1701+
* Use this message if this is a WINDOW clause, or if it's an OVER
1702+
* clause that includes ORDER BY or framing clauses. (We already
1703+
* rejected PARTITION BY above, so no need to check that.)
1704+
*/
1705+
if (windef->name||
1706+
orderClause||windef->frameOptions!=FRAMEOPTION_DEFAULTS)
1707+
ereport(ERROR,
1708+
(errcode(ERRCODE_WINDOWING_ERROR),
1709+
errmsg("cannot copy window \"%s\" because it has a frame clause",
1710+
windef->refname),
1711+
parser_errposition(pstate,windef->location)));
1712+
/* Else this clause is just OVER (foo), so say this: */
16941713
ereport(ERROR,
16951714
(errcode(ERRCODE_WINDOWING_ERROR),
1696-
errmsg("cannot override frame clause of window \"%s\"",
1697-
windef->refname),
1715+
errmsg("cannot copy window \"%s\" because it has a frame clause",
1716+
windef->refname),
1717+
errhint("Omit the parentheses in this OVER clause."),
16981718
parser_errposition(pstate,windef->location)));
1719+
}
16991720
wc->frameOptions=windef->frameOptions;
17001721
/* Process frame offset expressions */
17011722
wc->startOffset=transformFrameOffset(pstate,wc->frameOptions,

0 commit comments

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