BUG #19813: jsonpath: datetime errors bypass silent/ON ERROR; boolean() inconsistent; PASSING rejects bpchar/nam

The following bug has been logged on the website:

Bug reference: 19813
Logged by: chunling qin
Email address: 303677365(at)qq(dot)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.