From: Tom Lane <tgl@sss.pgh.pa.us>
To: sulfinu@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Aggregation results with json(b)_agg and array_agg in a SELECT
 with OUTER JOIN
In-reply-to: 
 <CAGH1kmzre_Nr+mnErVLbAgGpOAmho_VQwY+uMzmVqEzaZKkPEA@mail.gmail.com>
References: 
 <CAGH1kmzre_Nr+mnErVLbAgGpOAmho_VQwY+uMzmVqEzaZKkPEA@mail.gmail.com>
Comments: In-reply-to sulfinu@gmail.com
	message dated "Fri, 19 May 2023 15:46:35 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <35906.1684505642.1@sss.pgh.pa.us>
Date: Fri, 19 May 2023 10:14:02 -0400
Message-ID: <35907.1684505642@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/35907.1684505642%40sss.pgh.pa.us>
Precedence: bulk

sulfinu@gmail.com writes:
> This problem was noticed in version *15.1*, as I don't have the latest
> version at hand, but I've read the release notes for *15.2* and *15.3* and
> there's no sign of any related change. It's about the outcome of a SELECT
> statement which involves computing aggregate functions like json_agg,
> jsonb_agg and array_agg over columns that originate from outer-joined
> entities, when these entities are *missing*.

I see no bug here.  json_agg is defined as aggregating results equivalent
to what to_json() would produce, and what to_json() would produce for
NULL input is a NULL.

> *I expect the aggregation to produce either
> [{"name":null,"a":null}] or the SQL NULL for it, preferrably the latter.*

AFAICS, it *does* produce the latter, so you are not making yourself
very clear here.

Note that Postgres does draw a distinction between a NULL of composite
type and a value of composite type whose fields all happen to be NULL.
The SQL spec is a bit squishy on this, in that they require a
"composite_value IS NULL" test to return TRUE for both cases, but
careful parsing of the spec seems to indicate that they aren't saying
there is no distinction.

> Returning a simple SQL NULL is the most appropriate choice, in line with the
> general rule that aggregations over zero rows return NULL
> <https://www.postgresql.org/docs/current/functions-aggregate.html>.

Um, you did *not* aggregate over zero rows: the FROM/GROUP BY construct
definitely produced a row for a.id = 2.

> Although there is a workaround for this problem, it implies checking a
> stupid condition for absolutely every row in the JOIN result:
> select a.*, json_agg(b) filter (where b.name is not null) from
>   a
>   left join b on b.a = a.id
>   group by a.id;

json_agg_strict() is easier.

			regards, tom lane