| From: | Chinmay Kanchi <cgkanchi(at)gmail(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | GIN (JSONPath) checks equality only on the first node of each operand |
| Date: | 2026-10-11 02:04:42 |
| Message-ID: | CAJqPDh-tu5KY66KU8cvAEg3DQrZLzDZwjkLLa42zD+_Xm1WJAg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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
```
| From | Date | Subject | |
|---|---|---|---|
| Next Message | expl0it3r | 2026-10-11 16:44:05 | Static libpq and ecpglib clash with PThreads4W on Windows |
| Previous Message | Zsolt Parragi | 2026-10-10 11:51:29 | Re: PG18: use-after-free in exec partition pruning after an EPQ recheck in LockRows |