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

On Mon, 10 Mar 2025 at 13:46, Tender Wang <tndrwang@gmail.com> wrote:
>
> When the query has NOT MATCHED BY SOURCE, commit d7d297f84 add "src IS NOT NULL" join condition.
> In this case, the src is view(e.g. subquery), so in makeWholeRowVar(), it will call below code:
> result = makeVar(varno,
>                       InvalidAttrNumber,
>                       RECORDOID,
>                       -1,
>                       InvalidOid,
>                       varlevelsup);
>
> the vartype is RECORDOID, but te reltype of src is not RECORDOID, so $SUBJECT error reports.
>
> I add the below codes to makeWholeRowVar() default branch:
>
> if (rte->relkind == RELKIND_VIEW)
>         toid = get_rel_type_id(rte->relid);
> else
>         toid = RECORDOID;
>
> It can work.
>

Yes, I reached the same conclusion.

When the parser processes the "AND qq_src IS DISTINCT FROM qq_tgt"
clause, it creates a whole-row Var for qq_src whose type is the view
type. Then transform_MERGE_to_join() adds another whole-row Var for
qq_src, but by this time the RTE has been expanded into a subquery
RTE, so its type becomes RECORDOID. The executor then grumbles because
it has 2 Vars with the same varno and varattno, but different
vartypes.

Fixing that by having makeWholeRowVar() set the type based on
rte->relid for subquery RTEs that used to be views seems like a good
fix. However, it looks like that fix will only work as far back as v16
(where 47bb9db7599 and 0f8cfaf8921 were added).

Unfortunately, it looks like this bug pre-dates MERGE WHEN NOT MATCHED
BY SOURCE, and even MERGE itself. All that's needed to trigger it is a
query that causes 2 whole-row Vars to be added, one before and one
after view expansion. That can be made to happen via the rowmarking
mechanism in all supported branches as follows:

create table foo (a int, b int);
create view foo_v as select * from foo offset 0;
insert into foo values (1,2);
update foo set b = foo_v.b from foo_v where foo_v.a = foo.a returning foo_v;

which fails in the same way, with

ERROR:  attribute 3 of type record has wrong type
DETAIL:  Table has type record, but query expects foo_v.

Reading the commit message for 47bb9db7599 suggests that maybe it
would be OK to further back-patch the changes to ApplyRetrieveRule()
to retain relkind and relid on subquery RTEs for this purpose. That
wouldn't affect stored rules, but I haven't looked to see what else it
might affect.

Thoughts?

Regards,
Dean