| From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
|---|---|
| To: | exclusion(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Re: BUG #18953: Planner fails to build plan for complex query with LATERAL references |
| Date: | 2025-06-11 08:33:54 |
| Message-ID: | CAMbWs49XTfPD9OCw9xWsRtbiNYgQ-Nau_hHy9UbbGP-w2V=ANQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
On Wed, Jun 11, 2025 at 3:14 AM PG Bug reporting form
<noreply(at)postgresql(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Anthonin Bonnefoy | 2025-06-11 09:04:36 | Re: BUG #18947: TRAP: failed Assert("len_to_wrt >= 0") in pg_stat_statements |
| Previous Message | Никита Калинин | 2025-06-11 03:58:32 | Re: BUG #18944: Assertion Failure in psql with idle_session_timeout Set |