pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
GIN 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