From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oscar van Baten <info@oxcro.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Bugreport: incorrect result in 16.1 in query with
 string_agg(distinct+json_build_object
In-reply-to: 
 <CAC+9yuPPRxEKBNAA=s7=uktxaOUnOnHT-gCC_kbfdzSA_eQVGw@mail.gmail.com>
References: 
 <CAC+9yuPPRxEKBNAA=s7=uktxaOUnOnHT-gCC_kbfdzSA_eQVGw@mail.gmail.com>
Comments: In-reply-to Oscar van Baten <info@oxcro.com>
	message dated "Wed, 03 Jan 2024 17:15:15 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <210175.1704301004.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 03 Jan 2024 11:56:44 -0500
Message-ID: <210176.1704301004@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/210176.1704301004%40sss.pgh.pa.us>
Precedence: bulk

Oscar van Baten <info@oxcro.com> writes:
> 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 correc=
t
> answer.
> Removing the `DISTINCT` or replacing `(SELECT a.id)` with `a.id` does so=
lve
> it.
> It looks like a reference issue.

> You can reproduce with the query below.

Hi, I failed to reproduce this.  I get the expected output

 table_value | string_agg_value |                  zzz_2                  =
 =

-------------+------------------+-----------------------------------------=
-
 val_A       | val_B            | [{"val_1" : "val_A", "val_2" : "val_A"}]
(1 row)

both in HEAD and at 16.1 release (3edc6580c).  It seems to me that
either you have some relevant non-default planner setting, or this
is an AWS-specific bug.  Could we see the output of
	EXPLAIN (VERBOSE, SETTINGS)
for this query on the troublesome system?

We do have an open report of a problem with DISTINCT aggregates,
https://www.postgresql.org/message-id/flat/18264-e363593d7e9feb7d%40postgr=
esql.org
but it's unclear whether this is the same.

			regards, tom lane