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

On 7/31/26 18:36, Yugo Nagata wrote:

> If I understand the patch correctly, the optimization seems to be applied
> not only to <> ALL/NOT IN, but to any op ALL expression whose operator is
> strict. Is that right?

Yes, that's right. The code only checks `!saop->useOr` (ALL semantics)
and `func_strict`. It never inspects which specific operator is
involved. `x op ALL (x1, ..., xn)` is equivalent to `x op x1 AND ... x
op xn`. If `xi = NULL` and `op` is strict, expression can only be FALSE
or NULL. Since a qual treats FALSE and NULL identically, finding one
NULL array element is enough to fold SAOP to FALSE.

> This could improve performance when the array contains many elements including
> NULL, especially if one of the elements takes a long time to evaluate.
> For example, after applying the patch, the following query returns immediately
> without evaluating pg_sleep(3):
>
> postgres=# explain analyze select * from tbl where i not in (null, (select 1 from pg_sleep(3)));
> QUERY PLAN
> ---------------------------------------------------------------------------------------
> Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.004..0.004 rows=0.00 loops=1)
> Replaces: Scan on tbl
> One-Time Filter: false
> Planning Time: 0.088 ms
> Execution Time: 0.033 ms
> (5 rows)
>
> This seems like a nice optimization. However, I wonder whether skipping the
> evaluation of subqueries or function calls in the array could cause compatibility
> issues, especially if they have side effects.

I don't think this is a new risk. The docs already say that if an
expression's result can be determined from only part of it, the rest
need not be evaluated at all, and that relying on side effects in
WHERE/HAVING is unsafe for that reason (4.2.14. Expression Evaluation
Rules). The sibling subquery form `expr op ALL (subquery)` even states
this explicitly - "it's unwise to assume that the subquery will be
evaluated completely" - so the array form was simply the one case in
this family where that guarantee wasn't yet being enforced.

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