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

Commit269c5dd

Browse files
committed
Fix window functions that sort by expressions involving aggregates.
In commitc1d9579, I changed things sothat the output of the Agg node that feeds the window functions would notlist any ungrouped Vars directly. Formerly, for example, the Agg tlistmight have included both "x" and "sum(x)", which is not really valid if"x" isn't a grouping column. If we then had a window function ordering onsomething like "sum(x) + 1", prepare_sort_from_pathkeys would find no exactmatch for this in the Agg tlist, and would conclude that it must recomputethe expression. But it would break the expression down to just the Var"x", which it would find in the tlist, and then rebuild the ORDER BYexpression using a reference to the subplan's "x" output. Now, after theabove-referenced changes, "x" isn't in the Agg tlist if it's not a groupingcolumn, so that prepare_sort_from_pathkeys fails with "could not findpathkey item to sort", as reported by Bricklen Anderson.The fix is to not break down Aggrefs into their component parts, but justtreat them as irreducible expressions to be sought in the subplan tlist.This is definitely OK for the use with respect to window functions ingrouping_planner, since it just built the tlist being used on the samebasis. AFAICT it is safe for other uses too; most of the other call sitescouldn't encounter Aggrefs anyway.
1 parent57eb009 commit269c5dd

File tree

3 files changed

+35
-2
lines changed

3 files changed

+35
-2
lines changed

‎src/backend/optimizer/plan/createplan.c

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3533,7 +3533,13 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
35333533

35343534
if (!tle)
35353535
{
3536-
/* No matching tlist item; look for a computable expression */
3536+
/*
3537+
* No matching tlist item; look for a computable expression.
3538+
* Note that we treat Aggrefs as if they were variables; this
3539+
* is necessary when attempting to sort the output from an Agg
3540+
* node for use in a WindowFunc (since grouping_planner will
3541+
* have treated the Aggrefs as variables, too).
3542+
*/
35373543
Expr*sortexpr=NULL;
35383544

35393545
foreach(j,ec->ec_members)
@@ -3546,7 +3552,7 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
35463552
continue;
35473553
sortexpr=em->em_expr;
35483554
exprvars=pull_var_clause((Node*)sortexpr,
3549-
PVC_RECURSE_AGGREGATES,
3555+
PVC_INCLUDE_AGGREGATES,
35503556
PVC_INCLUDE_PLACEHOLDERS);
35513557
foreach(k,exprvars)
35523558
{

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -594,6 +594,26 @@ SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42;
594594
0
595595
(1 row)
596596

597+
-- window function with ORDER BY an expression involving aggregates (9.1 bug)
598+
select ten,
599+
sum(unique1) + sum(unique2) as res,
600+
rank() over (order by sum(unique1) + sum(unique2)) as rank
601+
from tenk1
602+
group by ten order by ten;
603+
ten | res | rank
604+
-----+----------+------
605+
0 | 9976146 | 4
606+
1 | 10114187 | 9
607+
2 | 10059554 | 8
608+
3 | 9878541 | 1
609+
4 | 9881005 | 2
610+
5 | 9981670 | 5
611+
6 | 9947099 | 3
612+
7 | 10120309 | 10
613+
8 | 9991305 | 6
614+
9 | 10040184 | 7
615+
(10 rows)
616+
597617
-- test non-default frame specifications
598618
SELECT four, ten,
599619
sum(ten) over (partition by four order by ten),

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -138,6 +138,13 @@ SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno),
138138
-- window function over ungrouped agg over empty row set (bug before 9.1)
139139
SELECTSUM(COUNT(f1)) OVER ()FROM int4_tblWHERE f1=42;
140140

141+
-- window function with ORDER BY an expression involving aggregates (9.1 bug)
142+
select ten,
143+
sum(unique1)+sum(unique2)as res,
144+
rank() over (order bysum(unique1)+sum(unique2))as rank
145+
from tenk1
146+
group by tenorder by ten;
147+
141148
-- test non-default frame specifications
142149
SELECT four, ten,
143150
sum(ten) over (partition by fourorder by ten),

0 commit comments

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