Feedback-ID: ia2694551:Fastmail
Date: Tue, 28 Mar 2023 13:22:48 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: "variable not found in subplan target list"
Message-ID: <20230328112248.6as34mlx5sr4kltg@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/20230328112248.6as34mlx5sr4kltg%40alvherre.pgsql>
Precedence: bulk

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)