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

On Thu, Jan 25, 2024 at 1:14 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> Sounds a bit invasive for back branches, but wondering if we couldn't
> just modify window_ntile_support() to reject any ntile args other than
> Consts. count(*), row_number(), rank(), dense_rank(), percent_rank()
> and percent_rank() all can't suffer from this issue as they don't have
> an argument. count(expr) would need to have something done to stop
> the same issue from occurring. Maybe int8inc_support() could just set
> req->monotonic = MONOTONICFUNC_NONE if the req->window_func has an
> arg, effectively disabling the optimisation for count(expr).

You're right that count(expr) also suffers from this issue.

select 1 from
(select count(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

For back branches, the idea of modifying window_ntile_support() and
int8inc_support() to reject any non-pseudoconstant args also seems
reasonable to me. One thing I noticed is that sometimes it's not easy
to tell whether the arg is pseudoconstant or not in the support
functions, because a pseudoconstant is not necessarily being type of
Const. For instance, count(1::text) is a CoerceViaIO, and
ntile(1.0::int) is a FuncExpr. But these are very corner cases and I
think we can just ignore them.

Thanks
Richard