pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedGIN jsonb_ops misses root strings in strict jsonpath queries
3+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-10-24 d44852026b 65f3b7096b 19656b77d1 0cf2c7e38f 3b2a60481c 327c5f0e6c 1b0b45c5ab Fix GIN index search for root strings in strict jsonpath queries.
* GIN jsonb_ops misses root strings in strict jsonpath queries
@ 2026-10-20 15:43 Rui Zhao <zhaorui126@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rui Zhao @ 2026-10-20 15:43 UTC (permalink / raw)
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Hi,
A GIN jsonb_ops scan can miss a root string when a jsonpath comparison
uses strict mode. I reproduced this on master at e75fcc9867f.
Using the existing test_jsonb data in jsonb.sql, which includes the
root string "a scalar":
SELECT test_json FROM test_jsonb
WHERE test_json @@ 'strict $ == "a scalar"';
-- "a scalar"
SELECT test_json FROM test_jsonb
WHERE test_json @? 'strict $ ? (@ == "a scalar")';
-- "a scalar"
CREATE INDEX test_jsonb_idx ON test_jsonb USING gin (test_json);
SET enable_seqscan = off;
SELECT test_json FROM test_jsonb
WHERE test_json @@ 'strict $ == "a scalar"';
-- 0 rows
SELECT test_json FROM test_jsonb
WHERE test_json @? 'strict $ ? (@ == "a scalar")';
-- 0 rows
The index stores root strings with JGINFLAG_KEY. Internally, a root
scalar is wrapped in an array. JsonbValueToJsonb() does this inside
its IsAJsonbScalar(val) branch:
scalarArray.type = jbvArray;
scalarArray.val.array.rawScalar = true;
scalarArray.val.array.nElems = 1;
pushJsonbValue(&pstate, WJB_BEGIN_ARRAY, &scalarArray); /* [ */
pushJsonbValue(&pstate, WJB_ELEM, val); /* original value */
pushJsonbValue(&pstate, WJB_END_ARRAY, NULL); /* ] */
gin_extract_jsonb() therefore sees WJB_ELEM and stores the root string
in the GIN index with the JGINFLAG_KEY marker.
But when processing the SQL query, jsonb_ops__extract_nodes() sets
key_entry to GIN_FALSE for the root in strict mode. That searches for
JGINFLAG_STR instead, so it cannot find the matching root string.
The attached patch changes that root case to GIN_TRUE. Both queries
then return "a scalar" using the existing index; no REINDEX is needed.
This bug affects only strict-mode queries using jsonb_ops.
The patch adds the queries above to jsonb.sql. make check passed all
239 tests. Without the code fix, only the two new indexed queries fail.
Regards,
Rui
Attachments:
v1-0001-Fix-GIN-lookups-for-root-strings-in-strict-jsonpa.patch (application/octet-stream, 3.8K) download
From e84329b08f3a7d58ff115e9e479a268dd65387aa Mon Sep 17 00:00:00 2001
From: Rui Zhao <zhaorui126@gmail.com>
Date: Tue, 20 Oct 2026 21:58:19 +0800
Subject: [PATCH v1] Fix GIN lookups for root strings in strict jsonpath
queries
A jsonb_ops index scan for 'strict $ == "a scalar"' misses a matching
root string. Root scalars are stored in a single-element array, so
gin_extract_jsonb() indexes strings with JGINFLAG_KEY. In strict mode,
jsonb_ops__extract_nodes() instead searches for JGINFLAG_STR at the root.
Use the key entry for root strings in strict mode. This matches existing
index entries and requires no reindexing. Add tests for both @@ and @?.
---
src/backend/utils/adt/jsonb_gin.c | 5 ++++-
src/test/regress/expected/jsonb.out | 29 +++++++++++++++++++++++++++++
src/test/regress/sql/jsonb.sql | 13 +++++++++++++
3 files changed, 46 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/jsonb_gin.c b/src/backend/utils/adt/jsonb_gin.c
index 96506661cb0..e1cc7df6674 100644
--- a/src/backend/utils/adt/jsonb_gin.c
+++ b/src/backend/utils/adt/jsonb_gin.c
@@ -442,7 +442,10 @@ jsonb_ops__extract_nodes(JsonPathGinContext *cxt, JsonPathGinPath path,
if (cxt->lax)
key_entry = GIN_MAYBE;
else if (!last) /* root ($) */
- key_entry = GIN_FALSE;
+ {
+ /* Root scalars are stored as single-element arrays. */
+ key_entry = GIN_TRUE;
+ }
else if (last->type == jpiAnyArray || last->type == jpiIndexArray)
key_entry = GIN_TRUE;
else if (last->type == jpiAny)
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index a483e4b15b0..b04387b2e2b 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -613,6 +613,35 @@ SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type =
t
(1 row)
+-- Root strings must match the entries used for string array elements.
+SELECT test_json FROM test_jsonb WHERE test_json @@ 'strict $ == "a scalar"';
+ test_json
+------------
+ "a scalar"
+(1 row)
+
+SELECT test_json FROM test_jsonb WHERE test_json @? 'strict $ ? (@ == "a scalar")';
+ test_json
+------------
+ "a scalar"
+(1 row)
+
+CREATE INDEX test_jsonb_idx ON test_jsonb USING gin (test_json);
+SET enable_seqscan = off;
+SELECT test_json FROM test_jsonb WHERE test_json @@ 'strict $ == "a scalar"';
+ test_json
+------------
+ "a scalar"
+(1 row)
+
+SELECT test_json FROM test_jsonb WHERE test_json @? 'strict $ ? (@ == "a scalar")';
+ test_json
+------------
+ "a scalar"
+(1 row)
+
+RESET enable_seqscan;
+DROP INDEX test_jsonb_idx;
-- corner cases
select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text;
?column?
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 791ee78f903..1939913562b 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -192,6 +192,19 @@ SELECT (test_json->>'field3') IS NULL AS expect_true FROM test_jsonb WHERE json_
SELECT (test_json->3) IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'array';
SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'array';
+-- Root strings must match the entries used for string array elements.
+SELECT test_json FROM test_jsonb WHERE test_json @@ 'strict $ == "a scalar"';
+SELECT test_json FROM test_jsonb WHERE test_json @? 'strict $ ? (@ == "a scalar")';
+
+CREATE INDEX test_jsonb_idx ON test_jsonb USING gin (test_json);
+SET enable_seqscan = off;
+
+SELECT test_json FROM test_jsonb WHERE test_json @@ 'strict $ == "a scalar"';
+SELECT test_json FROM test_jsonb WHERE test_json @? 'strict $ ? (@ == "a scalar")';
+
+RESET enable_seqscan;
+DROP INDEX test_jsonb_idx;
+
-- corner cases
select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text;
select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::int;
--
2.43.7
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: GIN jsonb_ops misses root strings in strict jsonpath queries
@ 2026-10-21 00:45 shihao zhong <zhong950419@gmail.com>
parent: Rui Zhao <zhaorui126@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: shihao zhong @ 2026-10-21 00:45 UTC (permalink / raw)
To: Rui Zhao <zhaorui126@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
Hi Rui,
I ran into the same bug on my side and ended up with the same one-line
fix, so +1 to v1.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: GIN jsonb_ops misses root strings in strict jsonpath queries
@ 2026-10-24 18:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2026-10-24 18:27 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: Rui Zhao <zhaorui126@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
shihao zhong <zhong950419@gmail.com> writes:
> Hi Rui,
> I ran into the same bug on my side and ended up with the same one-line
> fix, so +1 to v1.
Pushed with minor editorialization.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-10-24 18:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-20 15:43 GIN jsonb_ops misses root strings in strict jsonpath queries Rui Zhao <zhaorui126@gmail.com>
2026-10-21 00:45 ` shihao zhong <zhong950419@gmail.com>
2026-10-24 18:27 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox