BUG #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text - Mailing list pgsql-bugs

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:

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) = 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 = jbvString;
      res->val.string.val = VARDATA_ANY(val);
      res->val.string.len = 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 = DatumGetTextPP(val);
  res->val.string.val = VARDATA_ANY(txt);
  res->val.string.len = VARSIZE_ANY_EXHDR(txt);

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