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?
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. |