GIN disagrees with seq scan when NOT and AND operators are combined

Started by Chinmay Kanchiabout 1 month ago2 messagesbugs
Jump to latest
#1Chinmay Kanchi
cgkanchi@gmail.com

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)
```

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Chinmay Kanchi (#1)
Re: GIN disagrees with seq scan when NOT and AND operators are combined

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

Attachments:

t254129_2
wip-fix-jsonb-gin-not-semantics.patchtext/x-diff; charset=us-ascii; name=wip-fix-jsonb-gin-not-semantics.patchDownload+8-2