GIN (JSONPath) checks equality only on the first node of each operand - Mailing list pgsql-bugs

From Chinmay Kanchi
Subject GIN (JSONPath) checks equality only on the first node of each operand
Date
Msg-id CAJqPDh-tu5KY66KU8cvAEg3DQrZLzDZwjkLLa42zD+_Xm1WJAg@mail.gmail.com
Whole thread
Responses Re: GIN (JSONPath) checks equality only on the first node of each operand
List 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
```


pgsql-bugs by date:

Previous
From: Zsolt Parragi
Date:
Subject: Re: PG18: use-after-free in exec partition pruning after an EPQ recheck in LockRows
Next
From: expl0it3r@my-gate.net
Date:
Subject: Static libpq and ecpglib clash with PThreads4W on Windows