pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.








Message-ID: <19813-6a460679ae295ed6@postgresql.org>
Permalink:  ../19813-6a460679ae295ed6@postgresql.org/
Also on:    postgresql.org/message-id/19813-6a460679ae295ed6@postgresql.org

 · 

reply

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