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

Commitbb16dc4

Browse files
committed
Modify the definition of window-function PARTITION BY and ORDER BY clauses
so that their elements are always taken as simple expressions over thequery's input columns. It originally seemed like a good idea to make themact exactly like GROUP BY and ORDER BY, right down to the SQL92-era behaviorof accepting output column names or numbers. However, that was not such agreat idea, for two reasons:1. It permits circular references, as exhibited in bug #5018: the outputcolumn could be the one containing the window function itself. (We actuallyhad a regression test case illustrating this, but nobody thought twice abouthow confusing that would be.)2. It doesn't seem like a good idea for, eg, "lead(foo) OVER (ORDER BY foo)"to potentially use two completely different meanings for "foo".Accordingly, narrow down the behavior of window clauses to use only theSQL99-compliant interpretation that the expressions are simple expressions.
1 parentf065b17 commitbb16dc4

File tree

7 files changed

+116
-63
lines changed

7 files changed

+116
-63
lines changed

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

Lines changed: 14 additions & 7 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.123 2009/08/18 23:40:20 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.124 2009/08/27 20:08:02 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -595,17 +595,24 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
595595

596596
<para>
597597
The elements of the <literal>PARTITION BY</> list are interpreted in
598-
the same fashion as elements of a
599-
<xref linkend="sql-groupby" endterm="sql-groupby-title">, and
600-
the elements of the <literal>ORDER BY</> list are interpreted in the
601-
same fashion as elements of an
602-
<xref linkend="sql-orderby" endterm="sql-orderby-title">.
603-
The only difference is that these expressions can contain aggregate
598+
much the same fashion as elements of a
599+
<xref linkend="sql-groupby" endterm="sql-groupby-title">, except that
600+
they are always simple expressions and never the name or number of an
601+
output column.
602+
Another difference is that these expressions can contain aggregate
604603
function calls, which are not allowed in a regular <literal>GROUP BY</>
605604
clause. They are allowed here because windowing occurs after grouping
606605
and aggregation.
607606
</para>
608607

608+
<para>
609+
Similarly, the elements of the <literal>ORDER BY</> list are interpreted
610+
in much the same fashion as elements of an
611+
<xref linkend="sql-orderby" endterm="sql-orderby-title">, except that
612+
the expressions are always taken as simple expressions and never the name
613+
or number of an output column.
614+
</para>
615+
609616
<para>
610617
The optional <replaceable class="parameter">frame_clause</> defines
611618
the <firstterm>window frame</> for window functions that depend on the

‎doc/src/sgml/syntax.sgml‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.133 2009/06/17 21:58:49 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.134 2009/08/27 20:08:02 tgl Exp $ -->
22

33
<chapter id="sql-syntax">
44
<title>SQL Syntax</title>
@@ -1619,7 +1619,9 @@ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
16191619
expression that does not itself contain window function calls.
16201620
The <literal>PARTITION BY</> and <literal>ORDER BY</> lists have
16211621
essentially the same syntax and semantics as <literal>GROUP BY</>
1622-
and <literal>ORDER BY</> clauses of the whole query.
1622+
and <literal>ORDER BY</> clauses of the whole query, except that their
1623+
expressions are always just expressions and cannot be output-column
1624+
names or numbers.
16231625
<replaceable>window_name</replaceable> is a reference to a named window
16241626
specification defined in the query's <literal>WINDOW</literal> clause.
16251627
Named window specifications are usually referenced with just

‎src/backend/parser/analyze.c‎

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
20-
*$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.389 2009/06/11 14:48:59 momjian Exp $
20+
*$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.390 2009/08/27 20:08:02 tgl Exp $
2121
*
2222
*-------------------------------------------------------------------------
2323
*/
@@ -825,13 +825,14 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
825825
qry->sortClause=transformSortClause(pstate,
826826
stmt->sortClause,
827827
&qry->targetList,
828-
true/* fix unknowns */ );
828+
true/* fix unknowns */,
829+
false/* not window function */);
829830

830831
qry->groupClause=transformGroupClause(pstate,
831832
stmt->groupClause,
832833
&qry->targetList,
833834
qry->sortClause,
834-
false);
835+
false/* not window function */);
835836

836837
if (stmt->distinctClause==NIL)
837838
{
@@ -1039,7 +1040,8 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
10391040
qry->sortClause=transformSortClause(pstate,
10401041
stmt->sortClause,
10411042
&qry->targetList,
1042-
true/* fix unknowns */ );
1043+
true/* fix unknowns */,
1044+
false/* not window function */);
10431045

10441046
qry->limitOffset=transformLimitClause(pstate,stmt->limitOffset,
10451047
"OFFSET");
@@ -1291,7 +1293,8 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
12911293
qry->sortClause=transformSortClause(pstate,
12921294
sortClause,
12931295
&qry->targetList,
1294-
false/* no unknowns expected */ );
1296+
false/* no unknowns expected */,
1297+
false/* not window function */);
12951298

12961299
pstate->p_rtable=list_truncate(pstate->p_rtable,sv_rtable_length);
12971300
pstate->p_relnamespace=sv_relnamespace;

‎src/backend/parser/parse_clause.c‎

Lines changed: 81 additions & 40 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.190 2009/07/16 06:33:43 petere Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.191 2009/08/27 20:08:02 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -37,16 +37,15 @@
3737
#include"utils/rel.h"
3838

3939

40+
/* clause types for findTargetlistEntrySQL92 */
4041
#defineORDER_CLAUSE 0
4142
#defineGROUP_CLAUSE 1
4243
#defineDISTINCT_ON_CLAUSE 2
43-
#definePARTITION_CLAUSE 3
4444

4545
staticconstchar*constclauseText[]= {
4646
"ORDER BY",
4747
"GROUP BY",
48-
"DISTINCT ON",
49-
"PARTITION BY"
48+
"DISTINCT ON"
5049
};
5150

5251
staticvoidextractRemainingColumns(List*common_colnames,
@@ -73,8 +72,10 @@ static Node *transformFromClauseItem(ParseState *pstate, Node *n,
7372
Relids*containedRels);
7473
staticNode*buildMergedJoinVar(ParseState*pstate,JoinTypejointype,
7574
Var*l_colvar,Var*r_colvar);
76-
staticTargetEntry*findTargetlistEntry(ParseState*pstate,Node*node,
77-
List**tlist,intclause);
75+
staticTargetEntry*findTargetlistEntrySQL92(ParseState*pstate,Node*node,
76+
List**tlist,intclause);
77+
staticTargetEntry*findTargetlistEntrySQL99(ParseState*pstate,Node*node,
78+
List**tlist);
7879
staticintget_matching_location(intsortgroupref,
7980
List*sortgrouprefs,List*exprs);
8081
staticList*addTargetToSortList(ParseState*pstate,TargetEntry*tle,
@@ -1216,21 +1217,27 @@ transformLimitClause(ParseState *pstate, Node *clause,
12161217

12171218

12181219
/*
1219-
*findTargetlistEntry -
1220+
*findTargetlistEntrySQL92 -
12201221
* Returns the targetlist entry matching the given (untransformed) node.
12211222
* If no matching entry exists, one is created and appended to the target
12221223
* list as a "resjunk" node.
12231224
*
1225+
* This function supports the old SQL92 ORDER BY interpretation, where the
1226+
* expression is an output column name or number. If we fail to find a
1227+
* match of that sort, we fall through to the SQL99 rules. For historical
1228+
* reasons, Postgres also allows this interpretation for GROUP BY, though
1229+
* the standard never did. However, for GROUP BY we prefer a SQL99 match.
1230+
* This function is *not* used for WINDOW definitions.
1231+
*
12241232
* nodethe ORDER BY, GROUP BY, or DISTINCT ON expression to be matched
12251233
* tlistthe target list (passed by reference so we can append to it)
12261234
* clauseidentifies clause type being processed
12271235
*/
12281236
staticTargetEntry*
1229-
findTargetlistEntry(ParseState*pstate,Node*node,List**tlist,intclause)
1237+
findTargetlistEntrySQL92(ParseState*pstate,Node*node,List**tlist,
1238+
intclause)
12301239
{
1231-
TargetEntry*target_result=NULL;
12321240
ListCell*tl;
1233-
Node*expr;
12341241

12351242
/*----------
12361243
* Handle two special cases as mandated by the SQL92 spec:
@@ -1258,8 +1265,7 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
12581265
* 2. IntegerConstant
12591266
* This means to use the n'th item in the existing target list.
12601267
* Note that it would make no sense to order/group/distinct by an
1261-
* actual constant, so this does not create a conflict with our
1262-
* extension to order/group by an expression.
1268+
* actual constant, so this does not create a conflict with SQL99.
12631269
* GROUP BY column-number is not allowed by SQL92, but since
12641270
* the standard has no other behavior defined for this syntax,
12651271
* we may as well accept this common extension.
@@ -1268,7 +1274,7 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
12681274
* since the user didn't write them in his SELECT list.
12691275
*
12701276
* If neither special case applies, fall through to treat the item as
1271-
* an expression.
1277+
* an expression per SQL99.
12721278
*----------
12731279
*/
12741280
if (IsA(node,ColumnRef)&&
@@ -1278,15 +1284,15 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
12781284
char*name=strVal(linitial(((ColumnRef*)node)->fields));
12791285
intlocation= ((ColumnRef*)node)->location;
12801286

1281-
if (clause==GROUP_CLAUSE||clause==PARTITION_CLAUSE)
1287+
if (clause==GROUP_CLAUSE)
12821288
{
12831289
/*
12841290
* In GROUP BY, we must prefer a match against a FROM-clause
12851291
* column to one against the targetlist. Look to see if there is
1286-
* a matching column. If so, fall through tolet transformExpr()
1287-
*do the rest.NOTE: if name could refer ambiguously to more
1288-
*than one columnname exposed by FROM, colNameToVar will
1289-
*ereport(ERROR).That'sjust what we want here.
1292+
* a matching column. If so, fall through touse SQL99 rules.
1293+
* NOTE: if name could refer ambiguously to more than one column
1294+
* name exposed by FROM, colNameToVar will ereport(ERROR). That's
1295+
* just what we want here.
12901296
*
12911297
* Small tweak for 7.4.3: ignore matches in upper query levels.
12921298
* This effectively changes the search order for bare names to (1)
@@ -1295,15 +1301,15 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
12951301
* SQL99 do not allow GROUPing BY an outer reference, so this
12961302
* breaks no cases that are legal per spec, and it seems a more
12971303
* self-consistent behavior.
1298-
*
1299-
* Window PARTITION BY clauses should act exactly like GROUP BY.
13001304
*/
13011305
if (colNameToVar(pstate,name, true,location)!=NULL)
13021306
name=NULL;
13031307
}
13041308

13051309
if (name!=NULL)
13061310
{
1311+
TargetEntry*target_result=NULL;
1312+
13071313
foreach(tl,*tlist)
13081314
{
13091315
TargetEntry*tle= (TargetEntry*)lfirst(tl);
@@ -1367,12 +1373,36 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
13671373
}
13681374

13691375
/*
1370-
* Otherwise, we have an expression (this is a Postgres extension not
1371-
* found in SQL92). Convert the untransformed node to a transformed
1372-
* expression, and search for a match in the tlist. NOTE: it doesn't
1373-
* really matter whether there is more than one match.Also, we are
1374-
* willing to match a resjunk target here, though the above cases must
1375-
* ignore resjunk targets.
1376+
* Otherwise, we have an expression, so process it per SQL99 rules.
1377+
*/
1378+
returnfindTargetlistEntrySQL99(pstate,node,tlist);
1379+
}
1380+
1381+
/*
1382+
*findTargetlistEntrySQL99 -
1383+
* Returns the targetlist entry matching the given (untransformed) node.
1384+
* If no matching entry exists, one is created and appended to the target
1385+
* list as a "resjunk" node.
1386+
*
1387+
* This function supports the SQL99 interpretation, wherein the expression
1388+
* is just an ordinary expression referencing input column names.
1389+
*
1390+
* nodethe ORDER BY, GROUP BY, etc expression to be matched
1391+
* tlistthe target list (passed by reference so we can append to it)
1392+
*/
1393+
staticTargetEntry*
1394+
findTargetlistEntrySQL99(ParseState*pstate,Node*node,List**tlist)
1395+
{
1396+
TargetEntry*target_result;
1397+
ListCell*tl;
1398+
Node*expr;
1399+
1400+
/*
1401+
* Convert the untransformed node to a transformed expression, and search
1402+
* for a match in the tlist. NOTE: it doesn't really matter whether there
1403+
* is more than one match. Also, we are willing to match an existing
1404+
* resjunk target here, though the SQL92 cases above must ignore resjunk
1405+
* targets.
13761406
*/
13771407
expr=transformExpr(pstate,node);
13781408

@@ -1403,16 +1433,15 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
14031433
* GROUP BY items will be added to the targetlist (as resjunk columns)
14041434
* if not already present, so the targetlist must be passed by reference.
14051435
*
1406-
* This is also used for window PARTITION BY clauses (whichactuallyact
1407-
*just thesame,except for the clause name used in error messages).
1436+
* This is also used for window PARTITION BY clauses (which act almost the
1437+
* same,but are always interpreted per SQL99 rules).
14081438
*/
14091439
List*
14101440
transformGroupClause(ParseState*pstate,List*grouplist,
14111441
List**targetlist,List*sortClause,
1412-
boolisPartition)
1442+
boolisWindowFunc)
14131443
{
14141444
List*result=NIL;
1415-
intclause=isPartition ?PARTITION_CLAUSE :GROUP_CLAUSE;
14161445
ListCell*gl;
14171446

14181447
foreach(gl,grouplist)
@@ -1421,7 +1450,11 @@ transformGroupClause(ParseState *pstate, List *grouplist,
14211450
TargetEntry*tle;
14221451
boolfound= false;
14231452

1424-
tle=findTargetlistEntry(pstate,gexpr,targetlist,clause);
1453+
if (isWindowFunc)
1454+
tle=findTargetlistEntrySQL99(pstate,gexpr,targetlist);
1455+
else
1456+
tle=findTargetlistEntrySQL92(pstate,gexpr,targetlist,
1457+
GROUP_CLAUSE);
14251458

14261459
/* Eliminate duplicates (GROUP BY x, x) */
14271460
if (targetIsInSortList(tle,InvalidOid,result))
@@ -1475,12 +1508,16 @@ transformGroupClause(ParseState *pstate, List *grouplist,
14751508
*
14761509
* ORDER BY items will be added to the targetlist (as resjunk columns)
14771510
* if not already present, so the targetlist must be passed by reference.
1511+
*
1512+
* This is also used for window ORDER BY clauses (which act almost the
1513+
* same, but are always interpreted per SQL99 rules).
14781514
*/
14791515
List*
14801516
transformSortClause(ParseState*pstate,
14811517
List*orderlist,
14821518
List**targetlist,
1483-
boolresolveUnknown)
1519+
boolresolveUnknown,
1520+
boolisWindowFunc)
14841521
{
14851522
List*sortlist=NIL;
14861523
ListCell*olitem;
@@ -1490,8 +1527,11 @@ transformSortClause(ParseState *pstate,
14901527
SortBy*sortby= (SortBy*)lfirst(olitem);
14911528
TargetEntry*tle;
14921529

1493-
tle=findTargetlistEntry(pstate,sortby->node,
1494-
targetlist,ORDER_CLAUSE);
1530+
if (isWindowFunc)
1531+
tle=findTargetlistEntrySQL99(pstate,sortby->node,targetlist);
1532+
else
1533+
tle=findTargetlistEntrySQL92(pstate,sortby->node,targetlist,
1534+
ORDER_CLAUSE);
14951535

14961536
sortlist=addTargetToSortList(pstate,tle,
14971537
sortlist,*targetlist,sortby,
@@ -1550,18 +1590,19 @@ transformWindowDefinitions(ParseState *pstate,
15501590

15511591
/*
15521592
* Transform PARTITION and ORDER specs, if any. These are treated
1553-
* exactly like top-level GROUP BY and ORDER BY clauses, including the
1554-
* special handling of nondefault operator semantics.
1593+
*almostexactly like top-level GROUP BY and ORDER BY clauses,
1594+
*including thespecial handling of nondefault operator semantics.
15551595
*/
15561596
orderClause=transformSortClause(pstate,
15571597
windef->orderClause,
15581598
targetlist,
1559-
true);
1599+
true/* fix unknowns */,
1600+
true/* window function */);
15601601
partitionClause=transformGroupClause(pstate,
15611602
windef->partitionClause,
15621603
targetlist,
15631604
orderClause,
1564-
true);
1605+
true/* window function */);
15651606

15661607
/*
15671608
* And prepare the new WindowClause.
@@ -1736,8 +1777,8 @@ transformDistinctOnClause(ParseState *pstate, List *distinctlist,
17361777
intsortgroupref;
17371778
TargetEntry*tle;
17381779

1739-
tle=findTargetlistEntry(pstate,dexpr,
1740-
targetlist,DISTINCT_ON_CLAUSE);
1780+
tle=findTargetlistEntrySQL92(pstate,dexpr,targetlist,
1781+
DISTINCT_ON_CLAUSE);
17411782
sortgroupref=assignSortGroupRef(tle,*targetlist);
17421783
sortgrouprefs=lappend_int(sortgrouprefs,sortgroupref);
17431784
}

‎src/include/parser/parse_clause.h‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/parser/parse_clause.h,v 1.55 2009/06/11 14:49:11 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/parser/parse_clause.h,v 1.56 2009/08/27 20:08:02 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -28,9 +28,9 @@ extern Node *transformLimitClause(ParseState *pstate, Node *clause,
2828
constchar*constructName);
2929
externList*transformGroupClause(ParseState*pstate,List*grouplist,
3030
List**targetlist,List*sortClause,
31-
boolisPartition);
31+
boolisWindowFunc);
3232
externList*transformSortClause(ParseState*pstate,List*orderlist,
33-
List**targetlist,boolresolveUnknown);
33+
List**targetlist,boolresolveUnknown,boolisWindowFunc);
3434

3535
externList*transformWindowDefinitions(ParseState*pstate,
3636
List*windowdefs,

0 commit comments

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