From: Tom Lane <tgl@sss.pgh.pa.us>
To: zuming.jiang@inf.ethz.ch
cc: David Rowley <dgrowleyml@gmail.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18305: Unexpected error: "WindowFunc not found in subplan
 target lists" triggered by subqueries
In-reply-to: <18305-33c49b4c830b37b3@postgresql.org>
References: <18305-33c49b4c830b37b3@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Mon, 22 Jan 2024 12:15:00 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3738974.1705938286.1@sss.pgh.pa.us>
Date: Mon, 22 Jan 2024 10:44:46 -0500
Message-ID: <3738975.1705938286@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3738975.1705938286%40sss.pgh.pa.us>
Precedence: bulk

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.

			regards, tom lane