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

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

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