pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1
7+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2021-05-25 cba5c70b95 Fix setrefs.c code for Result Cache nodes
* BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1
@ 2021-05-22 16:20 PG Bug reporting form <noreply@postgresql.org>
2021-05-22 21:09 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2021-05-22 16:20 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: buschmann@nidsa.net
The following bug has been logged on the website:
Bug reference: 17030
Logged by: Hans Buschmann
Email address: buschmann@nidsa.net
PostgreSQL version: 14beta1
Operating system: Windows Server 2019 64 bit
Description:
On Windows I installed the new pg14beta1 downloaded from EDB Website.
xxxdb=# select version ();
version
---------------------------------------------------------------
PostgreSQL 14beta1, compiled by Visual C++ build 1914, 64-bit
(1 Zeile)
When testing our application I suddenly got the $subject error message:
ERROR: cannot decompile join alias var in plan tree
This occurs on a quite complex CTE query of the style (here only a
simplified layout to give an idea):
with
qxxx as (
select a,b,c
from taba
left join tabb
...)
select xmlelement (name sel,
xmlelement(name bbb
...
))
from (select * from qxxx order by colorder) as qxo
)
This particular query (generating an XML-tree) worked for about 5 years on
all newest minor versions from 9.6 to 13.3.
I know that this is not an reproduceble case, but I can supply the execution
plan from pg13.3 if needed.
Thanks for investigation
Hans Buschmann
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1
2021-05-22 16:20 BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 PG Bug reporting form <noreply@postgresql.org>
@ 2021-05-22 21:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-23 12:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-05-22 21:09 UTC (permalink / raw)
To: buschmann@nidsa.net; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> When testing our application I suddenly got the $subject error message:
> ERROR: cannot decompile join alias var in plan tree
That's surely a bug, but there's little we can do about it without
a self-contained test case.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1
2021-05-22 16:20 BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 PG Bug reporting form <noreply@postgresql.org>
2021-05-22 21:09 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-05-23 12:47 ` David Rowley <dgrowleyml@gmail.com>
2021-05-23 12:58 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Rowley @ 2021-05-23 12:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: buschmann@nidsa.net, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Sun, 23 May 2021 at 09:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > When testing our application I suddenly got the $subject error message:
> > ERROR: cannot decompile join alias var in plan tree
>
> That's surely a bug, but there's little we can do about it without
> a self-contained test case.
It fails for me with a fresh database with:
explain select * from pg_attribute a where attname not in(select
column_name from information_schema.columns);
I suspect Result Cache is to blame here and that I've done something
wrong in setrefs.c.
I can look into this a bit more in the morning. It's not a good time
of day right now to debug setrefs.c
David
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1
2021-05-22 16:20 BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 PG Bug reporting form <noreply@postgresql.org>
2021-05-22 21:09 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 Tom Lane <tgl@sss.pgh.pa.us>
2021-05-23 12:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
@ 2021-05-23 12:58 ` David Rowley <dgrowleyml@gmail.com>
2021-05-24 03:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Rowley @ 2021-05-23 12:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: buschmann@nidsa.net, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, 24 May 2021 at 00:47, David Rowley <dgrowleyml@gmail.com> wrote:
> I can look into this a bit more in the morning. It's not a good time
> of day right now to debug setrefs.c
It *may* just be a missing call to set_upper_references() for Result
Cache. i.e. the attached. But I will need to look harder in the
morning. Following setrefs.c gives me a headache at the best of times.
I've not addressed the join.sql regression test changes in the patch yet.
David
Attachments:
set_upper_references_for_resultcache.patch (application/octet-stream, 527B) download
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 9f40ed77e6..7c148bfc27 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -756,6 +756,8 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
{
ResultCache *rcplan = (ResultCache *) plan;
+ set_upper_references(root, plan, rtoffset);
+
rcplan->param_exprs = fix_scan_list(root, rcplan->param_exprs,
rtoffset,
NUM_EXEC_TLIST(plan));
* Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1
2021-05-22 16:20 BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 PG Bug reporting form <noreply@postgresql.org>
2021-05-22 21:09 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 Tom Lane <tgl@sss.pgh.pa.us>
2021-05-23 12:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-23 12:58 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
@ 2021-05-24 03:47 ` David Rowley <dgrowleyml@gmail.com>
2021-05-24 08:57 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Rowley @ 2021-05-24 03:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; buschmann@nidsa.net; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, 24 May 2021 at 00:58, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 24 May 2021 at 00:47, David Rowley <dgrowleyml@gmail.com> wrote:
> > I can look into this a bit more in the morning. It's not a good time
> > of day right now to debug setrefs.c
>
> It *may* just be a missing call to set_upper_references() for Result
> Cache. i.e. the attached. But I will need to look harder in the
> morning. Following setrefs.c gives me a headache at the best of times.
I've had a look at this today and I think the patch attached is what
needs to be done to fix the issue.
Hans, I see you're on Windows, but wondering if you have a build
environment around to test to see if this patch fixes the error for
your case?
David
Attachments:
resultcache_setrefs_fix.patch (application/octet-stream, 3.3K) download
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 9f40ed77e6..61ccfd300b 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -756,6 +756,12 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
{
ResultCache *rcplan = (ResultCache *) plan;
+ /*
+ * Result Cache does not evaluate its targetlist. It just
+ * uses the same targetlist from its outer subnode.
+ */
+ set_dummy_tlist_references(plan, rtoffset);
+
rcplan->param_exprs = fix_scan_list(root, rcplan->param_exprs,
rtoffset,
NUM_EXEC_TLIST(plan));
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 86fd3907c5..fec0325e73 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4216,8 +4216,8 @@ where t1.f1 = ss.f1;
QUERY PLAN
--------------------------------------------------
Nested Loop
- Output: t1.f1, i8.q1, i8.q2, q1, f1
- Join Filter: (t1.f1 = f1)
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
+ Join Filter: (t1.f1 = t2.f1)
-> Nested Loop Left Join
Output: t1.f1, i8.q1, i8.q2
-> Seq Scan on public.text_tbl t1
@@ -4228,7 +4228,7 @@ where t1.f1 = ss.f1;
Output: i8.q1, i8.q2
Filter: (i8.q2 = 123)
-> Result Cache
- Output: q1, f1
+ Output: (i8.q1), t2.f1
Cache Key: i8.q1
-> Limit
Output: (i8.q1), t2.f1
@@ -4255,13 +4255,13 @@ select * from
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
lateral (select ss1.* from text_tbl t3 limit 1) as ss2
where t1.f1 = ss2.f1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
- Output: t1.f1, i8.q1, i8.q2, q1, f1, q1, f1
- Join Filter: (t1.f1 = f1)
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1)
+ Join Filter: (t1.f1 = (t2.f1))
-> Nested Loop
- Output: t1.f1, i8.q1, i8.q2, q1, f1
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
-> Nested Loop Left Join
Output: t1.f1, i8.q1, i8.q2
-> Seq Scan on public.text_tbl t1
@@ -4272,19 +4272,19 @@ where t1.f1 = ss2.f1;
Output: i8.q1, i8.q2
Filter: (i8.q2 = 123)
-> Result Cache
- Output: q1, f1
+ Output: (i8.q1), t2.f1
Cache Key: i8.q1
-> Limit
Output: (i8.q1), t2.f1
-> Seq Scan on public.text_tbl t2
Output: i8.q1, t2.f1
-> Result Cache
- Output: q1, f1
- Cache Key: q1, f1
+ Output: ((i8.q1)), (t2.f1)
+ Cache Key: (i8.q1), t2.f1
-> Limit
- Output: (q1), (f1)
+ Output: ((i8.q1)), (t2.f1)
-> Seq Scan on public.text_tbl t3
- Output: q1, f1
+ Output: (i8.q1), t2.f1
(28 rows)
select * from
* Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1
2021-05-22 16:20 BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 PG Bug reporting form <noreply@postgresql.org>
2021-05-22 21:09 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 Tom Lane <tgl@sss.pgh.pa.us>
2021-05-23 12:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-23 12:58 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-24 03:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
@ 2021-05-24 08:57 ` David Rowley <dgrowleyml@gmail.com>
2021-05-25 00:52 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Rowley @ 2021-05-24 08:57 UTC (permalink / raw)
To: Hans Buschmann <buschmann@nidsa.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, 24 May 2021 at 20:53, Hans Buschmann <buschmann@nidsa.net> wrote:
> I managed to manually patch setrefs.c according to your patchset on a Linux test installation.
>
> The query/explain query (without the unneeded XML-stuff) runs fine now, no more error!
Great. I'll take another look at the patch in my morning (UTC+12) and
see about getting it in.
> Many thanks for your quick reaction!
Thanks for testing the patch.
David
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1
2021-05-22 16:20 BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 PG Bug reporting form <noreply@postgresql.org>
2021-05-22 21:09 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 Tom Lane <tgl@sss.pgh.pa.us>
2021-05-23 12:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-23 12:58 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-24 03:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-24 08:57 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
@ 2021-05-25 00:52 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: David Rowley @ 2021-05-25 00:52 UTC (permalink / raw)
To: Hans Buschmann <buschmann@nidsa.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, 24 May 2021 at 20:57, David Rowley <dgrowleyml@gmail.com> wrote:
>
> > The query/explain query (without the unneeded XML-stuff) runs fine now, no more error!
>
> Great. I'll take another look at the patch in my morning (UTC+12) and
> see about getting it in.
Pushed.
Thanks again for reporting.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cba5c70b956810c61b3778f7041f92fbb8065...
David
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-05-25 00:52 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-22 16:20 BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 PG Bug reporting form <noreply@postgresql.org>
2021-05-22 21:09 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 Tom Lane <tgl@sss.pgh.pa.us>
2021-05-23 12:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-23 12:58 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-24 03:47 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-24 08:57 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 David Rowley <dgrowleyml@gmail.com>
2021-05-25 00:52 ` Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1 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