[sqlsmith] Planner error on lateral joins

Started by Andreas Seltenreichover 5 years ago3 messageshackers
Jump to latest
#1Andreas Seltenreich
seltenreich@gmx.de

Hi,

testing with sqlsmith on master at 3df51ca8b3 produced one instance of
the following error:

ERROR: failed to build any 6-way joins

I can reproduce it on a fresh regression database with the query below.
These were last logged in 2015. Back then, it resulted in this commit:

http://git.postgresql.org/pg/commitdiff/cfe30a72fa80528997357cb0780412736767e8c4

regards,
Andreas

select * from
(select sample_1.a as c0
from fkpart5.fk2 as sample_1) as subq_0,
lateral (select 1
from
(select
subq_0.c0 as c3,
subq_5.c0 as c7,
sample_2.b as c9
from
public.brin_test as sample_2,
lateral (select
subq_3.c1 as c0
from
fkpart5.pk3 as sample_3,
lateral (select
sample_2.a as c0,
sample_3.a as c1
from
public.rtest_interface as ref_0
) as subq_1,
lateral (select
subq_1.c1 as c1
from
public.alter_table_under_transition_tables as ref_1
) as subq_3
) as subq_5) as subq_6
right join public.gtest30_1 as sample_6
on (true)
where subq_6.c7 = subq_6.c3) as subq_7;

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Andreas Seltenreich (#1)
Re: [sqlsmith] Planner error on lateral joins

Andreas Seltenreich <seltenreich@gmx.de> writes:

testing with sqlsmith on master at 3df51ca8b3 produced one instance of
the following error:
ERROR: failed to build any 6-way joins

Thanks for the test case! The attached modification to use only
longstanding test tables fails back to 9.5, but succeeds in 9.4.
I've not tried to bisect yet.

regards, tom lane

Attachments:

lateraljoinbug.sqltext/plain; charset=us-ascii; name=lateraljoinbug.sqlDownload
#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tom Lane (#2)
Re: [sqlsmith] Planner error on lateral joins

I wrote:

Andreas Seltenreich <seltenreich@gmx.de> writes:

testing with sqlsmith on master at 3df51ca8b3 produced one instance of
the following error:
ERROR: failed to build any 6-way joins

Thanks for the test case! The attached modification to use only
longstanding test tables fails back to 9.5, but succeeds in 9.4.
I've not tried to bisect yet.

Seems to be a missed consideration in add_placeholders_to_joinrel.
The attached fixes it for me.

regards, tom lane

Attachments:

fix-lateral-relids-for-unused-PHV.patchtext/x-diff; charset=us-ascii; name=fix-lateral-relids-for-unused-PHV.patchDownload+27-11