Hi Denis, Thanks for review.
On 9/23/26 15:07, Denis Smirnov wrote:
I checked v3. The direct NOT IN form is folded to false, but some equivalent forms still scan the table: create table t(a int); insert into t values (1), (42), (null); -- folded to false explain (costs off) select * from t where a not in (42, null); -- these still scan the table explain (costs off) select * from t where not (a in (42, null)); explain (costs off) select * from t where not (a = any (array[42, null])); explain (costs off) select * from t where not not (a not in (42, null)); explain (costs off) select * from t where (a not in (42, null)) = true; create function not_in_null(integer) returns boolean language sql immutable as $$ select $1 not in (42, null) $$; explain (costs off) select * from t where not_in_null(a); It looks like the expression produced after removing NOT or inlining the function does not get another chance to use the new folding.
Yes, that's exactly the cause. The fold was only tried while simplifying the SAOP node itself, but negate_clause(), simplify_boolean_equality() and SQL function inlining produce the <> ALL node only after that step.
in new v4 patch the fold is a separate pass, simplify_qual_null_saops(), run over the already simplified expression. It looks through AND/OR only: an AND with a FALSE or NULL arguments becomes FALSE, and such arguments are dropped from an OR. It does not look like through NOT or into other expressions. This replaces the is_qual flag in eval_const_expressions_context, so the patch is simpler now. All five of your examples now produce "One-Time Filter: false".
There are also CASE WHEN conditions and aggregate FILTER clauses, where false and null have the same effect: explain (costs off, verbose) select case when a not in (42, null) then 1 else 0 end from t; explain (costs off, verbose) select count(*) filter (where a not in (42, null)) from t; explain (costs off, verbose) select a, count(*) filter (where a not in (42, null)) over () from t; These plans still contain the array comparison. The CASE expression could become 0, and the filters could become false. This does not necessarily mean that the table scan can be removed.
Agreed. The v4-patch the same pass to CASE WHEN conditions and to the FILTER clauses of aggregates and window functions. Your examples now give:
Output: 0 Output: count(*) FILTER (WHERE false) Output: a, count(*) FILTER (WHERE false) OVER w1 As you noted, the scan itself is kept in these cases.
Could you also add regression tests, at least for the issues fixed in v3: multidimensional arrays, ON CONFLICT with a partial index, and folding under AND/OR?Currently, the patch only updates the expected output of two existing queries. Tests checking both results and plans would help prevent these issues from coming back.
Done. -- Best regards, Ilia Evdokimov, Tantor Labs LLC, https://tantorlabs.com/
From 52ece8d5f10a50bf4ce54d3b71b62411302bfba0 Mon Sep 17 00:00:00 2001 From: Evdokimov Ilia <[email protected]> Date: Fri, 25 Sep 2026 18:05:55 +0500 Subject: [PATCH v4] Fold "x op ALL (array)" with a NULL element to false in qual context --- src/backend/commands/copy.c | 2 +- src/backend/optimizer/plan/planner.c | 32 +- src/backend/optimizer/plan/subselect.c | 2 +- src/backend/optimizer/util/clauses.c | 165 +++++++++ src/backend/optimizer/util/inherit.c | 2 +- src/include/optimizer/optimizer.h | 1 + src/test/regress/expected/insert_conflict.out | 18 + src/test/regress/expected/planner_est.out | 24 +- src/test/regress/expected/predicate.out | 336 ++++++++++++++++++ src/test/regress/sql/insert_conflict.sql | 13 + src/test/regress/sql/predicate.sql | 111 ++++++ 11 files changed, 685 insertions(+), 21 deletions(-) diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 003b70852bb..68cb535f73f 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -205,7 +205,7 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, } /* Reduce WHERE clause to standard list-of-AND-terms form */ - whereClause = eval_const_expressions(NULL, whereClause); + whereClause = eval_const_expressions_qual(NULL, whereClause); whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false); whereClause = (Node *) make_ands_implicit((Expr *) whereClause); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 55a35aa3397..884d6d61954 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -98,6 +98,7 @@ create_upper_paths_hook_type create_upper_paths_hook = NULL; #define EXPRKIND_TABLEFUNC 11 #define EXPRKIND_TABLEFUNC_LATERAL 12 #define EXPRKIND_GROUPEXPR 13 +#define EXPRKIND_ARBITER_WHERE 14 /* * Data specific to grouping sets @@ -1057,10 +1058,21 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name, preprocess_expression(root, (Node *) parse->onConflict->arbiterElems, EXPRKIND_ARBITER_ELEM); + /* + * arbiterWhere is never evaluated as a runtime qual: it is only + * used to match a partial index's predicate via + * predicate_implied_by() in infer_arbiter_indexes(). Use + * EXPRKIND_ARBITER_WHERE (not EXPRKIND_QUAL) so it still gets the + * usual qual-shaped preprocessing (AND/OR flattening, + * canonicalize_qual, implicit-AND format) but is not subject to the + * qual-only constant folding in eval_const_expressions_qual(), + * which could replace it with a bare Const and break the + * structural predicate match. + */ parse->onConflict->arbiterWhere = preprocess_expression(root, parse->onConflict->arbiterWhere, - EXPRKIND_QUAL); + EXPRKIND_ARBITER_WHERE); parse->onConflict->onConflictSet = (List *) preprocess_expression(root, (Node *) parse->onConflict->onConflictSet, @@ -1438,12 +1450,17 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind) * with AND directly under AND, nor OR directly under OR. */ if (kind != EXPRKIND_RTFUNC) - expr = eval_const_expressions(root, expr); + { + if (kind == EXPRKIND_QUAL) + expr = eval_const_expressions_qual(root, expr); + else + expr = eval_const_expressions(root, expr); + } /* * If it's a qual or havingQual, canonicalize it. */ - if (kind == EXPRKIND_QUAL) + if (kind == EXPRKIND_QUAL || kind == EXPRKIND_ARBITER_WHERE) { expr = (Node *) canonicalize_qual((Expr *) expr, false); @@ -1458,7 +1475,8 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind) * hashfuncid of any that might execute more quickly by using hash lookups * instead of a linear search. */ - if (kind == EXPRKIND_QUAL || kind == EXPRKIND_TARGET) + if (kind == EXPRKIND_QUAL || kind == EXPRKIND_TARGET || + kind == EXPRKIND_ARBITER_WHERE) { convert_saop_to_hashed_saop(expr); } @@ -1474,7 +1492,9 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind) /* Expand SubLinks to SubPlans */ if (root->parse->hasSubLinks) - expr = SS_process_sublinks(root, expr, (kind == EXPRKIND_QUAL)); + expr = SS_process_sublinks(root, expr, + (kind == EXPRKIND_QUAL || + kind == EXPRKIND_ARBITER_WHERE)); /* * XXX do not insert anything here unless you have grokked the comments in @@ -1491,7 +1511,7 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind) * would be unable to simplify a top-level AND correctly. Also, * SS_process_sublinks expects explicit-AND format.) */ - if (kind == EXPRKIND_QUAL) + if (kind == EXPRKIND_QUAL || kind == EXPRKIND_ARBITER_WHERE) expr = (Node *) make_ands_implicit((Expr *) expr); return expr; diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index 5760b616813..f1b95041cee 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -1984,7 +1984,7 @@ convert_EXISTS_to_ANY(PlannerInfo *root, Query *subselect, subroot.type = T_PlannerInfo; subroot.glob = root->glob; subroot.parse = subselect; - whereClause = eval_const_expressions(&subroot, whereClause); + whereClause = eval_const_expressions_qual(&subroot, whereClause); whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false); whereClause = (Node *) make_ands_implicit((Expr *) whereClause); diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 3e1f210652d..046335a2dc3 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -142,6 +142,8 @@ static Node *eval_const_expressions_mutator(Node *node, static bool contain_non_const_walker(Node *node, void *context); static bool ece_function_is_safe(Oid funcid, eval_const_expressions_context *context); +static bool saop_never_true(ScalarArrayOpExpr *saop); +static Node *simplify_qual_null_saops(Node *node); static List *simplify_or_arguments(List *args, eval_const_expressions_context *context, bool *haveNull, bool *forceTrue); @@ -2650,6 +2652,161 @@ eval_const_expressions(PlannerInfo *root, Node *node) return eval_const_expressions_mutator(node, &context); } +/*-------------------- + * eval_const_expressions_qual + * + * Same as eval_const_expressions, but for an expression that is used as a + * qual, i.e. in a context where a NULL result has the same effect as FALSE. + * This allows additional simplifications; see simplify_qual_null_saops. + * + * The input should be in explicit-AND format: the extra simplification does + * not look into the elements of an implicit-AND List. + *-------------------- + */ +Node * +eval_const_expressions_qual(PlannerInfo *root, Node *node) +{ + node = eval_const_expressions(root, node); + return simplify_qual_null_saops(node); +} + +/* + * saop_never_true + * Is this ScalarArrayOpExpr "x op ALL (array)" with a strict operator + * and an array known to contain a NULL element? + * + * Such an expression can never yield TRUE: each comparison against the NULL + * element yields NULL, so the result is either FALSE or NULL. + */ +static bool +saop_never_true(ScalarArrayOpExpr *saop) +{ + Node *arrayarg; + + if (saop->useOr) + return false; + + set_sa_opfuncid(saop); + if (!func_strict(saop->opfuncid)) + return false; + + arrayarg = lsecond(saop->args); + + if (IsA(arrayarg, Const)) + { + Const *arrayconst = (Const *) arrayarg; + + /* A NULL array is handled by ordinary constant folding */ + if (arrayconst->constisnull) + return false; + + return array_contains_nulls(DatumGetArrayTypeP(arrayconst->constvalue)); + } + else if (IsA(arrayarg, ArrayExpr)) + { + ArrayExpr *arrayexpr = (ArrayExpr *) arrayarg; + ListCell *lc; + + /* + * In a multidimensional ARRAY[] the elements are sub-arrays, and a + * NULL sub-array doesn't produce a NULL element: e.g. + * ARRAY[NULL::int[], NULL::int[]] is an empty array, over which ALL + * is TRUE. + */ + if (arrayexpr->multidims) + return false; + + foreach(lc, arrayexpr->elements) + { + Node *elem = (Node *) lfirst(lc); + + if (IsA(elem, Const) && ((Const *) elem)->constisnull) + return true; + } + } + + return false; +} + +/* + * simplify_qual_null_saops + * Simplify an already const-simplified boolean expression that is used + * in a context where NULL has the same effect as FALSE, such as a WHERE + * or JOIN qual, a CASE WHEN condition, or an aggregate FILTER clause. + * + * In such a context "x op ALL (array)" is replaced by constant FALSE if + * saop_never_true() says it can never yield TRUE. We also look through + * AND and OR, since replacing a NULL argument by FALSE cannot change whether + * an AND or OR yields TRUE. An AND with a FALSE (or NULL) argument then + * reduces to FALSE, and such arguments are dropped from an OR. We must not + * look through NOT, since NOT NULL is NULL but NOT FALSE is TRUE, nor into + * the arguments of any other kind of expression. + * + * This is done as a separate pass over the output of + * eval_const_expressions_mutator, rather than while simplifying the + * ScalarArrayOpExpr node itself, because NOTs pushed down by negate_clause(), + * simplified boolean equalities such as "(expr) = true", and inlined SQL + * functions can all produce such a node only after its own simplification + * is complete. + */ +static Node * +simplify_qual_null_saops(Node *node) +{ + if (node == NULL) + return NULL; + + if (IsA(node, ScalarArrayOpExpr)) + { + if (saop_never_true((ScalarArrayOpExpr *) node)) + return makeBoolConst(false, false); + } + else if (is_andclause(node) || is_orclause(node)) + { + BoolExpr *expr = (BoolExpr *) node; + bool isand = (expr->boolop == AND_EXPR); + List *newargs = NIL; + bool changed = false; + ListCell *lc; + + foreach(lc, expr->args) + { + Node *arg = (Node *) lfirst(lc); + Node *newarg = simplify_qual_null_saops(arg); + + if (newarg != arg) + changed = true; + + /* FALSE and NULL are equivalent here */ + if (IsA(newarg, Const) && + (((Const *) newarg)->constisnull || + !DatumGetBool(((Const *) newarg)->constvalue))) + { + if (isand) + return makeBoolConst(false, false); + changed = true; + continue; /* drop it from the OR */ + } + + /* Keep the result flat, as eval_const_expressions does */ + if ((isand && is_andclause(newarg)) || + (!isand && is_orclause(newarg))) + newargs = list_concat(newargs, ((BoolExpr *) newarg)->args); + else + newargs = lappend(newargs, newarg); + } + + if (!changed) + return node; + if (newargs == NIL) + return makeBoolConst(false, false); + if (list_length(newargs) == 1) + return (Node *) linitial(newargs); + return (Node *) makeBoolExpr(expr->boolop, newargs, expr->location); + } + + return node; +} + #define MIN_ARRAY_SIZE_FOR_HASHED_SAOP 9 /*-------------------- * convert_saop_to_hashed_saop @@ -2944,6 +3101,9 @@ eval_const_expressions_mutator(Node *node, aggfilter = (Expr *) eval_const_expressions_mutator((Node *) expr->aggfilter, context); + /* NULL and FALSE are equivalent in a FILTER clause */ + aggfilter = (Expr *) + simplify_qual_null_saops((Node *) aggfilter); /* And build the replacement WindowFunc node */ newexpr = makeNode(WindowFunc); @@ -3009,6 +3169,9 @@ eval_const_expressions_mutator(Node *node, } case T_Aggref: node = ece_generic_processing(node); + /* NULL and FALSE are equivalent in a FILTER clause */ + ((Aggref *) node)->aggfilter = (Expr *) + simplify_qual_null_saops((Node *) ((Aggref *) node)->aggfilter); if (context->root != NULL) return simplify_aggref((Aggref *) node, context); return node; @@ -3699,6 +3862,8 @@ eval_const_expressions_mutator(Node *node, /* Simplify this alternative's test condition */ casecond = eval_const_expressions_mutator((Node *) oldcasewhen->expr, context); + /* NULL and FALSE are equivalent in a WHEN condition */ + casecond = simplify_qual_null_saops(casecond); /* * If the test condition is constant FALSE (or NULL), then diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c index 6e1d2b14bc4..38074365a5c 100644 --- a/src/backend/optimizer/util/inherit.c +++ b/src/backend/optimizer/util/inherit.c @@ -863,7 +863,7 @@ apply_child_basequals(PlannerInfo *root, RelOptInfo *parentrel, childqual = adjust_appendrel_attrs(root, (Node *) rinfo->clause, 1, &appinfo); - childqual = eval_const_expressions(root, childqual); + childqual = eval_const_expressions_qual(root, childqual); /* check for flat-out constant */ if (childqual && IsA(childqual, Const)) { diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h index cb6241e2bdd..5be86a38a2f 100644 --- a/src/include/optimizer/optimizer.h +++ b/src/include/optimizer/optimizer.h @@ -145,6 +145,7 @@ extern bool contain_volatile_functions_after_planning(Expr *expr); extern bool contain_volatile_functions_not_nextval(Node *clause); extern Node *eval_const_expressions(PlannerInfo *root, Node *node); +extern Node *eval_const_expressions_qual(PlannerInfo *root, Node *node); extern void convert_saop_to_hashed_saop(Node *node); diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index b4c9bdb0e55..0258e9465e3 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -476,6 +476,24 @@ ERROR: there is no unique or exclusion constraint matching the ON CONFLICT spec insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) where fruit like '%berry' do update set fruit = excluded.fruit; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification drop index partial_key_index; +-- The inference WHERE clause must not be reduced to constant-FALSE even +-- though it can never be true, else it no longer matches the index predicate +create unique index null_saop_key_index on insertconflicttest(key) where fruit <> all (array['Apple', null]); +-- Succeeds +explain (costs off) +insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) do nothing; + QUERY PLAN +------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: null_saop_key_index + -> Result +(4 rows) + +insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) do nothing; +insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) and fruit like '%berry' do nothing; +drop index null_saop_key_index; +delete from insertconflicttest where key = 1001; -- -- Test that wholerow references to ON CONFLICT's EXCLUDED work -- diff --git a/src/test/regress/expected/planner_est.out b/src/test/regress/expected/planner_est.out index 236cb274a78..806970eebac 100644 --- a/src/test/regress/expected/planner_est.out +++ b/src/test/regress/expected/planner_est.out @@ -192,23 +192,23 @@ false, true, false, true); SELECT explain_mask_costs($$ SELECT * FROM tenk1 WHERE unique1 <> ALL (ARRAY[1, 2, 99, NULL]);$$, false, true, false, true); - explain_mask_costs ---------------------------------------------------------- - Seq Scan on tenk1 (cost=N..N rows=1 width=N) - Filter: (unique1 <> ALL ('{1,2,99,NULL}'::integer[])) -(2 rows) + explain_mask_costs +------------------------------------ + Result (cost=N..N rows=0 width=N) + Replaces: Scan on tenk1 + One-Time Filter: false +(3 rows) -- Try a non-const array containing a NULL SELECT explain_mask_costs($$ SELECT * FROM tenk1 WHERE unique1 <> ALL (ARRAY[1, 2, 98, (SELECT 99), NULL]);$$, false, true, false, true); - explain_mask_costs -------------------------------------------------------------------------------------- - Seq Scan on tenk1 (cost=N..N rows=1 width=N) - Filter: (unique1 <> ALL (ARRAY[1, 2, 98, (InitPlan expr_1).col1, NULL::integer])) - InitPlan expr_1 - -> Result (cost=N..N rows=1 width=N) -(4 rows) + explain_mask_costs +------------------------------------ + Result (cost=N..N rows=0 width=N) + Replaces: Scan on tenk1 + One-Time Filter: false +(3 rows) -- Verify that scalarineqsel() works on "char" columns CREATE TEMP TABLE char_table_1 AS diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out index feae77cb840..b7b3b2af4e0 100644 --- a/src/test/regress/expected/predicate.out +++ b/src/test/regress/expected/predicate.out @@ -954,3 +954,339 @@ SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN; (1 row) DROP TABLE bool_tab; +-- +-- Test that "x op ALL (array)" with a strict operator and a NULL array +-- element is reduced to constant-FALSE where NULL and FALSE are equivalent +-- +CREATE TABLE null_saop_tab (a int, b int, arr int[]); +INSERT INTO null_saop_tab VALUES (1, 1, NULL), (42, 2, '{}'), (NULL, 3, NULL); +ANALYZE null_saop_tab; +-- Ensure NOT IN and <> ALL are reduced to constant-FALSE +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL); + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL); + a | b | arr +---+---+----- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE a <> ALL (ARRAY[42, b, NULL]); + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab WHERE a <> ALL (ARRAY[42, b, NULL]); + a | b | arr +---+---+----- +(0 rows) + +-- Ensure equivalent forms are reduced to constant-FALSE as well +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE NOT (a IN (42, NULL)); + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab WHERE NOT (a IN (42, NULL)); + a | b | arr +---+---+----- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE NOT (a = ANY (ARRAY[42, NULL])); + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab WHERE NOT (a = ANY (ARRAY[42, NULL])); + a | b | arr +---+---+----- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE NOT NOT (a NOT IN (42, NULL)); + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab WHERE NOT NOT (a NOT IN (42, NULL)); + a | b | arr +---+---+----- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) = true; + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) = true; + a | b | arr +---+---+----- +(0 rows) + +CREATE FUNCTION null_saop_func(int) RETURNS bool LANGUAGE sql IMMUTABLE +AS $$ SELECT $1 NOT IN (42, NULL) $$; +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE null_saop_func(a); + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab WHERE null_saop_func(a); + a | b | arr +---+---+----- +(0 rows) + +DROP FUNCTION null_saop_func(int); +-- Ensure it is reduced under AND and OR +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) AND b = 1; + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) AND b = 1; + a | b | arr +---+---+----- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) OR b = 1; + QUERY PLAN +--------------------------- + Seq Scan on null_saop_tab + Filter: (b = 1) +(2 rows) + +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) OR b = 1; + a | b | arr +---+---+----- + 1 | 1 | +(1 row) + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab +WHERE (b = 1 AND a NOT IN (42, NULL)) OR (b = 2 AND NOT (a IN (7, NULL))); + QUERY PLAN +----------------------------------- + Result + Replaces: Scan on null_saop_tab + One-Time Filter: false +(3 rows) + +SELECT * FROM null_saop_tab +WHERE (b = 1 AND a NOT IN (42, NULL)) OR (b = 2 AND NOT (a IN (7, NULL))); + a | b | arr +---+---+----- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE (b = 1 OR a NOT IN (42, NULL)) AND a > 0; + QUERY PLAN +--------------------------------- + Seq Scan on null_saop_tab + Filter: ((a > 0) AND (b = 1)) +(2 rows) + +SELECT * FROM null_saop_tab WHERE (b = 1 OR a NOT IN (42, NULL)) AND a > 0; + a | b | arr +---+---+----- + 1 | 1 | +(1 row) + +-- Ensure it is reduced in a join clause +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab t1 LEFT JOIN null_saop_tab t2 + ON t1.a = t2.a AND t2.b NOT IN (42, NULL); + QUERY PLAN +------------------------------------ + Nested Loop Left Join + Join Filter: false + -> Seq Scan on null_saop_tab t1 + -> Result + Replaces: Scan on t2 + One-Time Filter: false +(6 rows) + +SELECT * FROM null_saop_tab t1 LEFT JOIN null_saop_tab t2 + ON t1.a = t2.a AND t2.b NOT IN (42, NULL) +ORDER BY t1.b; + a | b | arr | a | b | arr +----+---+-----+---+---+----- + 1 | 1 | | | | + 42 | 2 | {} | | | + | 3 | | | | +(3 rows) + +-- Ensure it is reduced in CASE WHEN conditions and FILTER clauses +EXPLAIN (VERBOSE, COSTS OFF) +SELECT b, CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END FROM null_saop_tab; + QUERY PLAN +---------------------------------- + Seq Scan on public.null_saop_tab + Output: b, 0 +(2 rows) + +SELECT b, CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END FROM null_saop_tab; + b | case +---+------ + 1 | 0 + 2 | 0 + 3 | 0 +(3 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT count(*) FILTER (WHERE a NOT IN (42, NULL)) FROM null_saop_tab; + QUERY PLAN +----------------------------------------- + Aggregate + Output: count(*) FILTER (WHERE false) + -> Seq Scan on public.null_saop_tab + Output: a, b, arr +(4 rows) + +SELECT count(*) FILTER (WHERE a NOT IN (42, NULL)) FROM null_saop_tab; + count +------- + 0 +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT b, count(*) FILTER (WHERE a NOT IN (42, NULL)) OVER () +FROM null_saop_tab; + QUERY PLAN +---------------------------------------------------- + WindowAgg + Output: b, count(*) FILTER (WHERE false) OVER w1 + Window: w1 AS () + -> Seq Scan on public.null_saop_tab + Output: b +(5 rows) + +SELECT b, count(*) FILTER (WHERE a NOT IN (42, NULL)) OVER () +FROM null_saop_tab; + b | count +---+------- + 1 | 0 + 2 | 0 + 3 | 0 +(3 rows) + +-- Ensure it is not reduced under NOT +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE NOT (a NOT IN (42, NULL)); + QUERY PLAN +---------------------------------------------- + Seq Scan on null_saop_tab + Filter: (a = ANY ('{42,NULL}'::integer[])) +(2 rows) + +SELECT * FROM null_saop_tab WHERE NOT (a NOT IN (42, NULL)); + a | b | arr +----+---+----- + 42 | 2 | {} +(1 row) + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS NOT TRUE ORDER BY b; + QUERY PLAN +------------------------------------------------------------------- + Sort + Sort Key: b + -> Seq Scan on null_saop_tab + Filter: ((a <> ALL ('{42,NULL}'::integer[])) IS NOT TRUE) +(4 rows) + +SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS NOT TRUE ORDER BY b; + a | b | arr +----+---+----- + 1 | 1 | + 42 | 2 | {} + | 3 | +(3 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT b, CASE WHEN NOT (a NOT IN (42, NULL)) THEN 1 ELSE 0 END +FROM null_saop_tab; + QUERY PLAN +----------------------------------------------------------------------------- + Seq Scan on public.null_saop_tab + Output: b, CASE WHEN (a = ANY ('{42,NULL}'::integer[])) THEN 1 ELSE 0 END +(2 rows) + +SELECT b, CASE WHEN NOT (a NOT IN (42, NULL)) THEN 1 ELSE 0 END +FROM null_saop_tab; + b | case +---+------ + 1 | 0 + 2 | 1 + 3 | 0 +(3 rows) + +-- Ensure it is not reduced where NULL and FALSE are not equivalent +EXPLAIN (VERBOSE, COSTS OFF) +SELECT b, a NOT IN (42, NULL) FROM null_saop_tab; + QUERY PLAN +-------------------------------------------------- + Seq Scan on public.null_saop_tab + Output: b, (a <> ALL ('{42,NULL}'::integer[])) +(2 rows) + +SELECT b, a NOT IN (42, NULL) FROM null_saop_tab; + b | ?column? +---+---------- + 1 | + 2 | f + 3 | +(3 rows) + +-- Ensure it is not reduced for a multidimensional ARRAY[]: NULL sub-arrays +-- don't add NULL elements, and here the array is empty, so ALL is true +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE 1 <> ALL (ARRAY[NULL::int[], arr]) ORDER BY b; + QUERY PLAN +---------------------------------------------------------- + Sort + Sort Key: b + -> Seq Scan on null_saop_tab + Filter: (1 <> ALL (ARRAY[NULL::integer[], arr])) +(4 rows) + +SELECT * FROM null_saop_tab WHERE 1 <> ALL (ARRAY[NULL::int[], arr]) ORDER BY b; + a | b | arr +----+---+----- + 1 | 1 | + 42 | 2 | {} + | 3 | +(3 rows) + +DROP TABLE null_saop_tab; diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index d119158549f..ad5fd21781e 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -251,6 +251,19 @@ insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) whe drop index partial_key_index; +-- The inference WHERE clause must not be reduced to constant-FALSE even +-- though it can never be true, else it no longer matches the index predicate +create unique index null_saop_key_index on insertconflicttest(key) where fruit <> all (array['Apple', null]); + +-- Succeeds +explain (costs off) +insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) do nothing; +insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) do nothing; +insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) and fruit like '%berry' do nothing; + +drop index null_saop_key_index; +delete from insertconflicttest where key = 1001; + -- -- Test that wholerow references to ON CONFLICT's EXCLUDED work -- diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql index 0f92bb52435..129c0957e40 100644 --- a/src/test/regress/sql/predicate.sql +++ b/src/test/regress/sql/predicate.sql @@ -448,3 +448,114 @@ SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN; SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN; DROP TABLE bool_tab; + +-- +-- Test that "x op ALL (array)" with a strict operator and a NULL array +-- element is reduced to constant-FALSE where NULL and FALSE are equivalent +-- +CREATE TABLE null_saop_tab (a int, b int, arr int[]); +INSERT INTO null_saop_tab VALUES (1, 1, NULL), (42, 2, '{}'), (NULL, 3, NULL); +ANALYZE null_saop_tab; + +-- Ensure NOT IN and <> ALL are reduced to constant-FALSE +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL); +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL); + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE a <> ALL (ARRAY[42, b, NULL]); +SELECT * FROM null_saop_tab WHERE a <> ALL (ARRAY[42, b, NULL]); + +-- Ensure equivalent forms are reduced to constant-FALSE as well +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE NOT (a IN (42, NULL)); +SELECT * FROM null_saop_tab WHERE NOT (a IN (42, NULL)); + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE NOT (a = ANY (ARRAY[42, NULL])); +SELECT * FROM null_saop_tab WHERE NOT (a = ANY (ARRAY[42, NULL])); + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE NOT NOT (a NOT IN (42, NULL)); +SELECT * FROM null_saop_tab WHERE NOT NOT (a NOT IN (42, NULL)); + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) = true; +SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) = true; + +CREATE FUNCTION null_saop_func(int) RETURNS bool LANGUAGE sql IMMUTABLE +AS $$ SELECT $1 NOT IN (42, NULL) $$; +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE null_saop_func(a); +SELECT * FROM null_saop_tab WHERE null_saop_func(a); +DROP FUNCTION null_saop_func(int); + +-- Ensure it is reduced under AND and OR +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) AND b = 1; +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) AND b = 1; + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) OR b = 1; +SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) OR b = 1; + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab +WHERE (b = 1 AND a NOT IN (42, NULL)) OR (b = 2 AND NOT (a IN (7, NULL))); +SELECT * FROM null_saop_tab +WHERE (b = 1 AND a NOT IN (42, NULL)) OR (b = 2 AND NOT (a IN (7, NULL))); + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE (b = 1 OR a NOT IN (42, NULL)) AND a > 0; +SELECT * FROM null_saop_tab WHERE (b = 1 OR a NOT IN (42, NULL)) AND a > 0; + +-- Ensure it is reduced in a join clause +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab t1 LEFT JOIN null_saop_tab t2 + ON t1.a = t2.a AND t2.b NOT IN (42, NULL); +SELECT * FROM null_saop_tab t1 LEFT JOIN null_saop_tab t2 + ON t1.a = t2.a AND t2.b NOT IN (42, NULL) +ORDER BY t1.b; + +-- Ensure it is reduced in CASE WHEN conditions and FILTER clauses +EXPLAIN (VERBOSE, COSTS OFF) +SELECT b, CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END FROM null_saop_tab; +SELECT b, CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END FROM null_saop_tab; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT count(*) FILTER (WHERE a NOT IN (42, NULL)) FROM null_saop_tab; +SELECT count(*) FILTER (WHERE a NOT IN (42, NULL)) FROM null_saop_tab; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT b, count(*) FILTER (WHERE a NOT IN (42, NULL)) OVER () +FROM null_saop_tab; +SELECT b, count(*) FILTER (WHERE a NOT IN (42, NULL)) OVER () +FROM null_saop_tab; + +-- Ensure it is not reduced under NOT +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE NOT (a NOT IN (42, NULL)); +SELECT * FROM null_saop_tab WHERE NOT (a NOT IN (42, NULL)); + +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS NOT TRUE ORDER BY b; +SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS NOT TRUE ORDER BY b; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT b, CASE WHEN NOT (a NOT IN (42, NULL)) THEN 1 ELSE 0 END +FROM null_saop_tab; +SELECT b, CASE WHEN NOT (a NOT IN (42, NULL)) THEN 1 ELSE 0 END +FROM null_saop_tab; + +-- Ensure it is not reduced where NULL and FALSE are not equivalent +EXPLAIN (VERBOSE, COSTS OFF) +SELECT b, a NOT IN (42, NULL) FROM null_saop_tab; +SELECT b, a NOT IN (42, NULL) FROM null_saop_tab; + +-- Ensure it is not reduced for a multidimensional ARRAY[]: NULL sub-arrays +-- don't add NULL elements, and here the array is empty, so ALL is true +EXPLAIN (COSTS OFF) +SELECT * FROM null_saop_tab WHERE 1 <> ALL (ARRAY[NULL::int[], arr]) ORDER BY b; +SELECT * FROM null_saop_tab WHERE 1 <> ALL (ARRAY[NULL::int[], arr]) ORDER BY b; + +DROP TABLE null_saop_tab; -- 2.43.0
