Re: BUG #18305: Unexpected error: "WindowFunc not found in subplan target lists" triggered by subqueries

On Mon, Jan 22, 2024 at 11:44 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > My fuzzer finds a bug in Postgres 17devel, which triggers an unexpected
> > error "ERROR: WindowFunc not found in subplan target lists".
>
> > create table exeet_t3 (pkey int4);
> > create view exeet_t8 as
> > select
> > ntile(exeet_subq_0.c_0) over () as c_0
> > from
> > (select (select pkey from exeet_t3 order by pkey limit 1 offset 6) as
> > c_0) as exeet_subq_0;
>
> > select
> > 1 as c_1
> > from
> > exeet_t8 as exeet_ref_17
> > where exeet_ref_17.c_0 < 0;
> > ERROR: WindowFunc not found in subplan target lists
>
> Thanks for the report! Bisecting shows that this broke at
>
> 456fa635a909ee36f73ca84d340521bd730f265f is the first bad commit
> commit 456fa635a909ee36f73ca84d340521bd730f265f
> Author: David Rowley <drowley(at)postgresql(dot)org>
> Date: Fri Jan 27 16:08:41 2023 +1300
>
> Teach planner about more monotonic window functions
>
> 9d9c02ccd introduced runConditions for window functions to allow
> monotonic window function evaluation to be made more efficient when the
> window function value went beyond some value that it would never go
> back
> from due to its monotonic nature. That commit added prosupport
> functions
> to inform the planner that row_number(), rank(), dense_rank() and some
> forms of count(*) were monotonic. Here we add support for ntile(),
> cume_dist() and percent_rank().
>
> So I'm betting there's something wrong with the ntile support
> function, but I've not dug deeper.

Here is a simplified repro query:

select 1 from
(select ntile(s1.x) over () as c
from (select (select 1) as x) as s1) s
where s.c = 1;
ERROR: WindowFunc not found in subplan target lists

I think the problem is that in find_window_run_conditions() we fail to
check thoroughly whether the WindowFunc contains any potential subplan
nodes. We do have the check:

/* can't use it if there are subplans in the WindowFunc */
if (contain_subplans((Node *) wfunc))
return false;

... and the bug being reported here indicates that the current check is
insufficient, because a Var node inside the WindowFunc could potentially
belong to a subquery and reference a SubLink expression.

In the query above, the WindowFunc's argument, 's1.x', is actually an
EXPR_SUBLINK sublink. After we've pulled up subquery 's1', the
arguments of the two WindowFuncs (one in the target list of subquery
's', the other in the WindowClause's runCondition of subquery 's') would
be replaced with SubLink nodes. And then after we've converted SubLink
nodes to SubPlans, these two SubLink nodes would be replaced with
Params, but with different paramids.

Hmm, is there a way to detect in find_window_run_conditions() whether
the WindowFunc's argument references a SubLink node of a subquery that
hasn't been pulled up?

Thanks
Richard