BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18264
Logged by:          Vojtěch Beneš
Email address:      vojtech.benes@centrum.cz
PostgreSQL version: 16.1
Operating system:   Ubuntu 22.04.3 LTS
Description:

One particular query outputs unexpected error instead of result data. This
behaviour is strange while change of order of columns seems to avoid issue.
Also removing DINSTINCT keyword in first column or WHERE statement in second
column also somehow avoid issue. I was able to isolate part that causes
problems and write following test case: 

SELECT
string_agg(DISTINCT 'a', ', ') agg,
sum(
  (
    SELECT sum(1)
    FROM (SELECT id FROM unnest(array[1]) id) B
    WHERE  A.id = B.id
  )
) sum
FROM (SELECT id FROM unnest(array[1]) id) A;

Both psql and pgadmin outputs this:
ERROR:  attribute 1 of type record has wrong type
SQL status: 42804
Detail: Table has type text, but query expects integer.

Behaviour was reproduced in multiple updated clean environments:
PostgreSQL 16.1 (Ubuntu 16.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
PostgreSQL 17devel (Ubuntu 17~~devel-1.pgdg22.04+~20231227.2235.g58054de) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0,
64-bit

Here query works as expected:
PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
PostgreSQL 15.5 (Ubuntu 15.5-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

While simplifiing query for test case I also managed to produce query with
sligthly different kind of behaviour. I believe it is related to case above
and slight change produces same errors:
ERROR:  attribute number 3 exceeds number of columns 2
SQL status: XX000

CREATE TEMPORARY TABLE _tmp_a ON COMMIT DROP AS
SELECT * FROM (
   values (1, false)
) as t(id, bool);

SELECT 
string_agg(distinct 'a', ', '),
sum(
    CASE 
    WHEN B.bool IS NOT FALSE AND B.id IS NOT NULL THEN 0 
    ELSE (
        SELECT sum(1)
        FROM (SELECT id FROM unnest(array[1]) id) C 
        WHERE A.id = C.id
    ) END
)
FROM (SELECT id FROM unnest(array[1]) id) A
CROSS JOIN _tmp_a B