Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

вс, 16 авг. 2026 г. в 17:36, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> Consider the following test case:
>
> ```sql
> CREATE TABLE t0(x text);
> INSERT INTO t0 VALUES ('{}'), (NULL);
> CREATE VIEW v0 AS
> SELECT x, json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
> FROM t0;
>
> SELECT x IS NULL AS is_null, jv FROM v0;
> -- f | 42
> -- t | 42
>
> SELECT jv FROM v0 WHERE x IS NULL;
> -- NULL
> ```
>
> The second query selects exactly the row the first query shows as `t | 42`,
> yet returns NULL for its `jv`.
>
> A further reduction shows that the result depends on the input row order.
>
> ```sql
> SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
> ('{}'), (NULL)) v(x);
> -- 42
> -- 42
> SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
> (NULL), ('{}')) v(x);
> -- NULL
> -- 42
> ```
>
> Reproduced on 20devel, and on 17.11, 18.1, 18.4, and 19beta3; on 17rc1 only
> the `ON ERROR` variants misbehave.
>
> Hi, Suyang!

Thanks for the report!

On NULL input, jsonpath is deliberately not run: there is nothing to
search, so the result is NULL (a NOT NULL domain still checks the NULL).

The "found empty" / "had an error" flags were cleared only in the step
that runs jsonpath. NULL skips that step, so the previous row's flags
remain. The next check is "empty? then DEFAULT" — and it fires for the
wrong row.

This dates to SQL/JSON itself (6185c973, March 2024): NULL skips path
evaluation, the reset lived inside that evaluation. Later (dd8bea88abf)
the extra steps were omitted for the default "just return NULL", so the
bug stayed hidden. A non-NULL DEFAULT (42, TRUE ON ERROR) keeps those
steps — the bug shows. On 17rc1 the reporter mostly saw ON ERROR: ON
EMPTY DEFAULT did not always go through the same path yet.

Proposal fix
-----------
Clear "empty/error" at the start of each row, before deciding not to
run the path on NULL. NULL still does not run the path. The only change
is that another row's DEFAULT no longer sticks.

CC Amit Langote (JSON).