On Wed, Jun 11, 2025 at 3:14 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
> The following query:
> create table tbl1(a int);
> create table tbl2(b int);
> create table tbl3(c int);
> select * from tbl1 left join
> (select case when a = 0 then 0 else subq_3.cc end from tbl1,
> lateral (select 1 from tbl2 t1, tbl2 t2, tbl2 t3, tbl2 t4) subq_1,
> lateral (
> select tbl3.c as cc from tbl3, tbl2 t1, tbl2 t2,
> lateral (select c, a from tbl2 limit 1) subq_2
> ) as subq_3
> ) subq_4 on true;
> ends up with:
> ERROR: XX000: failed to build any 4-way joins
Thanks for the report. Here's a simplified repro.
create table t (a int);
set from_collapse_limit to 2;
select * from t t1 left join
(select coalesce(a+x) from t t2,
lateral (select t3.a as x from t t3, lateral (select t2.a, t3.a
offset 0) s))
on true;
ERROR: failed to build any 2-way joins
In this query, the join between t3 and s is placed into a separate
join sub-problem due to the from_collapse_limit. This join is deemed
not legal by join_is_legal(), as have_dangerous_phv() thinks the PHV
could pose a hazard as described in that function's comment. As a
result, no join could be built for this sub-problem.
It seems to me that there are some loose ends in the logic of
have_dangerous_phv(). In its comment, it says:
* (Note that we can still make use of A's parameterized
* path with pre-joined B+C as the outer rel. have_join_order_restriction()
* ensures that we will consider making such a join even if there are not
* other reasons to do so.)
However, if B and C end up in different sub-joinlists, it becomes
impossible to pre-join B+C.
In the query above, the PHV's minimum eval_at set includes t2 and t3.
But since t2 and t3 belong to different sub-joinlists, we have no way
to pre-join t2+t3 first and then join the result with s.
No idea how to fix this though. Any thoughts?
Thanks
Richard
