Re: BUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error

пт, 4 сент. 2026 г. в 14:53, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 19654
> Logged by: Ce Lyu
> Email address: 2530254482(at)qq(dot)com
> PostgreSQL version: 18.6
> Operating system: Linux, official Docker image postgres:18.6, Debian
> Description:
>
> JSON_EXISTS / JSON_VALUE / JSON_QUERY should return SQL NULL when the
> input document is SQL NULL, independent of which rows were evaluated
> earlier in the same query. After a previous row of the same compiled
> expression has taken the ON ERROR path, a later SQL NULL input incorrectly
> returns the ON ERROR replacement value instead of NULL. Scan order
> therefore
> changes the result.
>
> This is on the latest minor of the current major version.
>
> PostgreSQL version:
> PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu,
> compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
>
> Also reproduced on:
> PostgreSQL 19beta3 (Debian 19~beta3-1.pgdg13+1)
>
> How installed:
> Official Docker images library/postgres:18.6 and
> library/postgres:19beta3.
> Default postgresql.conf, no custom GUCs.
>
> Client:
> psql inside the container (docker exec ... psql -U postgres)
>
> Server logs:
> Nothing unusual. The queries succeed; the result value is wrong.
>
> Minimal self-contained reproducer (no tables):
>
> SELECT json_exists(j, 'strict $.a' FALSE ON ERROR)
> FROM (VALUES ('{}'::jsonb), (NULL)) AS t(j);
>
> -- actual: f, f
> -- expected: f, NULL
>
> SELECT json_exists(j, 'strict $.a' FALSE ON ERROR)
> FROM (VALUES (NULL::jsonb), ('{}')) AS t(j);
>
> -- actual: NULL, f
> -- expected: NULL, f
>
> Isolated scalars are correct:
>
> SELECT json_exists(NULL::jsonb, 'strict $.a' FALSE ON ERROR); -- NULL
> SELECT json_exists('{}'::jsonb, 'strict $.a' FALSE ON ERROR); -- f
>
> So the NULL-document case is only wrong when the same compiled expression
> has already taken ON ERROR on an earlier row.
>
> The leak is specifically the ON ERROR path, not "any prior non-NULL":
>
> -- LAX missing key is a clean false, not an error; NULL stays NULL
> SELECT json_exists(j, 'lax $.a' FALSE ON ERROR)
> FROM (VALUES ('{}'::jsonb), (NULL)) AS t(j);
> -- f, NULL (correct)
>
> -- successful match, then NULL stays NULL
> SELECT json_exists(j, 'strict $.a' FALSE ON ERROR)
> FROM (VALUES ('{"a":1}'::jsonb), (NULL)) AS t(j);
> -- t, NULL (correct)
>
> -- NULL, then a STRICT error, then NULL: the second NULL is poisoned
> SELECT json_exists(j, 'strict $.a' FALSE ON ERROR)
> FROM (VALUES (NULL::jsonb), ('{}'), (NULL)) AS t(j);
> -- NULL, f, f
> -- expected: NULL, f, NULL
>
> TRUE ON ERROR poisons NULL into true:
>
> SELECT json_exists(j, 'strict $.a' TRUE ON ERROR)
> FROM (VALUES ('{}'::jsonb), (NULL)) AS t(j);
> -- t, t
> -- expected: t, NULL
>
> The same sticky ON ERROR state affects JSON_VALUE and JSON_QUERY when
> the ON ERROR replacement is not NULL:
>
> SELECT json_value(j, 'strict $.a' RETURNING int DEFAULT 0 ON ERROR)
> FROM (VALUES ('{}'::jsonb), (NULL)) AS t(j);
> -- 0, 0
> -- expected: 0, NULL
>
> SELECT json_value(j, 'strict $.a' RETURNING int DEFAULT 0 ON ERROR)
> FROM (VALUES (NULL::jsonb), ('{}')) AS t(j);
> -- NULL, 0 (correct)
>
> SELECT json_query(j, 'strict $.a' EMPTY OBJECT ON ERROR)
> FROM (VALUES ('{}'::jsonb), (NULL)) AS t(j);
> -- {}, {}
> -- expected: {}, NULL
>
> Two independent JSON_EXISTS calls in one SELECT list do not share the
> state, so this is per compiled expression, not per backend:
>
> SELECT
> json_exists(NULL::jsonb, 'strict $.a' FALSE ON ERROR),
> json_exists('{}'::jsonb, 'strict $.a' FALSE ON ERROR),
> json_exists(NULL::jsonb, 'strict $.a' FALSE ON ERROR);
> -- NULL, f, NULL
>
> Likely cause (REL_18_STABLE):
>
> ExecInitJsonExpr() emits JUMP_IF_NULL on a NULL input document, targeting
> an EEOP_CONST NULL step that is intended to skip jsonpath evaluation.
> That CONST step then falls through into the "if jsestate->error then
> evaluate ON ERROR" steps.
>
> ExecEvalJsonExprPath() is the only place that resets jsestate->error /
> jsestate->empty (memset at the start of the function). The NULL-input
> jump skips that reset, so a previous row's error flag is still true and
> the ON ERROR replacement overwrites the NULL that was just stored.
>
> This matches every observation above: only a prior ON ERROR poisons later
> NULL inputs; a prior clean success does not; a later error poisons NULLs
> after it; UNKNOWN/NULL ON ERROR looks fine only because the replacement
> is already NULL.
>
> Originally visible as a Citus DISTINCT/WHERE discrepancy
> (https://github.com/citusdata/citus/issues/8792) because different shard
> scan orders hit NULL rows before or after a STRICT path error. The
> behavior reproduces on single-node vanilla PostgreSQL with VALUES.
>
>
>
>
>
Hi, Lyu!

Thanks for the report.

This is the same bug as BUG #19621.

Your analysis matches what we found there. On a SQL NULL document,
JUMP_IF_NULL skips EEOP_JSONEXPR_PATH, which is the only place that
clears jsestate->error / jsestate->empty. A later NULL row then still
sees the previous row's ON ERROR (or ON EMPTY) flag and takes the
replacement instead of returning NULL.

A patch (wait review) that clears those flags at the start of each JsonExpr
evaluation is already on that thread:
https://www.postgresql.org/message-id/CAB8bMisC9N06fQbJ0ie02Qzv5mO4K8rA=_Gb0UFzMZLCwft4QQ@mail.gmail.com

It has not been committed yet, which is why 18.6 and 19beta3 still show
the wrong results.

--
Regards,
Rachitskiy Andrey