From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19823: JSON_VALUE(... RETURNING jsonb) silently returns NULL for PASSING values of NaN / Infinity; the expo
Date: Sun, 01 Nov 2026 08:06:12 +0000
Message-ID: <19823-17cb42c43b9ee661@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19823
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux (TencentOS 4.4, x86_64)
Description:
Hello,
When a PASSING variable holds a numeric NaN or Infinity, JSON_VALUE with
RETURNING jsonb silently returns NULL. With ERROR ON ERROR the swallowed
error surfaces, and it points at JSON parsing rather than at the numeric
encoding that actually failed:
SELECT JSON_VALUE(jsonb '{}', '$x'
PASSING 'NaN'::numeric AS x RETURNING jsonb) AS nan_default;
-- actual: (NULL) expected: "NaN", like every other path below
SELECT JSON_VALUE(jsonb '{}', '$x'
PASSING 'Infinity'::float8 AS x RETURNING jsonb) AS inf_default;
-- actual: (NULL)
-- adding ERROR ON ERROR exposes what was swallowed:
SELECT JSON_VALUE(jsonb '{}', '$x'
PASSING 'NaN'::numeric AS x RETURNING jsonb ERROR ON ERROR);
-- ERROR: 22P02: invalid input syntax for type json
-- DETAIL: Token "NaN" is invalid.
-- CONTEXT: JSON data, line 1: NaN
The same value survives on every other path, so only the jsonb-encoding
route of JSON_VALUE drops it:
RETURNING numeric / text / float8 -> NaN / Infinity (kept)
JSON_QUERY(..., RETURNING jsonb) same args -> NaN (kept)
JSON_TABLE(... COLUMNS (j jsonb ...)) -> NaN, jsonb_typeof =
number
to_jsonb('NaN'::numeric) -> "NaN" (kept)
Expected: the jsonb rendering of a PASSING variable should agree with
to_jsonb() / JSON_QUERY() / JSON_TABLE() (which encode NaN as the string
"NaN"); at minimum, if the value cannot be encoded, the error raised
under ERROR ON ERROR should be attributed to the numeric encoding step,
not reported as a JSON parse error.
Actual: the field silently disappears from the output (NULL), and the
error message, when forced out, misattributes the cause.
For context, this is the same family as the timestamptz PASSING rendering
difference (same jsonb-encoding route of PASSING values), but the
mechanism is distinct and the impact is silent data loss: any row whose
sensor/financial value is NaN or Infinity loses that field in the JSON
output, with no warning, and downstream consumers cannot tell "no data"
apart from "bad data".
Reproduced on:
- PostgreSQL 18.6 (REL_18_STABLE, self-built, snapshot 2026-10-26)
- PostgreSQL 20devel (snapshot of 2026-10-29)
A draft fix location would be the numeric branch of the PASSING-to-jsonb
item encoding (the caller of json_errsave_error, jsonfuncs.c:662):
encode non-finite numerics the way to_jsonb() does.
Regards,
Chunling Qin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, 303677365@qq.com
Subject: Re: BUG #19823: JSON_VALUE(... RETURNING jsonb) silently returns NULL for PASSING values of NaN / Infinity; the expo
In-Reply-To: <19823-17cb42c43b9ee661@postgresql.org>
References: <19823-17cb42c43b9ee661@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox