Bugreport: incorrect result in 16.1 in query with string_agg(distinct+json_build_object
| От | Oscar van Baten |
|---|---|
| Тема | Bugreport: incorrect result in 16.1 in query with string_agg(distinct+json_build_object |
| Дата | |
| Msg-id | CAC+9yuPPRxEKBNAA=s7=uktxaOUnOnHT-gCC_kbfdzSA_eQVGw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Bugreport: incorrect result in 16.1 in query with string_agg(distinct+json_build_object
|
| Список | 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?
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?
В списке pgsql-bugs по дате отправления: