GIN (JSONPath) checks equality only on the first node of each operand
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254140psql -h localhost -U postgresBuilt from patchset v2 (message #2), October 12, 2026 at 08:46 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254140_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254140_2 && git checkout t254140_2Patchset v2 (message #2) is on t254140_2
When checking equality with an operand that has additional nodes
("2".double()), GIN only considers the first node ("2"). So $.a ==
"2".integer() looks up a match for the json string "2" instead of the
number 2, and silently drops rows that a sequential scan would have
returned.
```
CREATE TABLE t (j jsonb);
INSERT INTO t VALUES ('{"a": 2}'), ('{"a": "2"}'), ('{"a": 3}');
CREATE INDEX t_ops ON t USING gin (j jsonb_ops);
CREATE INDEX t_path_ops ON t USING gin (j jsonb_path_ops);
SET enable_seqscan = off;
SET enable_indexscan = off; -- leave bitmap scans only
SELECT 'seq', count(*) FROM (SELECT j FROM t OFFSET 0) s WHERE j @@ '$.a ==
"2".double()'; -- 1
DROP INDEX t_path_ops;
SELECT 'jsonb_ops', count(*) FROM t WHERE j @@ '$.a == "2".double()';
-- 0, should be 1
SELECT 'jsonb_ops', count(*) FROM t WHERE j @? '$.a ? (@ == (-2).abs())';
-- 0, should be 1
CREATE INDEX t_path_ops ON t USING gin (j jsonb_path_ops);
DROP INDEX t_ops;
SELECT 'jsonb_path_ops', count(*) FROM t WHERE j @@ '$.a == "2".double()';
-- 0, should be 1
```
Chinmay Kanchi <cgkanchi@gmail.com> writes:
When checking equality with an operand that has additional nodes
("2".double()), GIN only considers the first node ("2"). So $.a ==
"2".integer() looks up a match for the json string "2" instead of the
number 2, and silently drops rows that a sequential scan would have
returned.
Hmph, another bug in the same function as your previous report :-(.
Here's a less-WIP patch for both.
regards, tom lane
Hi Tom,
Thanks for the fixes. I think v1 covers both reported problems.
While reading v1, I saw that extract_jsp_query() sends @@ expressions
to extract_jsp_bool_expr(). That function switches on node types such
as jpiAnd and jpiEqual. I wondered how it handles a predicate followed
by a method, so I tried ($.age == 25).size() in GDB. jsp->type was still
jpiEqual, with $.age on the left, 25 on the right, and size() as its
next item.
In an SQL expression tree, a function call would have a FuncExpr node
around its argument. Here, jsonpath_gram.y builds a list of the
predicate and accessor:
accessor_expr:
path_primary { $$ = list_make1($1); }
| '(' expr ')' accessor_op { $$ = list_make2($2, $4); }
| '(' predicate ')' accessor_op { $$ = list_make2($2, $4); }
| accessor_expr accessor_op { $$ = lappend($1, $2); }
;
expr:
accessor_expr { $$ = makeItemList($1); }
makeItemList() links ($.age == 25) and size() through next. The head
remains jpiEqual, but extract_jsp_bool_expr() ignores its next item.
V1 checks for following items on the literal operand, but not on the
predicate itself.
Using the existing testjsonb data from jsonb.sql:
SELECT count(*) FROM testjsonb
WHERE j @@ '($.age == 25).size().boolean()';
v1 v1 + 0001
Sequential scan 1012 1012
GIN jsonb_ops 2 1012
GIN jsonb_path_ops 2 1012
The equality takes this branch:
else if (jspIsScalar(right_item.type) &&
!jspHasNext(&right_item))
{
scalar_item = &right_item;
path_item = &left_item;
}
left_item is $.age, and right_item is the constant 25 with no next item.
The function therefore extracts $.age == 25 as the index condition,
ignoring the methods attached to the equality itself.
In this case, .size() returns 1 for both true and false, and .boolean()
turns that into true. All 1012 rows should match, but GIN returns only
the two rows with age = 25.
To be honest, this is a corner case, and I haven't found a more common
way to trigger it. It seems unlikely to occur in normal queries.
0001 adds this check at the start of extract_jsp_bool_expr():
if (jspHasNext(jsp))
return NULL;
It is on top of v1 and adds regression tests.
All 239 core regression tests passed.
Regards,
Rui