Re: [HACKERS] MERGE SQL Statement for PG11

Hi,

as promised in Brussels, I taught sqlsmith about MERGE and did some
testing with merge.v14.patch applied on master at 9aef173163.

So far, it found a couple of failing assertions and a suspicous error
message.  Details and Testcases against the regression database below.

regards,
Andreas

-- TRAP: FailedAssertion("!(!((((const Node*)(node))->type) == T_SubLink))", File: "clauses.c", Line: 440)
MERGE INTO public.brin_test as target_0
USING pg_catalog.pg_database as ref_0
  left join pg_catalog.pg_user_mapping as sample_0 tablesample system (2.3)
  on (pg_catalog.mul_d_interval(
        cast(pg_catalog.pi() as float8),
        cast(case when sample_0.umoptions is not NULL then (select write_lag from pg_catalog.pg_stat_replication limit 1 offset 2)
             else (select write_lag from pg_catalog.pg_stat_replication limit 1 offset 2)
             end
           as "interval")) = (select intervalcol from public.brintest limit 1 offset 2)
        )
ON target_0.a = ref_0.encoding
WHEN NOT MATCHED AND cast(null as "timestamp") < cast(null as date)
   THEN INSERT VALUES ( 62, 6)
WHEN NOT MATCHED
  AND false
   THEN DO NOTHING;

-- TRAP: FailedAssertion("!(!((((const Node*)(node))->type) == T_SubLink))", File: "prepunion.c", Line: 2246)
MERGE INTO public.onek2 as target_0
USING public.prt1 as ref_0
  inner join public.tenk1 as ref_1
  on ((select t from public.btree_tall_tbl limit 1 offset 63)
         is not NULL)
ON target_0.stringu1 = ref_1.stringu1
WHEN NOT MATCHED THEN DO NOTHING;

-- TRAP: FailedAssertion("!(!((((const Node*)(node))->type) == T_Query))", File: "var.c", Line: 248)
MERGE INTO public.clstr_tst_inh as target_0
USING pg_catalog.pg_statio_sys_tables as ref_0
  left join public.rule_and_refint_t3 as ref_1
  on (((ref_0.heap_blks_hit is not NULL)
        or (((select f1 from public.path_tbl limit 1 offset 5)
               >= (select thepath from public.shighway limit 1 offset 33)
              )
          or (cast(null as tsvector) <> cast(null as tsvector))))
      and (ref_0.toast_blks_read is not NULL))
ON target_0.d = ref_1.data
WHEN NOT MATCHED
  AND cast(null as int2) = pg_catalog.lastval()
   THEN DO NOTHING;

-- ERROR:  unrecognized node type: 114
MERGE INTO public.found_test_tbl as target_0
USING public.itest7a as ref_0
ON target_0.a = ref_0.a
WHEN NOT MATCHED
   THEN INSERT VALUES ((select a from public.rtest_t3 limit 1 offset 6));