Thread: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

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

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

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

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

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

>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?

Thanks David for investigating.

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!

I will try to reactivate my Windows development environment, but the error seems not to be related to operating system
version.

Many thanks for your quick reaction!

Hans Buschmann

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

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=cba5c70b956810c61b3778f7041f92fbb8065acb

David