From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: enricotrinanes@gmail.com
Subject: BUG #18272: ERROR XX000 when selecting string_agg with distinct and subquery - occur in pg16.1 and not in pg15.4
Date: Fri, 05 Jan 2024 21:45:28 +0000
Message-ID: <18272-566ab0edabb9cd13@postgresql.org> (raw)
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"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, enricotrinanes@gmail.com
Subject: Re: BUG #18272: ERROR XX000 when selecting string_agg with distinct and subquery - occur in pg16.1 and not in pg15.4
In-Reply-To: <18272-566ab0edabb9cd13@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox