From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19813: jsonpath: datetime errors bypass silent/ON ERROR; boolean() inconsistent; PASSING rejects bpchar/nam
Date: Thu, 29 Oct 2026 06:51:13 +0000
Message-ID: <19813-6a460679ae295ed6@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19813
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:
Three issues in the jsonpath / SQL:JSON error-handling family. All reproduce
on the official postgres:latest docker image (18.6).
1. .datetime() template errors bypass silent mode and ON ERROR
SELECT jsonb_path_query(jsonb '"abc"', '$.datetime("XYZ")', '{}'::jsonb,
true);
-- ERROR: invalid datetime format separator: "X" (expected: no rows)
SELECT JSON_VALUE(jsonb '"abc"', '$.datetime("XYZ")' RETURNING text NULL
ON ERROR);
-- ERROR: invalid datetime format separator: "X" (expected: NULL)
SELECT JSON_VALUE(jsonb '"abc"', '$.datetime("XYZ")' RETURNING text
DEFAULT 'dflt' ON ERROR);
-- ERROR (expected: 'dflt')
Controls:
- A valid template with a non-matching input correctly returns no rows under
silent:
jsonb_path_query(jsonb '"abc"', '$.datetime("YYYY-MM-DD")',
'{}'::jsonb, true) -> (no rows)
- .decimal() was given exactly this treatment by commit 84001a0 ("Fix
jsonpath .decimal() to honor silent mode"):
jsonb_path_query(jsonb '1.234', '$.decimal(10,2000)', '{}'::jsonb,
true) -> (no rows)
.datetime() appears to have the same class of execution error without the
same handling.
- Without silent, raising the error is correct.
- $.integer() on a non-matching value under silent correctly returns no
rows, so the mechanism works for other methods.
2. .boolean() is inconsistent for numerically equal values
SELECT jsonb_path_query(jsonb '0', '$.boolean()'); -> false
SELECT jsonb_path_query(jsonb '0.0', '$.boolean()'); -> ERROR:
argument "0.0" of jsonpath item method .boolean() is invalid for type
boolean
SELECT jsonb_path_query(jsonb '1e2', '$.boolean()'); -> true
SELECT jsonb_path_query(jsonb '100.0', '$.boolean()'); -> ERROR
SELECT jsonb_path_query(jsonb '2147483648', '$.boolean()'); -> ERROR
(outside int4 range)
SELECT jsonb '0' = jsonb '0.0'; -> true (the two inputs are the
same JSON number)
SELECT jsonb '1e2' = jsonb '100.0'; -> true
The documentation says .boolean() converts "a JSON boolean, number, or
string". The sibling methods accept these inputs:
.integer()/.bigint()/.number() on 1.5 return 2/2/1.5, while .boolean()
errors; under silent, .boolean() yields no rows where .integer() yields a
value. The implementation appears to route the number through int4 parsing,
so any spelling of a number that is not a plain int4 literal fails.
3. JSON_* PASSING rejects bpchar/name values, bypassing ON ERROR
CREATE TABLE tchar (a char(3), b name, c text);
INSERT INTO tchar VALUES ('abc','abc','abc');
SELECT JSON_VALUE(jsonb 'null', '$v' PASSING c AS v) FROM tchar; ->
'abc' (text works)
SELECT JSON_VALUE(jsonb 'null', '$v' PASSING a AS v RETURNING text NULL
ON ERROR) FROM tchar;
-- ERROR: could not convert value of type character to jsonpath
SELECT JSON_VALUE(jsonb 'null', '$v' PASSING b AS v RETURNING text NULL
ON ERROR) FROM tchar;
-- ERROR: could not convert value of type name to jsonpath
Two problems: a char(N) column or a name column (the type that
information_schema columns use) cannot be passed to a jsonpath variable at
all, and the conversion failure is raised as a hard error that ignores the
ON ERROR clause of the enclosing SQL/JSON expression.
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 #19813: jsonpath: datetime errors bypass silent/ON ERROR; boolean() inconsistent; PASSING rejects bpchar/nam
In-Reply-To: <19813-6a460679ae295ed6@postgresql.org>
References: <19813-6a460679ae295ed6@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