diff options
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 10 | ||||
| -rw-r--r-- | src/test/regress/expected/window.out | 20 | ||||
| -rw-r--r-- | src/test/regress/sql/window.sql | 7 |
3 files changed, 35 insertions, 2 deletions
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index b674afe4264..0dedebaf705 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -3533,7 +3533,13 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys, if (!tle) { - /* No matching tlist item; look for a computable expression */ + /* + * No matching tlist item; look for a computable expression. + * Note that we treat Aggrefs as if they were variables; this + * is necessary when attempting to sort the output from an Agg + * node for use in a WindowFunc (since grouping_planner will + * have treated the Aggrefs as variables, too). + */ Expr *sortexpr = NULL; foreach(j, ec->ec_members) @@ -3546,7 +3552,7 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys, continue; sortexpr = em->em_expr; exprvars = pull_var_clause((Node *) sortexpr, - PVC_RECURSE_AGGREGATES, + PVC_INCLUDE_AGGREGATES, PVC_INCLUDE_PLACEHOLDERS); foreach(k, exprvars) { diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index e42ce174381..048d463533a 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -594,6 +594,26 @@ SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42; 0 (1 row) +-- window function with ORDER BY an expression involving aggregates (9.1 bug) +select ten, + sum(unique1) + sum(unique2) as res, + rank() over (order by sum(unique1) + sum(unique2)) as rank +from tenk1 +group by ten order by ten; + ten | res | rank +-----+----------+------ + 0 | 9976146 | 4 + 1 | 10114187 | 9 + 2 | 10059554 | 8 + 3 | 9878541 | 1 + 4 | 9881005 | 2 + 5 | 9981670 | 5 + 6 | 9947099 | 3 + 7 | 10120309 | 10 + 8 | 9991305 | 6 + 9 | 10040184 | 7 +(10 rows) + -- test non-default frame specifications SELECT four, ten, sum(ten) over (partition by four order by ten), diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 61da23a4a35..268430a260b 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -138,6 +138,13 @@ SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), -- window function over ungrouped agg over empty row set (bug before 9.1) SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42; +-- window function with ORDER BY an expression involving aggregates (9.1 bug) +select ten, + sum(unique1) + sum(unique2) as res, + rank() over (order by sum(unique1) + sum(unique2)) as rank +from tenk1 +group by ten order by ten; + -- test non-default frame specifications SELECT four, ten, sum(ten) over (partition by four order by ten), |