The follwing error sometimes happened while updating partitioned table using inheritance; ERROR: attribute xxx of type record has wrong type - Mailing list pgsql-bugs

Hi.

I defined partition using inheritance and trigger,
and tried to UPDATE it but sometimes failed with following error.
This error messages was the result of PostgreSQL 13.5.

=========================
postgres(8439)@[local]:5432=# update a set ( a, b, c ) = (select wk.x::char(10), wk.x, wk.y) from b wk where wk.x = a.b
anda.a = '2017'; 
2022-01-27 13:57:38.307 JST [8439] ERROR:  attribute 1 of type record has wrong type
2022-01-27 13:57:38.307 JST [8439] DETAIL:  Table has type tid, but query expects integer.
2022-01-27 13:57:38.307 JST [8439] STATEMENT:  update a set ( a, b, c ) = (select wk.x::char(10), wk.x, wk.y) from b wk
wherewk.x = a.b and a.a = '2017'; 
ERROR:  42804: attribute 1 of type record has wrong type
DETAIL:  Table has type tid, but query expects integer.
LOCATION:  CheckVarSlotCompatibility, execExprInterp.c:1909
=========================

I confirmed this issue happened in the following version (the parameters haven't changed since initdb):
9.5.25, 9.6.24, 10.19, 11.14, 12.9, and 13.5. This issue did not happen with following reproduce SQL in 14.1.
# I understand that 9.5.x and 9.6.x are no longer supported.

* When enable_hashjoin was off, it did not happen.

[The following is DDL and DML for reproducing this issue]
=========================
-- CREATE tables with inheritance.
create table a (a char(10), b int, c int);
create table a_1() inherits (a);
create table a_2() inherits (a);
create table a_3() inherits (a);

-- CREATE partitioning trigger.
create or replace function a_func() returns trigger as $$
begin
  if    (new.a >= '2021') then insert into a_1 values(new.*);
  elsif (new.a >= '2011' and new.a < '2021') then insert into a_2 values(new.*);
  else  insert into a_3 values (new.*);
  end if;
  return null;
end;
$$ language plpgsql;
create trigger a_trigger before insert on a for each row execute procedure a_func();

-- INSERT initial data.
insert into a select i::char(10), i, i * 2 from generate_series(200, 20300) i;

-- CREATE additional data table.
create table b (x int, y int);
insert into b select i, i * 3 from generate_series(2000, 2030) i;

-- **ANALYZE**
ANALYZE;

-- SQL (ERROR happened)
update a set ( a, b, c ) = (select wk.x::char(10), wk.x, wk.y) from b wk where wk.x = a.b and a.a = '2017';
=========================

[Output of EXPLAIN]
<When this issue happened>
=========================
postgres(8439)@[local]:5432=# explain update a set ( a, b, c ) = (select wk.x::char(10), wk.x, wk.y) from b wk where
wk.x= a.b and a.a = '2017'; 
                              QUERY PLAN
----------------------------------------------------------------------
 Update on a  (cost=0.01..386.62 rows=4 width=96)
   Update on a
   Update on a_1
   Update on a_2
   Update on a_3
   ->  Hash Join  (cost=0.01..1.47 rows=1 width=96)
         Hash Cond: (wk.x = a.b)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
         ->  Hash  (cost=0.00..0.00 rows=1 width=10)
               ->  Seq Scan on a  (cost=0.00..0.00 rows=1 width=10)
                     Filter: (a = '2017'::bpchar)
         SubPlan 1 (returns $2,$3,$4)
           ->  Result  (cost=0.00..0.02 rows=1 width=52)
   ->  Nested Loop  (cost=0.00..169.55 rows=1 width=96)
         Join Filter: (a_1.b = wk.x)
         ->  Seq Scan on a_1  (cost=0.00..167.84 rows=1 width=10)
               Filter: (a = '2017'::bpchar)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
   ->  Hash Join  (cost=2.40..3.85 rows=1 width=96)
         Hash Cond: (wk.x = a_2.b)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
         ->  Hash  (cost=2.39..2.39 rows=1 width=10)
               ->  Seq Scan on a_2  (cost=0.00..2.39 rows=1 width=10)
                     Filter: (a = '2017'::bpchar)
   ->  Nested Loop  (cost=0.00..211.75 rows=1 width=96)
         Join Filter: (a_3.b = wk.x)
         ->  Seq Scan on a_3  (cost=0.00..210.04 rows=1 width=10)
               Filter: (a = '2017'::bpchar)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
(29 rows)
=========================

<When this issue did NOT happen>
=========================
postgres(8439)@[local]:5432=# set enable_hashjoin to off;
SET
postgres(8439)@[local]:5432=# explain update a set ( a, b, c ) = (select wk.x::char(10), wk.x, wk.y) from b wk where
wk.x= a.b and a.a = '2017'; 
                            QUERY PLAN
------------------------------------------------------------------
 Update on a  (cost=0.00..387.12 rows=4 width=96)
   Update on a
   Update on a_1
   Update on a_2
   Update on a_3
   ->  Nested Loop  (cost=0.00..1.72 rows=1 width=96)
         Join Filter: (a.b = wk.x)
         ->  Seq Scan on a  (cost=0.00..0.00 rows=1 width=10)
               Filter: (a = '2017'::bpchar)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
         SubPlan 1 (returns $2,$3,$4)
           ->  Result  (cost=0.00..0.02 rows=1 width=52)
   ->  Nested Loop  (cost=0.00..169.55 rows=1 width=96)
         Join Filter: (a_1.b = wk.x)
         ->  Seq Scan on a_1  (cost=0.00..167.84 rows=1 width=10)
               Filter: (a = '2017'::bpchar)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
   ->  Nested Loop  (cost=0.00..4.10 rows=1 width=96)
         Join Filter: (a_2.b = wk.x)
         ->  Seq Scan on a_2  (cost=0.00..2.39 rows=1 width=10)
               Filter: (a = '2017'::bpchar)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
   ->  Nested Loop  (cost=0.00..211.75 rows=1 width=96)
         Join Filter: (a_3.b = wk.x)
         ->  Seq Scan on a_3  (cost=0.00..210.04 rows=1 width=10)
               Filter: (a = '2017'::bpchar)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
(27 rows)
=========================

[Log of debug_print_plan when this issue happened]
Attached.

Regards,
Takeshi Ideriha