Re: [HACKERS] MERGE SQL Statement for PG11

On 3 February 2018 at 19:57, Andreas Seltenreich <seltenreich(at)gmx(dot)de> wrote:

> 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.

Brilliant work, thank you.

It will likely take some time to work through these and the current
work items but will fix.

Do you have the DDL so we can recreate these easily?

Thanks

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

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services