GIN disagrees with seq scan when NOT and AND operators are combined
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)
```
Chinmay Kanchi <cgkanchi@gmail.com> writes:
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.
Thanks for the report. (For the archives' sake, note this repro
requires "set enable_seqscan = 0", else you get a seqscan plan
and no bug.)
I'm not too familiar with this code, but it looks like
extract_jsp_bool_expr concludes that !($.b == 20) is not supported
as a GIN scan condition, and then the next recursion level up does
the wrong thing with the negated && condition. The attached quick
hack seems to fix it. I didn't look around for other instances
of the same thinko, but there might be some.
regards, tom lane