Horton

Thread

Commits

  1. Fix EXPLAIN output for subplans in MERGE.

  1. Broken EXPLAIN output for SubPlan in MERGE

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2024-03-12T18:43:27Z

    While playing around with EXPLAIN and SubPlans, I noticed that there's
a bug in how this is handled for MERGE. For example:

drop table if exists src, tgt, ref;
create table src (a int, b text);
create table tgt (a int, b text);
create table ref (a int);

explain (verbose, costs off)
merge into tgt t
  using (select (select r.a from ref r where r.a = s.a) a, b from src s) s
  on t.a = s.a
  when not matched then insert values (s.a, s.b);

                        QUERY PLAN
-----------------------------------------------------------
 Merge on public.tgt t
   ->  Merge Left Join
         Output: t.ctid, s.a, s.b, s.ctid
         Merge Cond: (((SubPlan 1)) = t.a)
         ->  Sort
               Output: s.a, s.b, s.ctid, ((SubPlan 1))
               Sort Key: ((SubPlan 1))
               ->  Seq Scan on public.src s
                     Output: s.a, s.b, s.ctid, (SubPlan 1)
                     SubPlan 1
                       ->  Seq Scan on public.ref r
                             Output: r.a
                             Filter: (r.a = s.a)
         ->  Sort
               Output: t.ctid, t.a
               Sort Key: t.a
               ->  Seq Scan on public.tgt t
                     Output: t.ctid, t.a
   SubPlan 2
     ->  Seq Scan on public.ref r_1
           Output: r_1.a
           Filter: (r_1.a = t.ctid)

The final filter condition "(r_1.a = t.ctid)" is incorrect, and should
be "(r_1.a = s.a)".

What's happening is that the right hand side of that filter expression
is an input Param node which get_parameter() tries to display by
calling find_param_referent() and then drilling down through the
ancestor node (the ModifyTable node) to try to find the real name of
the variable (s.a).

However, that isn't working properly for MERGE because the inner_plan
and inner_tlist of the corresponding deparse_namespace aren't set
correctly. Actually the inner_tlist is correct, but the inner_plan is
set to the ModifyTable node, whereas it needs to be the outer child
node -- in a MERGE, any references to the source relation will be
INNER_VAR references to the targetlist of the join node immediately
under the ModifyTable node.

So I think we want to do something like the attached.

Regards,
Dean

  2. Re: Broken EXPLAIN output for SubPlan in MERGE

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-03-21T09:23:13Z

    On 2024-Mar-12, Dean Rasheed wrote:

> While playing around with EXPLAIN and SubPlans, I noticed that there's
> a bug in how this is handled for MERGE. [...]

> However, that isn't working properly for MERGE because the inner_plan
> and inner_tlist of the corresponding deparse_namespace aren't set
> correctly. Actually the inner_tlist is correct, but the inner_plan is
> set to the ModifyTable node, whereas it needs to be the outer child
> node -- in a MERGE, any references to the source relation will be
> INNER_VAR references to the targetlist of the join node immediately
> under the ModifyTable node.

Hmm, interesting, thanks for fixing it (commit 33e729c5148c).  I remember
wondering whether the nodes ought to be set differently, and now I have to
admit that this

        if (((ModifyTable *) plan)->operation == CMD_MERGE)
            dpns->inner_plan = outerPlan(plan);

is very funny-looking.  But I didn't come up with any examples where it
mattered.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Thou shalt not follow the NULL pointer, for chaos and madness await
thee at its end." (2nd Commandment for C programmers)