On Mon, Feb 13, 2023 at 7:58 AM Justin Pryzby <pry...@telsasoft.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
v1-0001-Fix-for-have_unsafe_outer_join_ref.patch
Description: Binary data
