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 #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text
Date: Fri, 18 Sep 2026 02:39:08 +0000
Message-ID: <19693-2ecd2b52c838b3e5@postgresql.org> (raw)

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.

view thread (2+ messages)  latest in thread

Message-ID: <19693-2ecd2b52c838b3e5@postgresql.org>
Permalink:  ../19693-2ecd2b52c838b3e5@postgresql.org/
Also on:    postgresql.org/message-id/19693-2ecd2b52c838b3e5@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 #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text
  In-Reply-To: <19693-2ecd2b52c838b3e5@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