"variable not found in subplan target list"

Alvaro Herrera <alvherre@alvh.no-ip.org>

From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-03-28T11:22:48Z
Lists: pgsql-hackers
I have to run now so can't dissect it, but while running sqlsmith on the
SQL/JSON patch after Justin's report, I got $SUBJECT in this query:

MERGE INTO public.target_parted as target_0
    USING (select  
          subq_0.c5 as c0, 
          subq_0.c0 as c1, 
          ref_0.a as c2, 
          subq_0.c1 as c3, 
          subq_0.c9 as c4, 
          (select c from public.prt2_m_p3 limit 1 offset 1)
             as c5, 
          subq_0.c8 as c6, 
          ref_0.a as c7, 
          subq_0.c7 as c8, 
          subq_0.c1 as c9, 
          pg_catalog.system_user() as c10
        from 
          public.itest1 as ref_0
            left join (select  
                  ref_1.matches as c0, 
                  ref_1.typ as c1, 
                  ref_1.colname as c2, 
                  (select slotname from public.iface limit 1 offset 44)
                     as c3, 
                  ref_1.matches as c4, 
                  ref_1.op as c5, 
                  ref_1.matches as c6, 
                  ref_1.value as c7, 
                  ref_1.op as c8, 
                  ref_1.op as c9, 
                  ref_1.typ as c10
                from 
                  public.brinopers_multi as ref_1
                where cast(null as polygon) <@ (select polygon from public.tab_core_types limit 1 offset 22)
                    ) as subq_0
            on (cast(null as macaddr8) >= cast(null as macaddr8))
        where subq_0.c10 > subq_0.c2
        limit 49) as subq_1
    ON target_0.b = subq_1.c2 
    WHEN MATCHED 
      AND (cast(null as box) |>> cast(null as box)) 
        or (cast(null as lseg) ?-| (select s from public.lseg_tbl limit 1 offset 6)
            )
       THEN DELETE
    WHEN NOT MATCHED AND (EXISTS (
          select  
              21 as c0, 
              subq_2.c0 as c1
            from 
              public.itest14 as sample_0 tablesample system (3.6) 
                inner join public.num_exp_sqrt as sample_1 tablesample bernoulli (0.3) 
                on (cast(null as "char") <= cast(null as "char")),
              lateral (select  
                    sample_1.id as c0
                  from 
                    public.a as ref_2
                  where (cast(null as lseg) <@ cast(null as line)) 
                    or ((select b3 from public.bit_defaults limit 1 offset 80)
                         <> (select b3 from public.bit_defaults limit 1 offset 4)
                        )
                  limit 158) as subq_2
            where (cast(null as name) !~ (select t from public.test_tsvector limit 1 offset 5)
                  ) 
              and ((select bool from public.tab_core_types limit 1 offset 61)
                   < (select pg_catalog.bool_or(v) from public.rtest_view1)
                  ))) 
        or (18 is NULL)
       THEN INSERT VALUES ( pg_catalog.int4um(
        cast(public.func_with_bad_set() as int4)), 13)
    WHEN MATCHED AND ((24 is not NULL) 
          or (true)) 
        or (cast(null as "timestamp") <= cast(null as timestamptz))
       THEN UPDATE  set 
        b = target_0.b


Ugh.

I got no more SQL/JSON related crashes so far.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"No hay ausente sin culpa ni presente sin disculpa" (Prov. francés)



Commits

  1. Revert "Move PartitionPruneInfo out of plan nodes into PlannedStmt"

  2. Fix setrefs.c code for adjusting partPruneInfos

  3. Fix corner-case planner failure for MERGE.