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

On Sat, 10 Dec 2022 at 00:00, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> 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);
> + }
> + }

Thanks for having a look at this.

I think what you have above fixes the bulk of the issue, but there's
still a bit more to do to properly fix the reported case.

The additional thing that seems to cause the reported error is that
once the subquery is pulled up, the run condition also needs a round
of constant folding done. See subquery_planner() around line 827. The
problem is that the target list's WindowFunc ends up with count(1)
over .., but the run condition's one is left as count(case 1 when 1
then 1 else null end), which preprocess_expression() will fold into
the same as what's in the target list.

I'm now wondering if WindowClause.runCondition should be of type Node
* instead of List *. I'd have imagined I should be passing the type of
EXPRKIND_QUAL to preprocess_expression's type, but canonicalize_qual()
does not like Lists.

David