Re: Oversight in reparameterize_path_by_child leading to executor crash

On Tue, Aug 8, 2023 at 7:34 PM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> On Tue, Aug 8, 2023 at 12:44 PM Richard Guo <guofenglinux(at)gmail(dot)com>
> wrote:
> > This is not an existing bug. Our current code (without this patch)
> > would always call create_nestloop_path() after the reparameterization
> > work has been done for the inner path. So we can safely check against
> > outer rel (not its topmost parent) in create_nestloop_path(). But now
> > with this patch, the reparameterization is postponed until createplan.c,
> > so we have to check against the topmost parent of outer rel in
> > create_nestloop_path(), otherwise we'd have duplicate clauses in the
> > final plan.
>
> Hmm. I am worried about the impact this will have when the nestloop
> path is created for two child relations. Your code will still pick the
> top_parent_relids which seems odd. Have you tested that case?

Well, the way it's working is that for child rels all parameterized
paths arriving at try_nestloop_path (or try_partial_nestloop_path) are
parameterized by top parents at first. Then our current code (without
this patch) applies reparameterize_path_by_child to the inner path
before calling create_nestloop_path(). So in create_nestloop_path() the
inner path is parameterized by child rel. That's why we check against
outer rel itself not its top parent there.

With this patch, the reparameterize_path_by_child work is postponed
until createplan time, so in create_nestloop_path() the inner path is
still parameterized by top parent. So we have to check against the top
parent of outer rel.

I think the query shown upthread is sufficient to verify this theory.

regression=# explain (costs off)
select * from prt1 t1 left join lateral
(select t1.a as t1a, t2.* from prt1 t2) s on t1.a = s.a;
QUERY PLAN
---------------------------------------------------------
Append
-> Nested Loop Left Join
-> Seq Scan on prt1_p1 t1_1
-> Index Scan using iprt1_p1_a on prt1_p1 t2_1
Index Cond: (a = t1_1.a)
-> Nested Loop Left Join
-> Seq Scan on prt1_p2 t1_2
-> Index Scan using iprt1_p2_a on prt1_p2 t2_2
Index Cond: (a = t1_2.a)
-> Nested Loop Left Join
-> Seq Scan on prt1_p3 t1_3
-> Index Scan using iprt1_p3_a on prt1_p3 t2_3
Index Cond: (a = t1_3.a)
(13 rows)

> Please add this to the next commitfest.

Done here https://commitfest.postgresql.org/44/4477/

Thanks
Richard