From: Andreas Seltenreich <seltenreich@gmx.de>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
  Chapman Flack <chap@anastigmatix.net>,
  PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: [HACKERS] MERGE SQL Statement for PG11
In-Reply-To: 
 <CANP8+j+YDPtO9KsMX2ztXLxHjpmjef3ytfmhRfvK48db0o4btg@mail.gmail.com>
	(Simon Riggs's message of "Tue, 30 Jan 2018 09:16:41 +0000")
References: 
 <CANP8+jLdJoqjzkEdfqy_Rw43gUk08btunpJVV7Yiw=UqtOtePw@mail.gmail.com>
	<CANP8+jJgS_KoN6DDteb_9-fUzfnp7Ytm9O5VE83dGpWp1pszkg@mail.gmail.com>
	<CAH2-WznpEqdzL_sgWOHfmb9jz6612JLkhdN5YcdmdW3qX3g4Uw@mail.gmail.com>
	<CANP8+jKTuZ5NDCLkqXBm9kMeaT-jNxxhkMg+FOpLni=pwOQ=BA@mail.gmail.com>
	<CANP8+jKJee2Luip7tv4KrK8R_EP5PbOvDD+vfUpp9uBFmfK9yg@mail.gmail.com>
	<CANP8+jK8YRSCrg8m8XiRMmoy9DfvL=hUQ1SpS=+y=920evbTvg@mail.gmail.com>
	<CAFj8pRCUbOsyD5ny1UvoZciEbk3Q1YJj+gjEta=1X8dGb1cjSA@mail.gmail.com>
	<CANP8+jL86yJU0cx09DCWZVT=ZAo4JK1oqS_0s0t=WUN-FP9iaQ@mail.gmail.com>
	<CAFj8pRApqwbS9ZBf38=vg38rU90dotukx22xxV+Oe8fiCv=3Kg@mail.gmail.com>
	<CANP8+jKoE=AXPz+gMdxXgod7UO_C8OGwJ6aD+A4b5nfxLOzQrA@mail.gmail.com>
	<20180129154457.GC11613@momjian.us>
	<CANP8+jLDtMxYXJ95ffA3PYqAVfTkh3V42W9TYWAJH6PsAgOocg@mail.gmail.com>
	<495e24a5-d5bf-3ef2-ccdd-dd112c1c3a15@anastigmatix.net>
	<CANP8+j+icDb4=+zVUiRN60TAf9s9WT+6dOoWQqDu+B90xATRHA@mail.gmail.com>
	<CAFj8pRA7=C9yPJW_iG2Es-qBkZt-hSUFmF+cqZGoHU5SAPj_kw@mail.gmail.com>
	<CANP8+j+YDPtO9KsMX2ztXLxHjpmjef3ytfmhRfvK48db0o4btg@mail.gmail.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.0.50 (gnu/linux)
Date: Sat, 03 Feb 2018 20:57:24 +0100
Message-ID: <87k1vtdexn.fsf@ansel.ydns.eu>
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

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