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

Поиск
Список
Период
Сортировка
Искать

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

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.




FAQ