Thread: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 18277 Logged by: Zuming Jiang Email address: zuming.jiang@inf.ethz.ch PostgreSQL version: 16.1 Operating system: Ubuntu 20.04 Description: My fuzzer finds a bug in Postgres 17devel, which triggers an unexpected error "ERROR: WindowFunc not found in subplan target lists". --- Set up database --- create table exeet_t184 (vkey int4); create view exeet_t188 as select ntile(ref_1.vkey) over w0 as c_1 from (exeet_t184 as ref_0 left outer join exeet_t184 as ref_1 on (ref_0.vkey = ref_1.vkey)) where ref_0.vkey <> ref_1.vkey window w0 as (partition by ref_0.vkey); The fuzzer generates a test case: --- Test case --- select (case when ('false' <> (SELECT nummultirange() && 'empty'::numrange)) then (case when (1 in ( select 1 as c_0 from exeet_t188 as ref_12 where 1 between ref_12.c_1 and 1)) then 1 else 1 end ) else 1 end) as c_5; --- Expected behavior --- The test case should not trigger any error. --- Actual behavior --- The test case trigger an error: ERROR: WindowFunc not found in subplan target lists --- Postgres version --- Github commit: d3c5f37dd543498cc7c678815d3921823beec9e9 Version: PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit --- Platform information --- Platform: Ubuntu 20.04 Kernel: Linux 5.4.0-147-generic
Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
From
Tom Lane
Date:
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". Thanks for the report! Bisecting says this broke at 2489d76c4906f4461a364ca8ad7e0751ead8aa0d is the first bad commit commit 2489d76c4906f4461a364ca8ad7e0751ead8aa0d Author: Tom Lane <tgl@sss.pgh.pa.us> Date: Mon Jan 30 13:16:20 2023 -0500 Make Vars be outer-join-aware. so it's my fault and I'll take a closer look in a bit. regards, tom lane
Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
From
Richard Guo
Date:
On Tue, Jan 9, 2024 at 7:00 AM 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".
Thanks for the report! Bisecting says this broke at
2489d76c4906f4461a364ca8ad7e0751ead8aa0d is the first bad commit
commit 2489d76c4906f4461a364ca8ad7e0751ead8aa0d
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon Jan 30 13:16:20 2023 -0500
Make Vars be outer-join-aware.
I think this is caused by that query_tree_mutator fails to mutate
runCondition for WindowClause. So when we've reduced the ref_0/ref_1
left join to inner join, we fail to remove references to this join as
nulling rels in the run condition '1 >= ntile(ref_1.vkey) OVER (?)'.
So maybe we can fix this issue by:
@@ -3641,6 +3646,7 @@ query_tree_mutator_impl(Query *query,
FLATCOPY(newnode, wc, WindowClause);
MUTATE(newnode->startOffset, wc->startOffset, Node *);
MUTATE(newnode->endOffset, wc->endOffset, Node *);
+ MUTATE(newnode->runCondition, wc->runCondition, List *);
I think we should also consider WindowClause->runCondition in
query_tree_walker, expression_tree_mutator and expression_tree_walker.
Hence, proposed the attached patch.
Thanks
Richard
runCondition for WindowClause. So when we've reduced the ref_0/ref_1
left join to inner join, we fail to remove references to this join as
nulling rels in the run condition '1 >= ntile(ref_1.vkey) OVER (?)'.
So maybe we can fix this issue by:
@@ -3641,6 +3646,7 @@ query_tree_mutator_impl(Query *query,
FLATCOPY(newnode, wc, WindowClause);
MUTATE(newnode->startOffset, wc->startOffset, Node *);
MUTATE(newnode->endOffset, wc->endOffset, Node *);
+ MUTATE(newnode->runCondition, wc->runCondition, List *);
I think we should also consider WindowClause->runCondition in
query_tree_walker, expression_tree_mutator and expression_tree_walker.
Hence, proposed the attached patch.
Thanks
Richard
Attachment
Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
From
Tom Lane
Date:
Richard Guo <guofenglinux@gmail.com> writes: > On Tue, Jan 9, 2024 at 7:00 AM 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". > I think this is caused by that query_tree_mutator fails to mutate > runCondition for WindowClause. Ugh! That seems quite horrid. David, was there a reason to not do that, or was it just an oversight? > I think we should also consider WindowClause->runCondition in > query_tree_walker, expression_tree_mutator and expression_tree_walker. Offhand, +1. If it has the form of an ordinary expression tree then it really needs to be processed in all these places. regards, tom lane