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

Commit1679e9f

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 parentbe64ba6 commit1679e9f

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
@@ -3539,7 +3539,13 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
35393539

35403540
if (!tle)
35413541
{
3542-
/* No matching tlist item; look for a computable expression */
3542+
/*
3543+
* No matching tlist item; look for a computable expression.
3544+
* Note that we treat Aggrefs as if they were variables; this
3545+
* is necessary when attempting to sort the output from an Agg
3546+
* node for use in a WindowFunc (since grouping_planner will
3547+
* have treated the Aggrefs as variables, too).
3548+
*/
35433549
Expr*sortexpr=NULL;
35443550

35453551
foreach(j,ec->ec_members)
@@ -3552,7 +3558,7 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
35523558
continue;
35533559
sortexpr=em->em_expr;
35543560
exprvars=pull_var_clause((Node*)sortexpr,
3555-
PVC_RECURSE_AGGREGATES,
3561+
PVC_INCLUDE_AGGREGATES,
35563562
PVC_INCLUDE_PLACEHOLDERS);
35573563
foreach(k,exprvars)
35583564
{

‎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