pgsql: Fix jsonb_object_agg() and related aggs used as window functions
Fix jsonb_object_agg() and related aggs used as window functions.
When being used as a window function, these aggregates must run the
finalization step repeatedly on their accumulated state. During that,
discarding null-valued pairs isn't okay if we want to reject duplicate
keys, since per SQL spec a null-valued pair still participates in
duplicate key checking. Hence, when both those options are selected
(jsonb_object_agg_unique_strict) run uniqueifyJsonbObject on a copy of
the pairs array, keeping the original data structure unmodified.
We don't want to do that when unique_keys isn't specified, because
repeat de-duplication can be very expensive when there are many
duplicates, but fortunately we don't have to.
uniqueifyJsonbObject did not renumber the pairs it kept, so a pair
added later could lose to an older one with the same key. Renumber
them to ensure that we preserve last-one-wins behavior across
multiple finalizations. Also improve related comments.
While adding test cases to cover these mistakes, I chose to sync up
all the json[b]_object_agg tests in json.sql and jsonb.sql. This
exposed that json_object_agg() fails to de-duplicate keys in its
output, which seems like a pre-existing bug; but for this commit
let's just memorialize its behavior.
Author: shihao zhong <zhong950419@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: /messages/by-id/CAGRkXqTCfSu=aEUCvKDTGroEWOcyz3MCv4Xi8x1PZ1T1rEmG=Q@mail.gmail.com
Backpatch-through: 19
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/4118012791355f59b0d7774b681dc46683b50cb2
Modified Files
--------------
src/backend/utils/adt/jsonb.c | 24 ++++++++++++--
src/backend/utils/adt/jsonb_util.c | 24 +++++++++-----
src/test/regress/expected/json.out | 65 +++++++++++++++++++++++++++++++++++++
src/test/regress/expected/jsonb.out | 20 ++++++++++++
src/test/regress/sql/json.sql | 21 ++++++++++++
src/test/regress/sql/jsonb.sql | 11 +++++++
6 files changed, 153 insertions(+), 12 deletions(-)