Re: Attribute of type record has wrong type error with MERGE ... WHEN NOT MATCHED BY SOURCE THEN DELETE

Duncan Sands <duncan(dot)sands(at)deepbluecap(dot)com> 于2025年3月10日周一 18:43写道：

> Postgresql version 17.4 (Ubuntu 17.4-1.pgdg24.10+2) on x86_64-pc-linux-gnu
>
> To reproduce, execute the statements in the attached file cr.sql. I get:
>
> duncan=> \i cr.sql
> CREATE TABLE
> CREATE TABLE
> CREATE VIEW
> CREATE TABLE
> COPY 1
> COPY 2
> COPY 1
> psql:cr.sql:42: ERROR: attribute 2 of type record has wrong type
> DETAIL: Table has type _country_or_region, but query expects record.
>
> I attribute it to the "WHEN NOT MATCHED BY SOURCE THEN DELETE" part of the
> MERGE
> as it doesn't happen if that part is left off.
>
>
Yeah, I can reproduce this on HEAD, but on 17.0, no error happened.
I searched commit history, I found that this error was related to d7d297f84.

commit d7d297f8449641bfd71750d04c302572a350052c
Author: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Date: Thu Oct 3 12:50:38 2024 +0100

Fix incorrect non-strict join recheck in MERGE WHEN NOT MATCHED BY
SOURCE.

If a MERGE command contains WHEN NOT MATCHED BY SOURCE actions, the
merge join condition is used by the executor to distinguish MATCHED
from NOT MATCHED BY SOURCE cases. However, this qual is executed using
the output from the join subplan node, which nulls the output from the
source relation in the not matched case, and so the result may be
incorrect if the join condition is "non-strict" -- for example,
something like "src.col IS NOT DISTINCT FROM tgt.col".

Reverted above commit, cr.sql succeeded.

psql (17.0)
Type "help" for help.

postgres=# \i /workspace/cr.sql
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE TABLE
COPY 1
COPY 2
COPY 1
MERGE 0

--
Thanks,
Tender Wang