A bug in make_outerjoininfo

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: A bug in make_outerjoininfo
Date: 2023-02-07 07:11:06
Message-ID: CAMbWs4_9Ogif8vH_GSFbKFw+RBNJRQe=ryiK0JKhRTPiSacCfA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In cases where we have any clauses between two outer joins, these
clauses should be treated as degenerate clauses in the upper OJ, and
they may prevent us from re-ordering the two outer joins. Previously we
have the flag 'delay_upper_joins' to help avoid the re-ordering in such
cases.

In b448f1c8 remove_useless_result_rtes will remove useless FromExprs and
merge its quals up to parent. This makes flag 'delay_upper_joins' not
necessary any more if the clauses between the two outer joins come from
FromExprs. However, if the clauses between the two outer joins come
from JoinExpr of an inner join, it seems we have holes in preserving
ordering. As an example, consider

create table t (a int unique);

select * from t t1 left join (t t2 left join t t3 on t2.a = t3.a) inner
join t t4 on coalesce(t3.a,1) = t4.a on t1.a = t2.a;

When building SpecialJoinInfo for outer join t1/t2, make_outerjoininfo
thinks identity 3 applies between OJ t1/t2 and OJ t2/t3, which is wrong
as there is an inner join between them.

This query will trigger the assertion for cross-checking on nullingrels
in search_indexed_tlist_for_var.

Thanks
Richard

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2023-02-07 07:11:17 Re: Perform streaming logical transactions by background workers and parallel apply
Previous Message Peter Eisentraut 2023-02-07 07:09:57 Re: Remove some useless casts to (void *)