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

Commitdb157fb

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 parent4c412ca commitdb157fb

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
@@ -1709,10 +1709,10 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
17091709
The syntax of a window function call is one of the following:
17101710

17111711
<synopsis>
1712-
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER ( <replaceable class="parameter">window_definition</replaceable> )
17131712
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER <replaceable>window_name</replaceable>
1714-
<replaceable>function_name</replaceable> ( *) OVER ( <replaceable class="parameter">window_definition</replaceable> )
1713+
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER ( <replaceable class="parameter">window_definition</replaceable> )
17151714
<replaceable>function_name</replaceable> ( * ) OVER <replaceable>window_name</replaceable>
1715+
<replaceable>function_name</replaceable> ( * ) OVER ( <replaceable class="parameter">window_definition</replaceable> )
17161716
</synopsis>
17171717
where <replaceable class="parameter">window_definition</replaceable>
17181718
has the syntax
@@ -1749,15 +1749,14 @@ UNBOUNDED FOLLOWING
17491749
names or numbers.
17501750
<replaceable>window_name</replaceable> is a reference to a named window
17511751
specification defined in the query's <literal>WINDOW</literal> clause.
1752-
Named window specifications are usually referenced with just
1753-
<literal>OVER</> <replaceable>window_name</replaceable>, but it is
1754-
also possible to write a window name inside the parentheses and then
1755-
optionally supply an ordering clause and/or frame clause (the referenced
1756-
window must lack these clauses, if they are supplied here).
1757-
This latter syntax follows the same rules as modifying an existing
1758-
window name within the <literal>WINDOW</literal> clause; see the
1759-
<xref linkend="sql-select"> reference
1760-
page for details.
1752+
Alternatively, a full <replaceable>window_definition</replaceable> can
1753+
be given within parentheses, using the same syntax as for defining a
1754+
named window in the <literal>WINDOW</literal> clause; see the
1755+
<xref linkend="sql-select"> reference page for details. It's worth
1756+
pointing out that <literal>OVER wname</> is not exactly equivalent to
1757+
<literal>OVER (wname)</>; the latter implies copying and modifying the
1758+
window definition, and will be rejected if the referenced window
1759+
specification includes a frame clause.
17611760
</para>
17621761

17631762
<para>

‎src/backend/parser/parse_clause.c

Lines changed: 26 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1665,11 +1665,16 @@ transformWindowDefinitions(ParseState *pstate,
16651665
/*
16661666
* Per spec, a windowdef that references a previous one copies the
16671667
* previous partition clause (and mustn't specify its own). It can
1668-
* specify its own ordering clause. but only if the previous one had
1668+
* specify its own ordering clause, but only if the previous one had
16691669
* none. It always specifies its own frame clause, and the previous
1670-
* one must not have a frame clause.(Yeah, it's bizarre that each of
1670+
* one must not have a frame clause. Yeah, it's bizarre that each of
16711671
* these cases works differently, but SQL:2008 says so; see 7.11
1672-
* <window clause> syntax rule 10 and general rule 1.)
1672+
* <window clause> syntax rule 10 and general rule 1. The frame
1673+
* clause rule is especially bizarre because it makes "OVER foo"
1674+
* different from "OVER (foo)", and requires the latter to throw an
1675+
* error if foo has a nondefault frame clause.Well, ours not to
1676+
* reason why, but we do go out of our way to throw a useful error
1677+
* message for such cases.
16731678
*/
16741679
if (refwc)
16751680
{
@@ -1708,11 +1713,27 @@ transformWindowDefinitions(ParseState *pstate,
17081713
wc->copiedOrder= false;
17091714
}
17101715
if (refwc&&refwc->frameOptions!=FRAMEOPTION_DEFAULTS)
1716+
{
1717+
/*
1718+
* Use this message if this is a WINDOW clause, or if it's an OVER
1719+
* clause that includes ORDER BY or framing clauses. (We already
1720+
* rejected PARTITION BY above, so no need to check that.)
1721+
*/
1722+
if (windef->name||
1723+
orderClause||windef->frameOptions!=FRAMEOPTION_DEFAULTS)
1724+
ereport(ERROR,
1725+
(errcode(ERRCODE_WINDOWING_ERROR),
1726+
errmsg("cannot copy window \"%s\" because it has a frame clause",
1727+
windef->refname),
1728+
parser_errposition(pstate,windef->location)));
1729+
/* Else this clause is just OVER (foo), so say this: */
17111730
ereport(ERROR,
17121731
(errcode(ERRCODE_WINDOWING_ERROR),
1713-
errmsg("cannot override frame clause of window \"%s\"",
1714-
windef->refname),
1732+
errmsg("cannot copy window \"%s\" because it has a frame clause",
1733+
windef->refname),
1734+
errhint("Omit the parentheses in this OVER clause."),
17151735
parser_errposition(pstate,windef->location)));
1736+
}
17161737
wc->frameOptions=windef->frameOptions;
17171738
/* Process frame offset expressions */
17181739
wc->startOffset=transformFrameOffset(pstate,wc->frameOptions,

0 commit comments

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