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));