pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedGIN disagrees with seq scan when NOT and AND operators are combined
2+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2026-10-13 5146b3f75d 154e2f1015 65f1b71c35 c138ab8a9b 04a8915fae d987ad61d3 b28a2b2bfa Fix two errors in extraction of GIN indexquals from jsonpath queries.
* GIN disagrees with seq scan when NOT and AND operators are combined
@ 2026-10-09 17:04 Chinmay Kanchi <cgkanchi@gmail.com>
2026-10-09 20:27 ` Re: GIN disagrees with seq scan when NOT and AND operators are combined Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Chinmay Kanchi @ 2026-10-09 17:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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)
```
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: GIN disagrees with seq scan when NOT and AND operators are combined
2026-10-09 17:04 GIN disagrees with seq scan when NOT and AND operators are combined Chinmay Kanchi <cgkanchi@gmail.com>
@ 2026-10-09 20:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2026-10-09 20:27 UTC (permalink / raw)
To: Chinmay Kanchi <cgkanchi@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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:
wip-fix-jsonb-gin-not-semantics.patch (text/x-diff, 1.1K) download
diff --git a/src/backend/utils/adt/jsonb_gin.c b/src/backend/utils/adt/jsonb_gin.c
index f5dbd5589d3..3efc4943d65 100644
--- a/src/backend/utils/adt/jsonb_gin.c
+++ b/src/backend/utils/adt/jsonb_gin.c
@@ -591,6 +591,7 @@ extract_jsp_bool_expr(JsonPathGinContext *cxt, JsonPathGinPath path,
case jpiAnd: /* expr && expr */
case jpiOr: /* expr || expr */
{
+ bool is_and = (not ^ (jsp->type == jpiAnd));
JsonPathItem arg;
JsonPathGinNode *larg;
JsonPathGinNode *rarg;
@@ -604,13 +605,18 @@ extract_jsp_bool_expr(JsonPathGinContext *cxt, JsonPathGinPath path,
if (!larg || !rarg)
{
- if (jsp->type == jpiOr)
+ /*
+ * For OR, we have to fail if either arm is unsupported.
+ * For AND, we can just ignore the unsupported arm and
+ * apply the supported one.
+ */
+ if (!is_and)
return NULL;
return larg ? larg : rarg;
}
- type = not ^ (jsp->type == jpiAnd) ? JSP_GIN_AND : JSP_GIN_OR;
+ type = is_and ? JSP_GIN_AND : JSP_GIN_OR;
return make_jsp_expr_node_binary(type, larg, rarg);
}
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-10-09 20:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-09 17:04 GIN disagrees with seq scan when NOT and AND operators are combined Chinmay Kanchi <cgkanchi@gmail.com>
2026-10-09 20:27 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox