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

Started by Ilia Evdokimov3 days ago1 messageshackers
Jump to latest
#1Ilia Evdokimov
ilya.evdokimov@tantorlabs.com

Hi everyone,

In commit c95cd299, we added an early-exit in `scalararraysel()` to
return selectivity 0.0 when a NOT IN / <> ALL list contains a NULL and
the operator is strict. The commit message noted a possible follow-up:

    In the future, it might be better to do something for this case in
    constant folding.  We would need to be careful to only do this for
    strict operators on expressions located in places that don't care about
    distinguishing false from NULL returns. i.e. EXPRKIND_QUAL expressions.
    Doing that requires a bit more thought and effort, so here we just fix
    some needlessly slow selectivity estimations for ScalarArrayOpExpr
    containing many array elements and at least one NULL.

This patch implements that follow-up.

When a <> ALL / NOT IN expression appears in a qual context and its
array contains a NULL element, the expression can never evaluate to
true; it can only return false or NULL. In a qual, both mean the row is
excluded. We can therefore fold the entire SAOP to constant false during
`eval_const_expressions()`, which the planner can then use to eliminate
the scan entirely.

A new `is_qual` flag is added to `eval_const_expressions_context`. A new
function `eval_const_expressions_qual()` sets this flag and is called
from sites that process WHERE/qual expressions. To prevent the flag from
leaking into non-qual contexts (e.g. `func(x NOT IN (NULL, 1))`),
is_qual is saved into a local variable and immediately reset to false at
the start of `eval_const_expressions_mutator`. Only the SAOP case reads
`this_node_is_qual` - after processing its arguments with `is_qual = false`.

Any suggestions?

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

Attachments:

v1-0001-Fold-NOT-IN-ALL-with-NULL-array-element-to-false-.patchtext/x-patch; charset=UTF-8; name=v1-0001-Fold-NOT-IN-ALL-with-NULL-array-element-to-false-.patchDownload+96-15