BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19736
Logged by: Shallow
Email address: theshallow27@gmail.com
PostgreSQL version: 18.6
Operating system: Linux
Description:
When `ABSENT ON NULL` removes a pair because its value is SQL NULL,
`JSON_OBJECT` also skips validation of that pair's key. The documented
restrictions say that a key cannot be NULL and must be a scalar expression
without a cast to JSON. Those restrictions should still apply when the value
pair will be omitted.
**Reproduction:**
```sql
SELECT JSON_OBJECT((NULL::text) VALUE NULL::text
ABSENT ON NULL RETURNING json);
SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text
ABSENT ON NULL RETURNING json);
```
**Actual result:** Both statements return `{}`.
**Expected result:** Both statements should reject the invalid key, as the
key
restrictions apply regardless of whether the value is omitted.
Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
Hi,
I reproduced both examples on master at 9e7d152488f1.
In json_build_object_worker(), the ABSENT ON NULL skip happens before
NULL-key validation. Both json and jsonb paths also skip the scalar-key
checks when omitting an entry without a uniqueness check.
The attached patch moves the json NULL-key check before the skip and
validates the key type before omitting an entry in either output path.
It avoids calling output or cast functions for omitted keys and preserves
the existing uniqueness checks.
Regression tests cover NULL, array, composite, json and jsonb keys with
NULL values. The new tests fail without the fix; with the patch, make
check passes all 239 tests on macOS/arm64 with assertions enabled.
Comments are welcome.
Thanks,
Yeongjae Min
On Fri, 02 Oct 2026 22:35:57 +0000, PG Bug reporting form
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19736
> Logged by: Shallow
> Email address: theshallow27@gmail.com
> PostgreSQL version: 18.6
> Operating system: Linux
> Description:
>
> When `ABSENT ON NULL` removes a pair because its value is SQL NULL,
> `JSON_OBJECT` also skips validation of that pair's key. The documented
> restrictions say that a key cannot be NULL and must be a scalar expression
> without a cast to JSON. Those restrictions should still apply when the value
> pair will be omitted.
>
> **Reproduction:**
>
> ```sql
> SELECT JSON_OBJECT((NULL::text) VALUE NULL::text
> ABSENT ON NULL RETURNING json);
>
> SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text
> ABSENT ON NULL RETURNING json);
> ```
>
> **Actual result:** Both statements return `{}`.
>
> **Expected result:** Both statements should reject the invalid key, as the
> key
> restrictions apply regardless of whether the value is omitted.
Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
От:
QQ Mail <781408517@qq.com>
Дата:
Hi, Your patch covers the non-scalar-key cases as well as the NULL-key case addressed by my earlier patch. Please consider your patch in place of mine. With AI assistance, I reviewed and tested your attached v1 patch against 9e7d152488f197e96f171f0714459c227c3f7693 on Linux x86_64 with GCC 14.2.0 and assertions enabled. All 239 core regression tests passed via make installcheck. An additional 260-case matrix passed, covering both output types, NULL and non-NULL values, uniqueness checks, scalar keys, arrays, composites, json/jsonb keys, domains, and a user-defined cast to json. Comparing against the unpatched build, 19 previously accepted invalid-key cases were rejected; the other 241 cases retained their results and error behavior. I also checked omitted keys using a custom type whose output function raises an error. The output function remained uncalled without uniqueness checking, while the existing conversion behavior with WITH UNIQUE KEYS was preserved. Volatile key/value expressions and value-expression errors retained their observed evaluation behavior. I did not find a correctness issue in these checks.
Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
От:
민영재 <dudwo990413@gmail.com>
Дата:
Hi,
Thanks for reviewing the patch and for the additional testing,
especially the Linux/x86_64 coverage and evaluation behavior checks.
Regards,
Yeongjae Min
Thanks for reviewing the patch and for the additional testing,
especially the Linux/x86_64 coverage and evaluation behavior checks.
Regards,
Yeongjae Min
Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
От:
Dmitry Dolgov <9erthalion6@gmail.com>
Дата:
> On Sun, Oct 11, 2026 at 07:26:02PM +0900, 민영재 wrote:
> I reproduced both examples on master at 9e7d152488f1.
>
> In json_build_object_worker(), the ABSENT ON NULL skip happens before
> NULL-key validation. Both json and jsonb paths also skip the scalar-key
> checks when omitting an entry without a uniqueness check.
>
> The attached patch moves the json NULL-key check before the skip and
> validates the key type before omitting an entry in either output path.
> It avoids calling output or cast functions for omitted keys and preserves
> the existing uniqueness checks.
>
> Regression tests cover NULL, array, composite, json and jsonb keys with
> NULL values. The new tests fail without the fix; with the patch, make
> check passes all 239 tests on macOS/arm64 with assertions enabled.
Thanks for investigating. I think it's worth to clarify the
documentation a bit -- one can argue that we don't need to verify keys
if the pair is going to be skipped, and only the wording happen to
insist on it. Currently it says:
key_expression is a scalar expression
defining the JSON key, which is
converted to the text type.
It cannot be NULL nor can it
belong to a type that has a cast to the json type.
Maybe extending it to "It cannot be ..., even if the pair is going to be
eventually omitted due to ABSENT ON NULL".
It also probably makes sense to verify if there are more such examples.
E.g. looks like jsonb_object_agg_strict exhibit something similar:
-- jsonb_object_agg_transfn_worker tests if the key is NULL
select jsonb_object_agg_strict(NULL, NULL);
ERROR: 22023: field name must not be null
-- it also checks if the key is not a composite or json, if the
-- value is not NULL
select jsonb_object_agg_strict('{"a": 1}'::jsonb, 'value');
ERROR: 22023: key value must be scalar, not array, composite, or json
-- but it doesn't do so if the value is NULL
select jsonb_object_agg_strict('{"a": 1}'::jsonb, NULL);
jsonb_object_agg_strict
-------------------------
{}
(1 row)
Regarding the proposed patch, maybe the condition on tcategory could be
factored out to not repeat it few times (including one in the
datum_to_jsonb_internal). It's also not clear to me why JSONTYPE_JSONB
is not included in json_build_object_worker, shouldn't it be included
since it has a cast to json?
Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
Hi Dmitry,
Thanks for the suggestions. V2 is attached.
I found the same issue in json_object_agg_strict() and
jsonb_object_agg_strict() and extended the fix to those workers,
also covering JSON_OBJECTAGG with ABSENT ON NULL.
V2 shares the category checks through json_validate_key_category()
and clarifies that key restrictions apply even to omitted pairs.
For JSONTYPE_JSONB, json_categorize_type(..., false, ...) maps jsonb
to JSONTYPE_JSON, so v1 already covered it in the json path.
The shared helper explicitly handles both categories.
All 239 core regression tests pass on macOS/arm64 with assertions
enabled, on master at 7787aec8ab00, including the added aggregate tests.
I also verified that omitted keys still avoid output conversion without
uniqueness checking, while WITH UNIQUE KEYS retains its behavior.
Thanks,
Yeongjae Min
On Fri, 16 Oct 2026 22:41:27 +0200, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
> > On Sun, Oct 11, 2026 at 07:26:02PM +0900, 민영재 wrote:
> > I reproduced both examples on master at 9e7d152488f1.
> >
> > In json_build_object_worker(), the ABSENT ON NULL skip happens before
> > NULL-key validation. Both json and jsonb paths also skip the scalar-key
> > checks when omitting an entry without a uniqueness check.
> >
> > The attached patch moves the json NULL-key check before the skip and
> > validates the key type before omitting an entry in either output path.
> > It avoids calling output or cast functions for omitted keys and preserves
> > the existing uniqueness checks.
> >
> > Regression tests cover NULL, array, composite, json and jsonb keys with
> > NULL values. The new tests fail without the fix; with the patch, make
> > check passes all 239 tests on macOS/arm64 with assertions enabled.
>
> Thanks for investigating. I think it's worth to clarify the
> documentation a bit -- one can argue that we don't need to verify keys
> if the pair is going to be skipped, and only the wording happen to
> insist on it. Currently it says:
>
> key_expression is a scalar expression
> defining the JSON key, which is
> converted to the text type.
> It cannot be NULL nor can it
> belong to a type that has a cast to the json type.
>
> Maybe extending it to "It cannot be ..., even if the pair is going to be
> eventually omitted due to ABSENT ON NULL".
>
> It also probably makes sense to verify if there are more such examples.
> E.g. looks like jsonb_object_agg_strict exhibit something similar:
>
> -- jsonb_object_agg_transfn_worker tests if the key is NULL
> select jsonb_object_agg_strict(NULL, NULL);
> ERROR: 22023: field name must not be null
>
> -- it also checks if the key is not a composite or json, if the
> -- value is not NULL
> select jsonb_object_agg_strict('{"a": 1}'::jsonb, 'value');
> ERROR: 22023: key value must be scalar, not array, composite, or json
>
> -- but it doesn't do so if the value is NULL
> select jsonb_object_agg_strict('{"a": 1}'::jsonb, NULL);
> jsonb_object_agg_strict
> -------------------------
> {}
> (1 row)
>
> Regarding the proposed patch, maybe the condition on tcategory could be
> factored out to not repeat it few times (including one in the
> datum_to_jsonb_internal). It's also not clear to me why JSONTYPE_JSONB
> is not included in json_build_object_worker, shouldn't it be included
> since it has a cast to json?
Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
Hi,
CFbot reported that v2 needs a rebase after b58a9a97fe reverted the json_check_mutability() changes. V3 is rebased onto master at 553262aff40d. It updates the insertion context for the shared key validation helper without restoring the reverted function; there are no behavior changes from v2.
I also verified that the attached patch applies with git am to a clean checkout of the stated master commit.
Thanks,
Yeongjae Min
On Sat, 17 Oct 2026 23:53:49 +0900, 민영재 <dudwo990413@gmail.com> wrote:
Hi Dmitry, Thanks for the suggestions. V2 is attached. I found the same issue in json_object_agg_strict() and jsonb_object_agg_strict() and extended the fix to those workers, also covering JSON_OBJECTAGG with ABSENT ON NULL. V2 shares the category checks through json_validate_key_category() and clarifies that key restrictions apply even to omitted pairs. For JSONTYPE_JSONB, json_categorize_type(..., false, ...) maps jsonb to JSONTYPE_JSON, so v1 already covered it in the json path. The shared helper explicitly handles both categories. All 239 core regression tests pass on macOS/arm64 with assertions enabled, on master at 7787aec8ab00, including the added aggregate tests. I also verified that omitted keys still avoid output conversion without uniqueness checking, while WITH UNIQUE KEYS retains its behavior. Thanks, Yeongjae Min On Fri, 16 Oct 2026 22:41:27 +0200, Dmitry Dolgov <9erthalion6@gmail.com> wrote: > > On Sun, Oct 11, 2026 at 07:26:02PM +0900, 민영재 wrote: > > I reproduced both examples on master at 9e7d152488f1. > > > > In json_build_object_worker(), the ABSENT ON NULL skip happens before > > NULL-key validation. Both json and jsonb paths also skip the scalar-key > > checks when omitting an entry without a uniqueness check. > > > > The attached patch moves the json NULL-key check before the skip and > > validates the key type before omitting an entry in either output path. > > It avoids calling output or cast functions for omitted keys and preserves > > the existing uniqueness checks. > > > > Regression tests cover NULL, array, composite, json and jsonb keys with > > NULL values. The new tests fail without the fix; with the patch, make > > check passes all 239 tests on macOS/arm64 with assertions enabled. > > Thanks for investigating. I think it's worth to clarify the > documentation a bit -- one can argue that we don't need to verify keys > if the pair is going to be skipped, and only the wording happen to > insist on it. Currently it says: > > <replaceable>key_expression</replaceable> is a scalar expression > defining the <acronym>JSON</acronym> key, which is > converted to the <type>text</type> type. > It cannot be <literal>NULL</literal> nor can it > belong to a type that has a cast to the <type>json</type> type. > > Maybe extending it to "It cannot be ..., even if the pair is going to be > eventually omitted due to ABSENT ON NULL". > > It also probably makes sense to verify if there are more such examples. > E.g. looks like jsonb_object_agg_strict exhibit something similar: > > -- jsonb_object_agg_transfn_worker tests if the key is NULL > select jsonb_object_agg_strict(NULL, NULL); > ERROR: 22023: field name must not be null > > -- it also checks if the key is not a composite or json, if the > -- value is not NULL > select jsonb_object_agg_strict('{"a": 1}'::jsonb, 'value'); > ERROR: 22023: key value must be scalar, not array, composite, or json > > -- but it doesn't do so if the value is NULL > select jsonb_object_agg_strict('{"a": 1}'::jsonb, NULL); > jsonb_object_agg_strict > ------------------------- > {} > (1 row) > > Regarding the proposed patch, maybe the condition on tcategory could be > factored out to not repeat it few times (including one in the > datum_to_jsonb_internal). It's also not clear to me why JSONTYPE_JSONB > is not included in json_build_object_worker, shouldn't it be included > since it has a cast to json?