GIN disagrees with seq scan when NOT and AND operators are combined - Mailing list pgsql-bugs

From Chinmay Kanchi
Subject GIN disagrees with seq scan when NOT and AND operators are combined
Date
Msg-id CAJqPDh9Cm=x8eYZ1Jf=_avd2WEqc=0t_f7_SO4igo-CKPi25_Q@mail.gmail.com
Whole thread
Responses Re: GIN disagrees with seq scan when NOT and AND operators are combined
List pgsql-bugs
Hi all,

When working on pg_lion (https://github.com/cgkanchi/pg_lion), I stumbled upon a bug where GIN returns fewer rows when multiple ! operators are specified. I was able to repro this on all versions from 16 to trunk.

Repro:
```
postgres=# CREATE TABLE t (j jsonb);
INSERT INTO t VALUES ('{"a": 1, "b": 10}'), ('{"a": 2, "b": 10}'), ('{"a": 2, "b": 20}');
CREATE TABLE
INSERT 0 3
postgres=# SELECT j FROM t WHERE j @@ '!(!($.a == 1) && $.b == 20)';
         j
-------------------
 {"a": 1, "b": 10}
 {"a": 2, "b": 10}
(2 rows)

postgres=# create index on t using gin(j);
CREATE INDEX
postgres=# SELECT j FROM t WHERE j @@ '!(!($.a == 1) && $.b == 20)';
         j
-------------------
 {"a": 1, "b": 10}
(1 row)
```




pgsql-bugs by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: PG18: use-after-free in exec partition pruning after an EPQ recheck in LockRows
Next
From: Tom Lane
Date:
Subject: Re: GIN disagrees with seq scan when NOT and AND operators are combined