agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 2530254482@qq.com
Subject: BUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error
Date: Fri, 04 Sep 2026 09:35:37 +0000
Message-ID: <19654-3acd06154d027634@postgresql.org> (raw)

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.

view thread (2+ messages)  latest in thread

Message-ID: <19654-3acd06154d027634@postgresql.org>
Permalink:  ../19654-3acd06154d027634@postgresql.org/
Also on:    postgresql.org/message-id/19654-3acd06154d027634@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 2530254482@qq.com
  Subject: Re: BUG #19654: JSON_EXISTS returns ON ERROR value for SQL NULL after a prior error
  In-Reply-To: <19654-3acd06154d027634@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox