Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads
 the toast pointer instead of the text
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: chaitanyyachoudhary@gmail.com
Reply-To: chaitanyyachoudhary@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 18 Sep 2026 02:39:08 +0000
Message-ID: <19693-2ecd2b52c838b3e5@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19693-2ecd2b52c838b3e5%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

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

When a text or varchar column value is passed to a SQL/JSON query function
through PASSING and the value is stored out of line (TOAST), the jsonpath
variable does not contain the text. It contains a few bytes of the toast
pointer.

Steps to reproduce:

  CREATE TABLE t (c text);
  ALTER TABLE t ALTER COLUMN c SET STORAGE EXTERNAL;
  INSERT INTO t VALUES (repeat('x', 10000));

  SELECT length(c) AS stored,
         length(JSON_VALUE('{}', '$x' PASSING c AS x)) AS via_passing
  FROM t;

Result:

   stored | via_passing
  --------+-------------
    10000 |           3

Expected: 10000 in both columns.

The 3-character result is not part of the stored value:

  SELECT JSON_VALUE('{}', '$x' PASSING c AS x) =3D c FROM t;   -- f
  SELECT left(JSON_VALUE('{}', '$x' PASSING c AS x), 20) FROM t;   --
\x12\x14'

Forcing a detoast before PASSING gives the right answer:

  SELECT length(JSON_VALUE('{}', '$x' PASSING (c || '') AS x)) FROM t;   --
10000

A short value, which is stored inline, also works. JSON_QUERY and
JSON_EXISTS are affected the same way; for example

  SELECT JSON_EXISTS('{}', '$x ? (@ starts with "xxxxxxxxxx")' PASSING c AS
x) FROM t;

returns false for a value of ten thousand x's.

Cause:

In src/backend/utils/adt/jsonpath_exec.c, JsonItemFromDatum() handles
TEXTOID and VARCHAROID by reading the datum directly:

  case TEXTOID:
  case VARCHAROID:
      res->type =3D jbvString;
      res->val.string.val =3D VARDATA_ANY(val);
      res->val.string.len =3D VARSIZE_ANY_EXHDR(val);
      break;

The datum is never detoasted, so for an out-of-line value the macros read
the toast pointer's own bytes. The value comes from the PASSING argument
via GetJsonPathVar() -> JsonItemFromDatum(var->value, ...) and nothing on
that path detoasts it either. The other varlena cases in this function
(JSONB, and the datetime types through their output paths) go through code
that detoasts.

Fix: detoast the datum in that case, for example

  text *txt =3D DatumGetTextPP(val);
  res->val.string.val =3D VARDATA_ANY(txt);
  res->val.string.len =3D VARSIZE_ANY_EXHDR(txt);

The same code is present on REL_18_STABLE and master as of 2026-09-17.