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

Commit0a445f2

Browse files
committed
Selectively include window frames in expression walks/mutates.
query_tree_walker and query_tree_mutator were skipping thewindowClause of the query, without regard for the fact that thestartOffset and endOffset in a WindowClause node are expression treesthat need to be processed. This was an oversight in commitec4be2efrom 2010 which added the expression fields; the main symptom is thatfunction parameters in window frame clauses don't work in inlinedfunctions.Fix (as conservatively as possible since this needs to not breakexisting out-of-tree callers) and add tests.Backpatch all the way, since this has been broken since 9.0.Per report from Alastair McKinley; fix by me with kibitzing and reviewfrom Tom Lane.Discussion:https://postgr.es/m/DB6PR0202MB2904E7FDDA9D81504D1E8C68E3800@DB6PR0202MB2904.eurprd02.prod.outlook.com
1 parentb978de0 commit0a445f2

File tree

5 files changed

+169
-7
lines changed

5 files changed

+169
-7
lines changed

‎src/backend/catalog/dependency.c

Lines changed: 2 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -2013,18 +2013,13 @@ find_expr_references_walker(Node *node,
20132013
context->addrs);
20142014
}
20152015

2016-
/* query_tree_walker ignores ORDER BY etc, but we need those opers */
2017-
find_expr_references_walker((Node*)query->sortClause,context);
2018-
find_expr_references_walker((Node*)query->groupClause,context);
2019-
find_expr_references_walker((Node*)query->windowClause,context);
2020-
find_expr_references_walker((Node*)query->distinctClause,context);
2021-
20222016
/* Examine substructure of query */
20232017
context->rtables=lcons(query->rtable,context->rtables);
20242018
result=query_tree_walker(query,
20252019
find_expr_references_walker,
20262020
(void*)context,
2027-
QTW_IGNORE_JOINALIASES);
2021+
QTW_IGNORE_JOINALIASES |
2022+
QTW_EXAMINE_SORTGROUP);
20282023
context->rtables=list_delete_first(context->rtables);
20292024
returnresult;
20302025
}

‎src/backend/nodes/nodeFuncs.c

Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2260,6 +2260,13 @@ query_tree_walker(Query *query,
22602260
{
22612261
Assert(query!=NULL&&IsA(query,Query));
22622262

2263+
/*
2264+
* We don't walk any utilityStmt here. However, we can't easily assert
2265+
* that it is absent, since there are at least two code paths by which
2266+
* action statements from CREATE RULE end up here, and NOTIFY is allowed
2267+
* in a rule action.
2268+
*/
2269+
22632270
if (walker((Node*)query->targetList,context))
22642271
return true;
22652272
if (walker((Node*)query->withCheckOptions,context))
@@ -2278,6 +2285,54 @@ query_tree_walker(Query *query,
22782285
return true;
22792286
if (walker(query->limitCount,context))
22802287
return true;
2288+
2289+
/*
2290+
* Most callers aren't interested in SortGroupClause nodes since those
2291+
* don't contain actual expressions. However they do contain OIDs which
2292+
* may be needed by dependency walkers etc.
2293+
*/
2294+
if ((flags&QTW_EXAMINE_SORTGROUP))
2295+
{
2296+
if (walker((Node*)query->groupClause,context))
2297+
return true;
2298+
if (walker((Node*)query->windowClause,context))
2299+
return true;
2300+
if (walker((Node*)query->sortClause,context))
2301+
return true;
2302+
if (walker((Node*)query->distinctClause,context))
2303+
return true;
2304+
}
2305+
else
2306+
{
2307+
/*
2308+
* But we need to walk the expressions under WindowClause nodes even
2309+
* if we're not interested in SortGroupClause nodes.
2310+
*/
2311+
ListCell*lc;
2312+
2313+
foreach(lc,query->windowClause)
2314+
{
2315+
WindowClause*wc=lfirst_node(WindowClause,lc);
2316+
2317+
if (walker(wc->startOffset,context))
2318+
return true;
2319+
if (walker(wc->endOffset,context))
2320+
return true;
2321+
}
2322+
}
2323+
2324+
/*
2325+
* groupingSets and rowMarks are not walked:
2326+
*
2327+
* groupingSets contain only ressortgrouprefs (integers) which are
2328+
* meaningless without the corresponding groupClause or tlist.
2329+
* Accordingly, any walker that needs to care about them needs to handle
2330+
* them itself in its Query processing.
2331+
*
2332+
* rowMarks is not walked because it contains only rangetable indexes (and
2333+
* flags etc.) and therefore should be handled at Query level similarly.
2334+
*/
2335+
22812336
if (!(flags&QTW_IGNORE_CTE_SUBQUERIES))
22822337
{
22832338
if (walker((Node*)query->cteList,context))
@@ -3114,6 +3169,56 @@ query_tree_mutator(Query *query,
31143169
MUTATE(query->havingQual,query->havingQual,Node*);
31153170
MUTATE(query->limitOffset,query->limitOffset,Node*);
31163171
MUTATE(query->limitCount,query->limitCount,Node*);
3172+
3173+
/*
3174+
* Most callers aren't interested in SortGroupClause nodes since those
3175+
* don't contain actual expressions. However they do contain OIDs, which
3176+
* may be of interest to some mutators.
3177+
*/
3178+
3179+
if ((flags&QTW_EXAMINE_SORTGROUP))
3180+
{
3181+
MUTATE(query->groupClause,query->groupClause,List*);
3182+
MUTATE(query->windowClause,query->windowClause,List*);
3183+
MUTATE(query->sortClause,query->sortClause,List*);
3184+
MUTATE(query->distinctClause,query->distinctClause,List*);
3185+
}
3186+
else
3187+
{
3188+
/*
3189+
* But we need to mutate the expressions under WindowClause nodes even
3190+
* if we're not interested in SortGroupClause nodes.
3191+
*/
3192+
List*resultlist;
3193+
ListCell*temp;
3194+
3195+
resultlist=NIL;
3196+
foreach(temp,query->windowClause)
3197+
{
3198+
WindowClause*wc=lfirst_node(WindowClause,temp);
3199+
WindowClause*newnode;
3200+
3201+
FLATCOPY(newnode,wc,WindowClause);
3202+
MUTATE(newnode->startOffset,wc->startOffset,Node*);
3203+
MUTATE(newnode->endOffset,wc->endOffset,Node*);
3204+
3205+
resultlist=lappend(resultlist, (Node*)newnode);
3206+
}
3207+
query->windowClause=resultlist;
3208+
}
3209+
3210+
/*
3211+
* groupingSets and rowMarks are not mutated:
3212+
*
3213+
* groupingSets contain only ressortgroup refs (integers) which are
3214+
* meaningless without the groupClause or tlist. Accordingly, any mutator
3215+
* that needs to care about them needs to handle them itself in its Query
3216+
* processing.
3217+
*
3218+
* rowMarks contains only rangetable indexes (and flags etc.) and
3219+
* therefore should be handled at Query level similarly.
3220+
*/
3221+
31173222
if (!(flags&QTW_IGNORE_CTE_SUBQUERIES))
31183223
MUTATE(query->cteList,query->cteList,List*);
31193224
else/* else copy CTE list as-is */

‎src/include/nodes/nodeFuncs.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#defineQTW_IGNORE_RANGE_TABLE0x08/* skip rangetable entirely */
2525
#defineQTW_EXAMINE_RTES0x10/* examine RTEs */
2626
#defineQTW_DONT_COPY_QUERY0x20/* do not copy top Query */
27+
#defineQTW_EXAMINE_SORTGROUP0x80/* include SortGroupNode lists */
2728

2829
/* callback function for check_functions_in_node */
2930
typedefbool (*check_function_callback) (Oidfunc_id,void*context);

‎src/test/regress/expected/window.out

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3787,3 +3787,45 @@ SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
37873787
5 | t | t | t
37883788
(5 rows)
37893789

3790+
-- Tests for problems with failure to walk or mutate expressions
3791+
-- within window frame clauses.
3792+
-- test walker (fails with collation error if expressions are not walked)
3793+
SELECT array_agg(i) OVER w
3794+
FROM generate_series(1,5) i
3795+
WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW);
3796+
array_agg
3797+
-----------
3798+
{1}
3799+
{1,2}
3800+
{2,3}
3801+
{3,4}
3802+
{4,5}
3803+
(5 rows)
3804+
3805+
-- test mutator (fails when inlined if expressions are not mutated)
3806+
CREATE FUNCTION pg_temp.f(group_size BIGINT) RETURNS SETOF integer[]
3807+
AS $$
3808+
SELECT array_agg(s) OVER w
3809+
FROM generate_series(1,5) s
3810+
WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING)
3811+
$$ LANGUAGE SQL STABLE;
3812+
EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
3813+
QUERY PLAN
3814+
------------------------------------------------------
3815+
Subquery Scan on f
3816+
-> WindowAgg
3817+
-> Sort
3818+
Sort Key: s.s
3819+
-> Function Scan on generate_series s
3820+
(5 rows)
3821+
3822+
SELECT * FROM pg_temp.f(2);
3823+
f
3824+
---------
3825+
{1,2,3}
3826+
{2,3,4}
3827+
{3,4,5}
3828+
{4,5}
3829+
{5}
3830+
(5 rows)
3831+

‎src/test/regress/sql/window.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1241,3 +1241,22 @@ SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FO
12411241
SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
12421242
FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
12431243
WINDOW wAS (ORDER BY i ROWS BETWEEN CURRENT ROWAND1 FOLLOWING);
1244+
1245+
-- Tests for problems with failure to walk or mutate expressions
1246+
-- within window frame clauses.
1247+
1248+
-- test walker (fails with collation error if expressions are not walked)
1249+
SELECT array_agg(i) OVER w
1250+
FROM generate_series(1,5) i
1251+
WINDOW wAS (ORDER BY i ROWS BETWEEN (('foo'<'foobar')::integer) PRECEDINGAND CURRENT ROW);
1252+
1253+
-- test mutator (fails when inlined if expressions are not mutated)
1254+
CREATEFUNCTIONpg_temp.f(group_sizeBIGINT) RETURNS SETOFinteger[]
1255+
AS $$
1256+
SELECT array_agg(s) OVER w
1257+
FROM generate_series(1,5) s
1258+
WINDOW wAS (ORDER BY s ROWS BETWEEN CURRENT ROWAND GROUP_SIZE FOLLOWING)
1259+
$$ LANGUAGE SQL STABLE;
1260+
1261+
EXPLAIN (costs off)SELECT*FROMpg_temp.f(2);
1262+
SELECT*FROMpg_temp.f(2);

0 commit comments

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