From: Tom Lane <tgl@sss.pgh.pa.us>
To: maxim.boguk@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17084: Wrong results of distinct node.
In-reply-to: <17084-9f83b67ab464cc69@postgresql.org>
References: <17084-9f83b67ab464cc69@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 06 Jul 2021 14:34:35 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1679022.1625585130.1@sss.pgh.pa.us>
Date: Tue, 06 Jul 2021 11:25:30 -0400
Message-ID: <1679023.1625585130@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1679023.1625585130%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> During investigation of weird app behaviour I found very strange results
> with sorting/dising of some dataset.

I am not sure there is a PG bug here.  Your query is doing DISTINCT
on (among other things)

>             array_agg(geo_id) AS geo_ids

which is going to have results that vary depending on the unspecified
order in which the first-level aggregation is done.  So you might
by luck have more or fewer distinct array values with different
plans.

If you still get unstable results after changing that to, say,

              array_agg(geo_id order by geo_id) AS geo_ids

then I'd agree we need to investigate.

			regards, tom lane