Re: BUG #17709: Regression in PG15 with window functions - "WindowFunc not found in subplan target lists"

On Fri, Dec 9, 2022 at 5:42 PM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> Following query works fine on PG14, but produce error "WindowFunc not found
> in subplan target lists" on PG15:
>
> select 1
> from
> (
> select count(case t1.a when 1 then 1 else null end) over (partition by
> t2.b) c
> from (select 1 a) t1, (select 1 b) t2
> ) t
> where t.c = 1

Thanks for the report! I can reproduce this issue.

The WindowFunc within runCondition comes from the query's targetList,
before we pull up subquery 't1'. Then when it comes to pulling up
subquery 't1', we perform pullup variable replacement for the query's
targetList but not for runCondition in the query's windowClause. I
believe that's how this error is triggered.

Below is how we can fix this issue.

--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2134,6 +2134,16 @@ perform_pullup_replace_vars(PlannerInfo *root,
* can't contain any references to a subquery.
*/
}
+ if (parse->windowClause)
+ {
+ foreach(lc, parse->windowClause)
+ {
+ WindowClause *wclause = (WindowClause *) lfirst(lc);
+
+ wclause->runCondition = (List *)
+ pullup_replace_vars((Node *) wclause->runCondition,
rvcontext);
+ }
+ }
if (parse->mergeActionList)
{
foreach(lc, parse->mergeActionList)

Thanks
Richard