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

Поиск
Список
Период
Сортировка
Искать

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

От:
Chinmay 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)
```




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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Chinmay Kanchi  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

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);
 			}
FAQ