Bugreport: incorrect result in 16.1 in query with string_agg(distinct+json_build_object

From: Oscar van Baten <info(at)oxcro(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Bugreport: incorrect result in 16.1 in query with string_agg(distinct+json_build_object
Date: 2024-01-03 16:15:15
Message-ID: CAC+9yuPPRxEKBNAA=s7=uktxaOUnOnHT-gCC_kbfdzSA_eQVGw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

/*
Hi,

We've upgraded one of our instances from 14.10 to 16.1 at AWS
All fine, except for a certain output which became different.
At another instance running at 14.7 we are able to reproduce this correct
answer.
Removing the `DISTINCT` or replacing `(SELECT a.id)` with `a.id` does solve
it.
It looks like a reference issue.

You can reproduce with the query below.

PS: I did scan https://wiki.postgresql.org/wiki/Todo but was not able to
find a similar issue.
It does look like this one:
https://www.postgresql.org/message-id/3536307.1703952795%40sss.pgh.pa.us
But not sure if it's the same issue
*/

DROP TABLE IF EXISTS pg_temp.alldata;
create temp table pg_temp.alldata AS
(
SELECT 'val_A' AS id
);

SELECT
a.id as table_value,
STRING_AGG( 'val_B', ', ') AS string_agg_value,
JSON_AGG
(
JSON_BUILD_OBJECT
(
'val_1', a.id,
'val_2', (SELECT a.id)
)
) AS zzz_2
FROM pg_temp.alldata a
GROUP BY id;

at:
PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-12), 64-bit
output:
table_value|string_agg_value|zzz_2 |
-----------+----------------+----------------------------------------+
val_A |val_B |[{"val_1" : "val_A", "val_2" : "val_A"}]|

at:
PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-12), 64-bit
output:
table_value|string_agg_value|zzz_2 |
-----------+----------------+----------------------------------------+
val_A |val_B |[{"val_1" : "val_A", "val_2" : "val_B"}]| --
why is val_B suddenly here?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2024-01-03 16:55:09 BUG #18268: Failed to validate GPG signature for pgdg-redhat-repo-42.0-38PGDG.noarch
Previous Message PG Bug reporting form 2024-01-03 12:06:40 BUG #18267: Logical replication bug: data is not synchronized after Alter Publication.