Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN
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?
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,
Show quoted text
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?
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
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.
"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
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.
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.
Show quoted text
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.
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:
Show quoted text
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
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.