From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
---|---|
To: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)lists(dot)postgresql(dot)org, "Finnerty, Jim" <jfinnert(at)amazon(dot)com> |
Subject: | Re: Making Vars outer-join aware |
Date: | 2023-02-13 07:33:15 |
Message-ID: | CAMbWs4-K8_yqARnrmRB5=xAWTJGgTpimQinvaa-k6c8f6Hideg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Feb 13, 2023 at 7:58 AM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> The patch broke this query:
>
> select from pg_inherits inner join information_schema.element_types
> right join (select from pg_constraint as sample_2) on true
> on false, lateral (select scope_catalog, inhdetachpending from
> pg_publication_namespace limit 3);
> ERROR: could not devise a query plan for the given query
Thanks for the report! I've looked at it a little bit and traced down
to function have_unsafe_outer_join_ref(). The comment there says
* In practice, this test never finds a problem ...
* ...
* It still seems worth checking
* as a backstop, but we don't go to a lot of trouble: just reject if the
* unsatisfied part includes any outer-join relids at all.
This seems not correct as showed by the counterexample. ISTM that we
need to do the check honestly as what the other comment says
* If the parameterization is only partly satisfied by the outer rel,
* the unsatisfied part can't include any outer-join relids that could
* null rels of the satisfied part.
The NOT_USED part of code is doing this check. But I think we need a
little tweak. We should check the nullable side of related outer joins
against the satisfied part, rather than inner_paramrels. Maybe
something like attached.
However, this test seems to cost some cycles after the change. So I
wonder if it's worthwhile to perform it, considering that join order
restrictions should be able to guarantee there is no problem here.
BTW, here is a simplified query that can trigger this issue on HEAD.
select * from t1 inner join t2 left join (select null as c from t3 left
join t4 on true) as sub on true on true, lateral (select c, t1.a from t5
offset 0 ) ss;
Thanks
Richard
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Fix-for-have_unsafe_outer_join_ref.patch | application/octet-stream | 1.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2023-02-13 07:37:10 | Re: recovery modules |
Previous Message | Drouvot, Bertrand | 2023-02-13 07:09:50 | Re: Reconcile stats in find_tabstat_entry() and get rid of PgStat_BackendFunctionEntry |