pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17709: Regression in PG15 with window functions - "WindowFunc not found in subplan target lists"
5+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2022-12-10 94985c2102 04788ee4c5 Add subquery pullup handling for WindowClause runCondition
* BUG #17709: Regression in PG15 with window functions - "WindowFunc not found in subplan target lists"
@ 2022-12-09 08:46 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2022-12-09 08:46 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: makhmutov@gmail.com
The following bug has been logged on the website:
Bug reference: 17709
Logged by: Alexey Makhmutov
Email address: makhmutov@gmail.com
PostgreSQL version: 15.1
Operating system: Ubuntu 20.04
Description:
Following query works fine on PG14, but produce error "WindowFunc not found
in subplan target lists" on PG15:
select 1
from
(
select count(case t1.a when 1 then 1 else null end) over (partition by
t2.b) c
from (select 1 a) t1, (select 1 b) t2
) t
where t.c = 1
On PG14 this query produce expected result (1), but on PG15.1 it produces
following error: "ERROR: WindowFunc not found in subplan target lists"
As of 8 December 2022, this problem could be reproduced on latest
REL_15_STABLE and HEAD (16dev) builds.
This seems to be result of functionality
https://github.com/postgres/postgres/commit/9d9c02ccd1aea8e9131d8f4edb21bf1687e40782
introduced in PG15.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17709: Regression in PG15 with window functions - "WindowFunc not found in subplan target lists"
@ 2022-12-09 11:00 Richard Guo <guofenglinux@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Guo @ 2022-12-09 11:00 UTC (permalink / raw)
To: makhmutov@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Dec 9, 2022 at 5:42 PM PG Bug reporting form <noreply@postgresql.org>
wrote:
> Following query works fine on PG14, but produce error "WindowFunc not found
> in subplan target lists" on PG15:
>
> select 1
> from
> (
> select count(case t1.a when 1 then 1 else null end) over (partition by
> t2.b) c
> from (select 1 a) t1, (select 1 b) t2
> ) t
> where t.c = 1
Thanks for the report! I can reproduce this issue.
The WindowFunc within runCondition comes from the query's targetList,
before we pull up subquery 't1'. Then when it comes to pulling up
subquery 't1', we perform pullup variable replacement for the query's
targetList but not for runCondition in the query's windowClause. I
believe that's how this error is triggered.
Below is how we can fix this issue.
inline patch 1: src/backend/optimizer/prep/prepjointree.c (+8/-0 lines) download inline-1.patch
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2134,6 +2134,16 @@ perform_pullup_replace_vars(PlannerInfo *root,
* can't contain any references to a subquery.
*/
}
+ if (parse->windowClause)
+ {
+ foreach(lc, parse->windowClause)
+ {
+ WindowClause *wclause = (WindowClause *) lfirst(lc);
+
+ wclause->runCondition = (List *)
+ pullup_replace_vars((Node *) wclause->runCondition,
* Re: BUG #17709: Regression in PG15 with window functions - "WindowFunc not found in subplan target lists"
@ 2022-12-09 11:52 David Rowley <dgrowleyml@gmail.com>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David Rowley @ 2022-12-09 11:52 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: makhmutov@gmail.com; pgsql-bugs@lists.postgresql.org
On Sat, 10 Dec 2022 at 00:00, Richard Guo <guofenglinux@gmail.com> wrote:
> Thanks for the report! I can reproduce this issue.
>
> The WindowFunc within runCondition comes from the query's targetList,
> before we pull up subquery 't1'. Then when it comes to pulling up
> subquery 't1', we perform pullup variable replacement for the query's
> targetList but not for runCondition in the query's windowClause. I
> believe that's how this error is triggered.
>
> Below is how we can fix this issue.
>
> --- a/src/backend/optimizer/prep/prepjointree.c
> +++ b/src/backend/optimizer/prep/prepjointree.c
> @@ -2134,6 +2134,16 @@ perform_pullup_replace_vars(PlannerInfo *root,
> * can't contain any references to a subquery.
> */
> }
> + if (parse->windowClause)
> + {
> + foreach(lc, parse->windowClause)
> + {
> + WindowClause *wclause = (WindowClause *) lfirst(lc);
> +
> + wclause->runCondition = (List *)
> + pullup_replace_vars((Node *) wclause->runCondition, rvcontext);
> + }
> + }
Thanks for having a look at this.
I think what you have above fixes the bulk of the issue, but there's
still a bit more to do to properly fix the reported case.
The additional thing that seems to cause the reported error is that
once the subquery is pulled up, the run condition also needs a round
of constant folding done. See subquery_planner() around line 827. The
problem is that the target list's WindowFunc ends up with count(1)
over .., but the run condition's one is left as count(case 1 when 1
then 1 else null end), which preprocess_expression() will fold into
the same as what's in the target list.
I'm now wondering if WindowClause.runCondition should be of type Node
* instead of List *. I'd have imagined I should be passing the type of
EXPRKIND_QUAL to preprocess_expression's type, but canonicalize_qual()
does not like Lists.
David
Attachments:
runcondition_fix_v2.patch (text/plain, 1.2K) download
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 15aa9c5087..5dd4f92720 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -833,6 +833,9 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
EXPRKIND_LIMIT);
wc->endOffset = preprocess_expression(root, wc->endOffset,
EXPRKIND_LIMIT);
+ wc->runCondition = (List *) preprocess_expression(root,
+ (Node *) wc->runCondition,
+ EXPRKIND_TARGET);
}
parse->limitOffset = preprocess_expression(root, parse->limitOffset,
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 57fea35e44..f2ff3dbffe 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2187,6 +2187,17 @@ perform_pullup_replace_vars(PlannerInfo *root,
pullup_replace_vars((Node *) otherrte->joinaliasvars,
rvcontext);
}
+
+ foreach(lc, parse->windowClause)
+ {
+ WindowClause *wclause = lfirst_node(WindowClause, lc);
+
+ if (wclause->runCondition)
+ wclause->runCondition = (List *)
+ pullup_replace_vars((Node *) wclause->runCondition,
+ rvcontext);
+ }
+
}
/*
* Re: BUG #17709: Regression in PG15 with window functions - "WindowFunc not found in subplan target lists"
@ 2022-12-09 14:01 Richard Guo <guofenglinux@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Guo @ 2022-12-09 14:01 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: makhmutov@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Dec 9, 2022 at 7:53 PM David Rowley <dgrowleyml@gmail.com> wrote:
> The additional thing that seems to cause the reported error is that
> once the subquery is pulled up, the run condition also needs a round
> of constant folding done. See subquery_planner() around line 827. The
> problem is that the target list's WindowFunc ends up with count(1)
> over .., but the run condition's one is left as count(case 1 when 1
> then 1 else null end), which preprocess_expression() will fold into
> the same as what's in the target list.
Yes exactly. That's what we also have to do. I was debugging with a
simplified version of the query with the WindowFunc as count(t1.a) over
(...) and did not realize constant folding is also needed for the
runCondition.
> I'm now wondering if WindowClause.runCondition should be of type Node
> * instead of List *. I'd have imagined I should be passing the type of
> EXPRKIND_QUAL to preprocess_expression's type, but canonicalize_qual()
> does not like Lists.
I'm not sure about this. From how the runCondition is constructed in
find_window_run_conditions, it seems there is no need to canonicalize
it.
Thanks
Richard
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17709: Regression in PG15 with window functions - "WindowFunc not found in subplan target lists"
@ 2022-12-10 06:29 David Rowley <dgrowleyml@gmail.com>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David Rowley @ 2022-12-10 06:29 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: makhmutov@gmail.com; pgsql-bugs@lists.postgresql.org
On Sat, 10 Dec 2022 at 03:01, Richard Guo <guofenglinux@gmail.com> wrote:
>
>
> On Fri, Dec 9, 2022 at 7:53 PM David Rowley <dgrowleyml@gmail.com> wrote:
>>
>> The additional thing that seems to cause the reported error is that
>> once the subquery is pulled up, the run condition also needs a round
>> of constant folding done. See subquery_planner() around line 827. The
>> problem is that the target list's WindowFunc ends up with count(1)
>> over .., but the run condition's one is left as count(case 1 when 1
>> then 1 else null end), which preprocess_expression() will fold into
>> the same as what's in the target list.
>
>
> Yes exactly. That's what we also have to do. I was debugging with a
> simplified version of the query with the WindowFunc as count(t1.a) over
> (...) and did not realize constant folding is also needed for the
> runCondition.
I made a few small minor adjustments and pushed the patch.
Thanks for the report Alexey and to Richard for looking into this.
David
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-12-10 06:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-09 08:46 BUG #17709: Regression in PG15 with window functions - "WindowFunc not found in subplan target lists" PG Bug reporting form <noreply@postgresql.org>
2022-12-09 11:00 ` Richard Guo <guofenglinux@gmail.com>
2022-12-09 11:52 ` David Rowley <dgrowleyml@gmail.com>
2022-12-09 14:01 ` Richard Guo <guofenglinux@gmail.com>
2022-12-10 06:29 ` David Rowley <dgrowleyml@gmail.com>
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