agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text
4+ messages / 3 participants
[nested] [flat]
* BUG #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text
@ 2026-09-18 02:39 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-09-18 02:39 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: chaitanyyachoudhary@gmail.com
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text
@ 2026-09-19 00:12 Michael Paquier <michael@paquier.xyz>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Paquier @ 2026-09-19 00:12 UTC (permalink / raw)
To: chaitanyyachoudhary@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Sep 18, 2026 at 02:39:08AM +0000, PG Bug reporting form wrote:
> 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.
At quick glance, your take seems right and that looks like an
oversight. Would you like to write a patch?
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aq3TflHWHS-407w0@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text
@ 2026-09-19 03:34 shihao zhong <zhong950419@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 4+ messages in thread
From: shihao zhong @ 2026-09-19 03:34 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: chaitanyyachoudhary@gmail.com; pgsql-bugs@lists.postgresql.org
Hi,
I can reproduce this on master. It is not limited to EXTERNAL storage.
With the default storage an inline compressed value hits it too, so any
large text column passed with PASSING gives a wrong result.
Chaitanya, I hope you don't mind that I wrote it up. The fix is the one
you suggested.
Thanks,
Shihao
Attachments:
[application/octet-stream] v1-0001-Fix-PASSING-of-toasted-text-values-in-JSON-query-.patch (1.2K, ../../CAGRkXqQkSdHnDLTdJigJfj+gmcW5EMz0UiB+AJ9zcCy-7o-Z6w@mail.gmail.com/3-v1-0001-Fix-PASSING-of-toasted-text-values-in-JSON-query-.patch)
download | inline diff:
From 9fca6812d193e35ca184a8a6cc44388f09ba59b2 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Fri, 18 Sep 2026 23:25:43 -0400
Subject: [PATCH v1 1/2] Fix PASSING of toasted text values in JSON query
functions
---
src/backend/utils/adt/jsonpath_exec.c | 13 +++++++++----
1 file changed, 9 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 18021d60af1..50a3c4f4b4d 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -3346,10 +3346,15 @@ JsonItemFromDatum(Datum val, Oid typid, int32 typmod, JsonbValue *res)
break;
case TEXTOID:
case VARCHAROID:
- res->type = jbvString;
- res->val.string.val = VARDATA_ANY(DatumGetPointer(val));
- res->val.string.len = VARSIZE_ANY_EXHDR(DatumGetPointer(val));
- break;
+ {
+ /* The value may be toasted, so be sure to detoast it */
+ text *txt = DatumGetTextPP(val);
+
+ res->type = jbvString;
+ res->val.string.val = VARDATA_ANY(txt);
+ res->val.string.len = VARSIZE_ANY_EXHDR(txt);
+ break;
+ }
case DATEOID:
case TIMEOID:
case TIMETZOID:
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v1-0002-Add-tests-for-PASSING-of-toasted-text-values.patch (2.0K, ../../CAGRkXqQkSdHnDLTdJigJfj+gmcW5EMz0UiB+AJ9zcCy-7o-Z6w@mail.gmail.com/4-v1-0002-Add-tests-for-PASSING-of-toasted-text-values.patch)
download | inline diff:
From 24cdfa3fd5b834c1054d1bf999a7daff52734487 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Fri, 18 Sep 2026 23:25:43 -0400
Subject: [PATCH v1 2/2] Add tests for PASSING of toasted text values
---
src/test/regress/expected/sqljson_queryfuncs.out | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 5 +++++
2 files changed, 14 insertions(+)
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index ff64dce0c59..584ed3756d5 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1473,6 +1473,15 @@ SELECT JSON_QUERY(jsonb 'null', '$"Xyz"' PASSING 1 AS "Xyz");
1
(1 row)
+-- Test PASSING of a toasted text value
+CREATE TABLE test_passing_toast AS SELECT repeat('x', 10000) AS t;
+SELECT JSON_VALUE(jsonb 'null', '$a' PASSING t AS a) = t AS ok FROM test_passing_toast;
+ ok
+----
+ t
+(1 row)
+
+DROP TABLE test_passing_toast;
-- Test ON ERROR / EMPTY value validity for the function; all fail.
SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);
ERROR: invalid ON ERROR behavior
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index a69ef253f66..c1db78270ea 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -488,6 +488,11 @@ SELECT JSON_QUERY(jsonb 'null', '$Xyz' PASSING 1 AS Xyz);
SELECT JSON_QUERY(jsonb 'null', '$Xyz' PASSING 1 AS "Xyz");
SELECT JSON_QUERY(jsonb 'null', '$"Xyz"' PASSING 1 AS "Xyz");
+-- Test PASSING of a toasted text value
+CREATE TABLE test_passing_toast AS SELECT repeat('x', 10000) AS t;
+SELECT JSON_VALUE(jsonb 'null', '$a' PASSING t AS a) = t AS ok FROM test_passing_toast;
+DROP TABLE test_passing_toast;
+
-- Test ON ERROR / EMPTY value validity for the function; all fail.
SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);
SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR);
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text
@ 2026-09-19 10:29 Michael Paquier <michael@paquier.xyz>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Paquier @ 2026-09-19 10:29 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: chaitanyyachoudhary@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Sep 18, 2026 at 11:34:37PM -0400, shihao zhong wrote:
> I can reproduce this on master. It is not limited to EXTERNAL storage.
> With the default storage an inline compressed value hits it too, so any
> large text column passed with PASSING gives a wrong result.
Yep, that sounds pretty much right, but it also feels like the tests
could be extended a bit. Will adjust a few things and fix down to
v17.
Thanks for the report.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aq5j_NKhWTLcbzrT@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-09-19 10:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 02:39 BUG #19693: JSON_VALUE/JSON_QUERY PASSING a toasted text value reads the toast pointer instead of the text PG Bug reporting form <noreply@postgresql.org>
2026-09-19 00:12 ` Michael Paquier <michael@paquier.xyz>
2026-09-19 03:34 ` shihao zhong <zhong950419@gmail.com>
2026-09-19 10:29 ` Michael Paquier <michael@paquier.xyz>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox