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

Commit920c826

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 parentd4e6133 commit920c826

File tree

2 files changed

+45
-18
lines changed

2 files changed

+45
-18
lines changed

‎doc/src/sgml/syntax.sgml

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

17321732
<synopsis>
1733-
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
17341733
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
1735-
<replaceable>function_name</replaceable> ( *) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
1734+
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
17361735
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
1736+
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
17371737
</synopsis>
17381738
where <replaceable class="parameter">window_definition</replaceable>
17391739
has the syntax
@@ -1768,15 +1768,14 @@ UNBOUNDED FOLLOWING
17681768
<para>
17691769
<replaceable>window_name</replaceable> is a reference to a named window
17701770
specification defined in the query's <literal>WINDOW</literal> clause.
1771-
Named window specifications are usually referenced with just
1772-
<literal>OVER</> <replaceable>window_name</replaceable>, but it is
1773-
also possible to write a window name inside the parentheses and then
1774-
optionally supply an ordering clause and/or frame clause (the referenced
1775-
window must lack these clauses, if they are supplied here).
1776-
This latter syntax follows the same rules as modifying an existing
1777-
window name within the <literal>WINDOW</literal> clause; see the
1778-
<xref linkend="sql-select"> reference
1779-
page for details.
1771+
Alternatively, a full <replaceable>window_definition</replaceable> can
1772+
be given within parentheses, using the same syntax as for defining a
1773+
named window in the <literal>WINDOW</literal> clause; see the
1774+
<xref linkend="sql-select"> reference page for details. It's worth
1775+
pointing out that <literal>OVER wname</> is not exactly equivalent to
1776+
<literal>OVER (wname)</>; the latter implies copying and modifying the
1777+
window definition, and will be rejected if the referenced window
1778+
specification includes a frame clause.
17801779
</para>
17811780

17821781
<para>
@@ -1853,12 +1852,19 @@ UNBOUNDED FOLLOWING
18531852
PRECEDING</literal> is not allowed.
18541853
</para>
18551854

1855+
<para>
1856+
If <literal>FILTER</literal> is specified, then only the input
1857+
rows for which the <replaceable>filter_clause</replaceable>
1858+
evaluates to true are fed to the window function; other rows
1859+
are discarded. Only aggregate window functions accept
1860+
a <literal>FILTER</literal> clause.
1861+
</para>
1862+
18561863
<para>
18571864
The built-in window functions are described in <xref
18581865
linkend="functions-window-table">. Other window functions can be added by
18591866
the user. Also, any built-in or user-defined aggregate function can be
1860-
used as a window function. Only aggregate window functions accept
1861-
a <literal>FILTER</literal> clause.
1867+
used as a window function.
18621868
</para>
18631869

18641870
<para>

‎src/backend/parser/parse_clause.c

Lines changed: 26 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1735,11 +1735,16 @@ transformWindowDefinitions(ParseState *pstate,
17351735
/*
17361736
* Per spec, a windowdef that references a previous one copies the
17371737
* previous partition clause (and mustn't specify its own). It can
1738-
* specify its own ordering clause. but only if the previous one had
1738+
* specify its own ordering clause, but only if the previous one had
17391739
* none. It always specifies its own frame clause, and the previous
1740-
* one must not have a frame clause.(Yeah, it's bizarre that each of
1740+
* one must not have a frame clause. Yeah, it's bizarre that each of
17411741
* these cases works differently, but SQL:2008 says so; see 7.11
1742-
* <window clause> syntax rule 10 and general rule 1.)
1742+
* <window clause> syntax rule 10 and general rule 1. The frame
1743+
* clause rule is especially bizarre because it makes "OVER foo"
1744+
* different from "OVER (foo)", and requires the latter to throw an
1745+
* error if foo has a nondefault frame clause.Well, ours not to
1746+
* reason why, but we do go out of our way to throw a useful error
1747+
* message for such cases.
17431748
*/
17441749
if (refwc)
17451750
{
@@ -1778,11 +1783,27 @@ transformWindowDefinitions(ParseState *pstate,
17781783
wc->copiedOrder= false;
17791784
}
17801785
if (refwc&&refwc->frameOptions!=FRAMEOPTION_DEFAULTS)
1786+
{
1787+
/*
1788+
* Use this message if this is a WINDOW clause, or if it's an OVER
1789+
* clause that includes ORDER BY or framing clauses. (We already
1790+
* rejected PARTITION BY above, so no need to check that.)
1791+
*/
1792+
if (windef->name||
1793+
orderClause||windef->frameOptions!=FRAMEOPTION_DEFAULTS)
1794+
ereport(ERROR,
1795+
(errcode(ERRCODE_WINDOWING_ERROR),
1796+
errmsg("cannot copy window \"%s\" because it has a frame clause",
1797+
windef->refname),
1798+
parser_errposition(pstate,windef->location)));
1799+
/* Else this clause is just OVER (foo), so say this: */
17811800
ereport(ERROR,
17821801
(errcode(ERRCODE_WINDOWING_ERROR),
1783-
errmsg("cannot override frame clause of window \"%s\"",
1784-
windef->refname),
1802+
errmsg("cannot copy window \"%s\" because it has a frame clause",
1803+
windef->refname),
1804+
errhint("Omit the parentheses in this OVER clause."),
17851805
parser_errposition(pstate,windef->location)));
1806+
}
17861807
wc->frameOptions=windef->frameOptions;
17871808
/* Process frame offset expressions */
17881809
wc->startOffset=transformFrameOffset(pstate,wc->frameOptions,

0 commit comments

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