On 12/2/24 10:46, Richard Guo wrote:
On Wed, Nov 27, 2024 at 5:45 PM Richard Guo <guofengli...@gmail.com> wrote:
I ended up using 'under the same lowest nulling outer join' to
keep consistent with the wording used elsewhere. Please see the
updated patch attached.
Commit e032e4c7d computes the nullingrel data for each leaf RTE, and
we can leverage that to determine if the referenced rel is under the
same lowest nulling outer join: we just need to check if the
nullingrels of the subquery RTE are a subset of those of the lateral
referenced rel. This eliminates the need to introduce
lowest_nullable_side. Please see attached.
Thanks for drawing attention to e032e4c7d. It is a really helpful
structure. I remember last year, we discussed [1] one sophisticated
subquery pull-up technique, and we needed exactly the same data - it was
too invasive to commit, and we committed only a small part of it. The
nullingrel_info structure may give this feature one more chance.
A couple of words about your patch. These few lines of code caused a lot
of discoveries, but in my opinion, they look fine. But I didn't find
negative tests, where we need to wrap a Var with PHV like the following:
explain (verbose, costs off)
select t1.q1, x from
int8_tbl t1 left join
(int8_tbl t2 left join
lateral (select t2.q2 as x, * from int8_tbl t3) ss on t2.q2 = ss.q1)
on t1.q1 = t2.q1
order by 1, 2;
If regression tests doesn't contain such check it would be nice to add.
[1]
https://www.postgresql.org/message-id/35c8a3e8-d080-dfa8-2be3-cf5fe702010a%40postgrespro.ru
--
regards, Andrei Lepikhov
