Re: Fold NOT IN / <> ALL expressions containing NULL to FALSE

On 9/13/26 21:12, Rustam ALLAKOV wrote:

> 1. Multidimensional arrays
>
> CREATE TEMP TABLE mda (a int[]);
> INSERT INTO mda VALUES (NULL::int[]);
> SELECT * FROM mda WHERE 1 <> ALL (ARRAY[NULL::int[], a]);
>
> master: 1 row
> v2: 0 rows

Nice catch. I didn't consider multidimensional arrays. The
non-const-array branch in clauses.c walked ArrayExpr->elements and
folded to false whenever it saw a NULL Const there, without checking
multidims. For a multidimensional ArrayExpr each elements entry is a
sub-array, not a scalar - a NULL sub-array doesn't imply a NULL scalar
element of the result.

So we should skip the fold when ((ArrayExpr *) arrayarg)->multidims is true.

> 2. ON CONFLICT with a partial index
>
> CREATE TEMP TABLE t (a int, b int);
> CREATE UNIQUE INDEX ti ON t (a) WHERE b <> ALL (ARRAY[1, NULL]);
> INSERT INTO t VALUES (1, 5)
> ON CONFLICT (a) WHERE b <> ALL (ARRAY[1, NULL]) DO NOTHING;
>
> master: succeeds
> v2: ERROR: there is no unique or exclusion constraint matching
> the ON CONFLICT specification

onConflict->arbiterWhere was preprocessed as an ordinary EXPRKIND_QUAL,
so the new folding introduced by this patch reduced it to constant
false. But arbiterWhere is never evaluated at runtime -
infer_arbiter_indexes() only uses it to check, at plan time, whether a
candidate index's predicate is implied by it via predicate_implied_by(),
which has no notion of a bare "false" clause vacuously implying
anything. So the folded arbiterWhere stopped matching the (correctly
unfolded) index predicate, even though the two are logically identical.

Fix: a new preprocess_expression() kind, EXPRKIND_ARBITER_WHERE, that
gets the same qual-shaped treatment as EXPRKIND_QUAL (AND/OR flattening,
canonicalize_qual, make_ands_implicit, etc.) but is routed through plain
eval_const_expressions() instead of eval_const_expressions_qual(), so
it's exempt from the new folding. An alternative would be teaching
predicate_implied_by() that a literal "false" clause vacuously implies
anything - didn't go that route since it's a general-purpose proof
routine used well beyond ON CONFLICT, but open to it if preferred.

I added this fix in v3-0002 patch. If anyone sees a better way to fix
this, happy to hear it.

> 3. No folding under AND/OR
>
> CREATE TEMP TABLE s (x int);
>
> -- Plans with v2:
> EXPLAIN (COSTS OFF) SELECT * FROM s
> WHERE x NOT IN (42, NULL); -- One-Time Filter: false
> EXPLAIN (COSTS OFF) SELECT * FROM s
> WHERE x NOT IN (42, NULL) AND x = 1; -- Seq Scan
> EXPLAIN (COSTS OFF) SELECT * FROM s
> WHERE x NOT IN (42, NULL) OR false; -- Seq Scan
>
> Perhaps this could be handled in canonicalize_qual().

eval_const_expressions_mutator() clears context->is_qual unconditionally
at entry, so it never reaches past the first node. When the qual is
itself an AND/OR, its arguments - processed via
simplify_and/or_arguments() - never see is_qual = true. Propagating it
into AND/OR arguments is safe, but must not propogate into NOT, CASE, or
non-qual contexts.

simplify_and/or_arguments() now take an is_qual parameter and set
context->is_qual before each of their own recursive calls.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/