pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
4+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2024-01-10 d641b827af ee95532186 c3f52fd5d7 Handle WindowClause.runCondition in tree walker/mutator functions.
* BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
@ 2024-01-08 21:09 PG Bug reporting form <noreply@postgresql.org>
2024-01-08 22:59 ` Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2024-01-08 21:09 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: zuming.jiang@inf.ethz.ch
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
2024-01-08 21:09 BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func PG Bug reporting form <noreply@postgresql.org>
@ 2024-01-08 22:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-09 07:35 ` Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2024-01-08 22:59 UTC (permalink / raw)
To: zuming.jiang@inf.ethz.ch; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
2024-01-08 21:09 BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func PG Bug reporting form <noreply@postgresql.org>
2024-01-08 22:59 ` Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-01-09 07:35 ` Richard Guo <guofenglinux@gmail.com>
2024-01-09 21:34 ` Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Guo @ 2024-01-09 07:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: zuming.jiang@inf.ethz.ch; pgsql-bugs@lists.postgresql.org
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
Attachments:
v1-0001-Fix-bug-18277.patch (application/octet-stream, 3.7K) download
From 69f1187aa85d34385fc23d07d32978be4efbd42d Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 9 Jan 2024 15:29:02 +0800
Subject: [PATCH v1] Fix bug 18277
---
src/backend/nodes/nodeFuncs.c | 6 ++++++
src/test/regress/expected/window.out | 26 ++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 9 +++++++++
3 files changed, 41 insertions(+)
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 030463cb42..e1a5bc7e95 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2283,6 +2283,8 @@ expression_tree_walker_impl(Node *node,
return true;
if (WALK(wc->endOffset))
return true;
+ if (WALK(wc->runCondition))
+ return true;
}
break;
case T_CTECycleClause:
@@ -2627,6 +2629,8 @@ query_tree_walker_impl(Query *query,
return true;
if (WALK(wc->endOffset))
return true;
+ if (WALK(wc->runCondition))
+ return true;
}
}
@@ -3312,6 +3316,7 @@ expression_tree_mutator_impl(Node *node,
MUTATE(newnode->orderClause, wc->orderClause, List *);
MUTATE(newnode->startOffset, wc->startOffset, Node *);
MUTATE(newnode->endOffset, wc->endOffset, Node *);
+ MUTATE(newnode->runCondition, wc->runCondition, List *);
return (Node *) newnode;
}
break;
@@ -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 *);
resultlist = lappend(resultlist, (Node *) newnode);
}
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 2201740c18..60de2cbf96 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4181,6 +4181,32 @@ SELECT * FROM
sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3 | 1
(2 rows)
+-- Ensure we remove references to reduced outer joins as nulling rels in run
+-- conditions
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c
+ FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
+ WHERE e1.empno = e2.empno) s
+WHERE s.c = 1;
+ QUERY PLAN
+---------------------------------------------------------
+ Subquery Scan on s
+ Filter: (s.c = 1)
+ -> WindowAgg
+ Run Condition: (ntile(e2.salary) OVER (?) <= 1)
+ -> Sort
+ Sort Key: e1.depname
+ -> Merge Join
+ Merge Cond: (e1.empno = e2.empno)
+ -> Sort
+ Sort Key: e1.empno
+ -> Seq Scan on empsalary e1
+ -> Sort
+ Sort Key: e2.empno
+ -> Seq Scan on empsalary e2
+(14 rows)
+
-- Tests to ensure we don't push down the run condition when it's not valid to
-- do so.
-- Ensure we don't push down when the frame options show that the window
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 437e948d6c..678aa1a21c 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1368,6 +1368,15 @@ SELECT * FROM
FROM empsalary
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
+-- Ensure we remove references to reduced outer joins as nulling rels in run
+-- conditions
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c
+ FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
+ WHERE e1.empno = e2.empno) s
+WHERE s.c = 1;
+
-- Tests to ensure we don't push down the run condition when it's not valid to
-- do so.
--
2.31.0
* Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func
2024-01-08 21:09 BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func PG Bug reporting form <noreply@postgresql.org>
2024-01-08 22:59 ` Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func Tom Lane <tgl@sss.pgh.pa.us>
2024-01-09 07:35 ` Re: BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func Richard Guo <guofenglinux@gmail.com>
@ 2024-01-09 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2024-01-09 21:34 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; zuming.jiang@inf.ethz.ch; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-01-09 21:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-08 21:09 BUG #18277: Unexpected error: "WindowFunc not found in subplan target lists" triggered by JOIN and Window Func PG Bug reporting form <noreply@postgresql.org>
2024-01-08 22:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-09 07:35 ` Richard Guo <guofenglinux@gmail.com>
2024-01-09 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox