Thread

1. Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   sulfinu@gmail.com — 2023-05-19T12:46:35Z

   Hello,
   
   I'm sorry if this message brings up once more an already settled issue, but
   there's no public list of  bug reports for PostgreSQL (the release notes
   contain only *acknowledged*, *solved* bugs).
   
   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*.
   
   Here's a very simple schema to illustrate the problem:
   create table a (
     id serial2 primary key,
     name text
   );
   
   create table b (
     name text primary key,
     a int2 references a(id)
   );
   
   insert into a (name) values ('a1'), ('a2');
   insert into b (name, a) values
     ('b for a1', 1), ('another b for a1', 1);
   Notice that entity named *a2* in table a has no corresponding bs. Now run
   this query:
   select a.*, json_agg(b) from
     a
     left join b on b.a = a.id
     group by a.id;
   *The aggregation column for entity a2 has value [null] (if array_agg() was
   used, the obtained value would be {NULL}).*
   
   Since the query without aggregation
   select a.*, b.* from
     a
     left join b on b.a = a.id;
   produces *an entry* for entity *a2* with NULL values for columns belonging
   to table b, *I expect the aggregation to produce either
   [{"name":null,"a":null}] or the SQL NULL for it, preferrably the latter.*
   
   I suppose there's a dillema regarding what to return: the aggregation is
   computed over one row, but its values are actually missing. The curent
   choice of PostgreSQL is somewhere in-between, which is inappropriate from
   both perspectives - a political compromise. Moreover, in the case of
   array_agg(), the returned value could have also originated from a
   single-row single-column actual NULL value!
   
   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>. For the
   database client, it has the benefit of being both *cheap* to detect and
   *distinguishable* from other cases.
   
   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;
   
   Thoughts?
   

2. Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   sulfinu@gmail.com — 2023-05-19T13:21:21Z

   As it always happens, I've had a better idea for an workaround *after*
   sending the e-mail:
   select * from
   a,
       lateral (select json_agg(b) from
                b
                where b.a = a.id) q;
   
   
   Hello,
   >
   > I'm sorry if this message brings up once more an already settled issue,
   > but there's no public list of  bug reports for PostgreSQL (the release
   > notes contain only *acknowledged*, *solved* bugs).
   >
   > 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*.
   >
   > Here's a very simple schema to illustrate the problem:
   > create table a (
   >   id serial2 primary key,
   >   name text
   > );
   >
   > create table b (
   >   name text primary key,
   >   a int2 references a(id)
   > );
   >
   > insert into a (name) values ('a1'), ('a2');
   > insert into b (name, a) values
   >   ('b for a1', 1), ('another b for a1', 1);
   > Notice that entity named *a2* in table a has no corresponding bs. Now run
   > this query:
   > select a.*, json_agg(b) from
   >   a
   >   left join b on b.a = a.id
   >   group by a.id;
   > *The aggregation column for entity a2 has value [null] (if array_agg() was
   > used, the obtained value would be {NULL}).*
   >
   > Since the query without aggregation
   > select a.*, b.* from
   >   a
   >   left join b on b.a = a.id;
   > produces *an entry* for entity *a2* with NULL values for columns
   > belonging to table b, *I expect the aggregation to produce either
   > [{"name":null,"a":null}] or the SQL NULL for it, preferrably the latter.*
   >
   > I suppose there's a dillema regarding what to return: the aggregation is
   > computed over one row, but its values are actually missing. The curent
   > choice of PostgreSQL is somewhere in-between, which is inappropriate from
   > both perspectives - a political compromise. Moreover, in the case of
   > array_agg(), the returned value could have also originated from a
   > single-row single-column actual NULL value!
   >
   > 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>. For
   > the database client, it has the benefit of being both *cheap* to detect
   > and *distinguishable* from other cases.
   >
   > 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;
   >
   > Thoughts?
   >
   

3. Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   Tom Lane <tgl@sss.pgh.pa.us> — 2023-05-19T14:14:02Z

   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
   
   
   
   

4. Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   David G. Johnston <david.g.johnston@gmail.com> — 2023-05-19T14:36:49Z

   On Fri, May 19, 2023 at 7:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
   
   >
   > > *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.
   >
   
   The OP is correct, the result for json_agg on an outer join where the input
   is a composite column of the nullable-side of the join is a json array with
   a single json null value.  Likewise, for array_agg we produce a length one
   array with a single SQL NULL.
   
   I agree that, in at least the json_agg case, the json array that is
   produced should be an json object with keys matching the names of the
   fields of the composite.  Absent that, representing "found no rows on the
   nullable side of the join" should be represented by SQL NULL as the overall
   result.  Producing a value in the JSON array that isn't an object when the
   input is a composite is a POLA violation.
   
   That all said, it seems near impossible to change this behavior now.  But
   adding a note to the effect of: when aggregating a composite where the
   fields are all null the simple null representation form will be used in the
   resultant array instead of producing an object where all keys have null
   values. (I haven't experimented with cases where there are matching rows in
   the outer join but all the relevant columns actually end up with null
   values in them)
   
   David J.
   

5. Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   Tom Lane <tgl@sss.pgh.pa.us> — 2023-05-19T15:12:54Z

   "David G. Johnston" <david.g.johnston@gmail.com> writes:
   > I agree that, in at least the json_agg case, the json array that is
   > produced should be an json object with keys matching the names of the
   > fields of the composite.
   
   Well, it *is*, if the input is a composite value.  A bare NULL isn't
   a composite value.  Observe the difference:
   
   regression=# select to_json(null::b);
    to_json 
   ---------
    
   (1 row)
   
   regression=# select to_json(null::b) is null;
    ?column? 
   ----------
    t
   (1 row)
   
   regression=# select to_json(row(null,null)::b);
           to_json         
   ------------------------
    {"name":null,"a":null}
   (1 row)
   
   As I said, we do not treat null::b and row(null,null)::b exactly
   alike.  We could spend a long time arguing about the merits of that
   and whether or not it exactly satisfies the SQL spec, but at the end
   of the day, the odds of it changing in Postgres are epsilon.  I do
   not agree that it's wrong, and even if I did I doubt we'd take the
   compatibility hit of changing it.
   
   			regards, tom lane
   
   
   
   

6. Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   David G. Johnston <david.g.johnston@gmail.com> — 2023-05-19T15:46:49Z

   On Fri, May 19, 2023 at 8:12 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
   
   > "David G. Johnston" <david.g.johnston@gmail.com> writes:
   > > I agree that, in at least the json_agg case, the json array that is
   > > produced should be an json object with keys matching the names of the
   > > fields of the composite.
   >
   > Well, it *is*, if the input is a composite value.  A bare NULL isn't
   > a composite value.  Observe the difference:
   >
   > regression=# select to_json(null::b);
   >  to_json
   > ---------
   >
   > (1 row)
   >
   > regression=# select to_json(row(null,null)::b);
   >         to_json
   > ------------------------
   >  {"name":null,"a":null}
   > (1 row)
   >
   >
   Is there a place in our docs where the reader can learn that in the query:
   "SELECT b FROM a LEFT JOIN b":
   
   The reference to "b" in the target list, for rows where there is no match,
   is constructed semantically via null:b as opposed to (b.col1, b.col2,
   ...)::b ?
   
   David J.
   
   The following does work if the object form of the JSON is desired.
   
   select a.*, json_agg((b.name, b.a)::b) from
     a
     left join b on b.a = a.id
     group by a.id;
   
   (one cannot avoid writing out the column names here since any reference to
   plain "b" or "b.*" results in the scalar null construction of b coming into
   play)
   
   David J.
   

7. Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   sulfinu@gmail.com — 2023-05-19T16:59:30Z

   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:
   create table a (
     id serial2 primary key,
     name text
   );
   
   create table b (
     name text,
     a int2 references a(id)
   );
   
   insert into a (name) values ('a1'), ('a2'), ('a3');
   insert into b (name, a) values
     ('b for a1', 1), ('another b for a1', 1), (null, null);
   
   select a.*, json_agg(b) from
     a
     left join b on b.a = a.id or a.id = 3 and b.a is null
     group by a.id;
   select a.*, array_agg(b) from
     a
     left join b on b.a = a.id or a.id = 3 and b.a is null
     group by a.id;
   Therefore, *it is a bug*. Whether the collective handling of joined columns
   as a NULL record has some justification or there are reasons for *not*
   fixing this inconsistency, that's another matter.
   Anyway, thanks for a second workaround.
   
   
   On Fri, May 19, 2023 at 8:12 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
   >
   >> "David G. Johnston" <david.g.johnston@gmail.com> writes:
   >> > I agree that, in at least the json_agg case, the json array that is
   >> > produced should be an json object with keys matching the names of the
   >> > fields of the composite.
   >>
   >> Well, it *is*, if the input is a composite value.  A bare NULL isn't
   >> a composite value.  Observe the difference:
   >>
   >> regression=# select to_json(null::b);
   >>  to_json
   >> ---------
   >>
   >> (1 row)
   >>
   >> regression=# select to_json(row(null,null)::b);
   >>         to_json
   >> ------------------------
   >>  {"name":null,"a":null}
   >> (1 row)
   >>
   >>
   > Is there a place in our docs where the reader can learn that in the query:
   > "SELECT b FROM a LEFT JOIN b":
   >
   > The reference to "b" in the target list, for rows where there is no match,
   > is constructed semantically via null:b as opposed to (b.col1, b.col2,
   > ...)::b ?
   >
   > David J.
   >
   > The following does work if the object form of the JSON is desired.
   >
   > select a.*, json_agg((b.name, b.a)::b) from
   >   a
   >   left join b on b.a = a.id
   >   group by a.id;
   >
   > (one cannot avoid writing out the column names here since any reference to
   > plain "b" or "b.*" results in the scalar null construction of b coming into
   > play)
   >
   > David J.
   >
   >
   

8. Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   sulfinu@gmail.com — 2023-05-19T17:03:09Z

    I did spend some time putting together this bug report, so, out of minimal
   courtesy, please reply *only after* reading my message and executing those
   statements.
   Also, there is no such thing as "json_agg_strict()" in PostgreSQL *15*.
   
   
   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
   >
   

9. Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

   David G. Johnston <david.g.johnston@gmail.com> — 2023-05-19T20:14:07Z

   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.