agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error
2+ messages / 2 participants
[nested] [flat]
* BUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error
@ 2026-09-04 09:35 PG Bug reporting form <noreply@postgresql.org>
2026-09-04 10:12 ` Re: BUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-04 09:35 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 2530254482@qq.com
The following bug has been logged on the website:
Bug reference: 19654
Logged by: Ce Lyu
Email address: 2530254482@qq.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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error
2026-09-04 09:35 BUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-04 10:12 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andrey Rachitskiy @ 2026-09-04 10:12 UTC (permalink / raw)
To: 2530254482@qq.com; pgsql-bugs@lists.postgresql.org
пт, 4 сент. 2026 г. в 14:53, PG Bug reporting form <noreply@postgresql.org>:
> The following bug has been logged on the website:
>
> Bug reference: 19654
> Logged by: Ce Lyu
> Email address: 2530254482@qq.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...
It has not been committed yet, which is why 18.6 and 19beta3 still show
the wrong results.
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-04 10:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-04 09:35 BUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error PG Bug reporting form <noreply@postgresql.org>
2026-09-04 10:12 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox