On Mon, Jan 22, 2024 at 11:44 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.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@postgresql.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?
