BUG #18272: ERROR XX000 when selecting string_agg with distinct and subquery - occur in pg16.1 and not in pg15.4 - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18272
Logged by:          Enrico Trinanes
Email address:      enricotrinanes@gmail.com
PostgreSQL version: 16.1
Operating system:   aarch64-unknown-linux-gnu Red Hat 7.3.1-6 - 64-bit
Description:

---------------------------------------------------------------------------------------------------------------
-- TABLES AND SCENARION CREATION

--DROP TABLE error_test_users;
--DROP TABLE error_test_roles;
--DROP TABLE error_test_role_user;

-- CREATE TABLE USED AS FROM IN THE ERROR SELECT
CREATE TABLE error_test_users (
    id int,
    name varchar(255),
    email varchar(500)
);
INSERT INTO public.error_test_users(id, name, email)
    VALUES 
        (1, 'John', 'john@example.com'),
        (2, 'Mary', 'john@example.com'),
        (3, 'Adam', 'adam@example.com'),
        (4, 'Eve', 'eve@example.com')
;

-- CREATE TABLE USED AS STRIG_AGG ACCESS
CREATE TABLE error_test_roles (
    id int,
    name varchar(255)
);
INSERT INTO public.error_test_roles(id, name)
    VALUES 
        (1, 'Analist'),
        (2, 'Manager'),
        (3, 'Director'),
        (4, 'Administrator')
;

-- CREATE TABLE THAT WOULD BE USED AS PIVOT FOR JOINING THE AGG TABLE AND
SEPARATE IT WITH
-- relation_type SO WE CAN HAVE MULTIPLE COLUMNS TRYING TO GET DIFFERENT
GROUPS
CREATE TABLE error_test_role_user (
    user_id int,
    role_id int,
    relation_type smallint
);
INSERT INTO public.error_test_role_user(user_id, role_id, relation_type)
    VALUES 
        (1, 1, 1), (1, 2, 2), (1, 3, 1),
        (2, 1, 2), (2, 3, 2),
        (3, 1, 1), (3, 2, 1), (3, 3, 2), (3, 4, 2),
        (4, 4, 1)
;

-- CHECK CREATION
select * from error_test_users;
select * from error_test_roles;
select * from error_test_role_user;


---------------------------------------------------------------------------------------------------------------
-- SELECT THAT GIVES ERROR. RELATED TO STRING_AGG(DISTINCT (SELECT))
-- this kind of select can be done by other means, but in this case,
-- i am using it only as a simplified version that gives the same
-- error of what was my scenario, so you can analyse it without many
unecessary joins
select
    error_test_users.id,
    error_test_users.name,
    error_test_users.email,
    MAX(relation_type_1.role_id) as max_id_relation_type_1,
    MAX(relation_type_2.role_id) as max_id_relation_type_2,
    string_agg(distinct
        (select name from error_test_roles where error_test_roles.id =
relation_type_1.role_id)
    , ', ') as roles_relation_type_1,
    string_agg(distinct
        (select name from error_test_roles where error_test_roles.id =
relation_type_2.role_id)
    , ', ') as roles_relation_type_2
from error_test_users
left join error_test_role_user as relation_type_1 on relation_type_1.user_id
= error_test_users.id
    and relation_type_1.relation_type = 1
left join error_test_role_user as relation_type_2 on relation_type_2.user_id
= error_test_users.id
    and relation_type_2.relation_type = 2
group by 1,2,3

---------------------------------------------------------------------------------------------------------------
-- SELECT RESULTS IN DIFFERENT ENVIRONMENTS

-- PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-6), 64-bit
-- RETURN
-- ERROR:  attribute number 5 exceeds number of columns 2
-- Estado SQL:XX000


-- PostgreSQL 15.4 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-6), 64-bit
-- RETURN
--
"id"    "name"    "email"                "max_id_relation_type_1"    "max_id_relation_type_2"
"roles_relation_type_1"   "roles_relation_type_2"
 
-- 1    "John"    "john@example.com"    3                            2                            "Analist,
Director"        "Manager"
-- 2    "Mary"    "john@example.com"                                3
"Analist, Director"
 
-- 3    "Adam"    "adam@example.com"    2                            4                            "Analist,
Manager"        "Administrator, Director"
-- 4    "Eve"    "eve@example.com"    4                                                        "Administrator"