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: chaitanyyachoudhary@gmail.com
Subject: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
Date: Fri, 18 Sep 2026 02:45:17 +0000
Message-ID: <19695-2f2c573ef1660737@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19695
Logged by:          Chaitanya Choudhary
Email address:      chaitanyyachoudhary@gmail.com
PostgreSQL version: 18.6
Operating system:   macOS 26 (aarch64), Homebrew build of 18.6
Description:        

Within one statement, after a JSON_VALUE(... RETURNING jsonb) or
RETURNING json evaluation yields SQL NULL, every later evaluation of that
expression in the same statement also yields NULL, even when the input has
a value. Other RETURNING types are not affected.

Steps to reproduce:

  SELECT JSON_VALUE('123', '$' RETURNING jsonb),
         JSON_VALUE('null', '$' RETURNING jsonb);
  -- 123 | (null)     correct

  SELECT JSON_VALUE('null', '$' RETURNING jsonb),
         JSON_VALUE('123', '$' RETURNING jsonb);
  -- (null) | (null)  expected (null) | 123

The same across rows of a scan:

  SELECT JSON_VALUE(x, '$' RETURNING jsonb)
  FROM (VALUES ('1'::jsonb), ('null'), ('2')) v(x);
  -- 1, (null), (null)   expected 1, (null), 2

  SELECT JSON_VALUE(x, '$' RETURNING jsonb)
  FROM (VALUES ('1'::jsonb), ('2')) v(x);
  -- 1, 2                correct: no NULL came first

RETURNING int is not affected:

  SELECT JSON_VALUE('null', '$' RETURNING int),
         JSON_VALUE('123', '$' RETURNING int);
  -- (null) | 123

A NULL produced by a JSON null item, by EMPTY (no match), or by an error
converted to NULL under NULL ON ERROR all trigger it. RETURNING json
behaves like RETURNING jsonb. JSON_QUERY and JSON_EXISTS are not affected.

Notes on the cause:

The result depends on what earlier rows or earlier calls in the same
statement returned, so some state persists across evaluations of the
expression. The RETURNING json/jsonb coercion runs through
ExecEvalJsonCoercion() in src/backend/executor/execExprInterp.c, which
calls json_populate_type() with a per-expression cache
(op->d.jsonexpr_coercion.json_coercion_cache) and with op->resnull passed
by pointer as the isnull argument. That cache lives for the statement and
is the only state shared between the evaluations. I have not traced the
exact line where the null is retained.

The code involved is unchanged between 18.6 and master as of 2026-09-17.

view thread (3+ messages)  latest in thread

Message-ID: <19695-2f2c573ef1660737@postgresql.org>
Permalink:  ../19695-2f2c573ef1660737@postgresql.org/
Also on:    postgresql.org/message-id/19695-2f2c573ef1660737@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, chaitanyyachoudhary@gmail.com
  Subject: Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
  In-Reply-To: <19695-2f2c573ef1660737@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