Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN
Date
Msg-id CAKFQuwa_S4GzcHvgJ2B24yWoZDVD_Fwa-4cwBaNi8QrUYbQybw@mail.gmail.com
Whole thread Raw
In response to Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN  (sulfinu@gmail.com)
List pgsql-bugs
On Fri, May 19, 2023 at 9:59 AM <sulfinu@gmail.com> wrote:
I've adjusted the statements so that you can clearly see that there is a difference between a missing outer-joined entity and its columns being actually set to NULL:
Therefore, it is a bug.

Yep, the output of a left join, so far as the implicit composite (row?) type produced for the nullable relation is concerned, has a form that depends on whether or not a match was found.  i.e., the system produces null::b for a non-match.  Its a reasonable way to express "no match present".  And in the presence of an aggregate checking for {NULL} versus {(,)} to differentiate the two cases is actually doable (see NULLIF())

A bug is "something that isn't working as designed" but you haven't said what design you are taking to be authoritative.  Different queries and data producing different outputs is something that usually is beneficial.

David J.

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: initdb faild to initialize full text search dictionaries
Next
From: "396934406"
Date:
Subject: ERROR: CREATE DATABASE cannot be executed within a pipeline