Re: Oversight in reparameterize_path_by_child leading to executor crash

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

> On Tue, Aug 8, 2023 at 12:44 PM Richard Guo <guofenglinux@gmail.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