Re: Convert NOT IN sublinks to anti-joins when safe

On Wed, Feb 4, 2026 at 6:47 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Is there a convenient way to verify that an operator never returns
> NULL on non-null inputs?  Would it be sufficient to insist that the
> operator belongs to btree opclass (assuming that the strict ordering
> requirements of btree imply this safety)?

I think we can insist that the operator be a member of a btree or hash
opfamily.  Btree operators must adhere to strict total order, and hash
operators must adhere to strict equality; if they return NULL for
non-null inputs, the indexes themselves would be corrupt.

I'm less confident about other access methods like gist or gin.  Their
semantics can be more flexible, and using such operators in a NOT IN
clause is quite rare.

Attached is the updated patch, which adds the check requiring the
operator to be a member of a btree or hash opfamily.

> And, is it worth checking if an operator never returns NULL even on
> NULL inputs?  If we can identify such operators, we should be able to
> remove the requirement that both sides of NOT IN must be non-nullable.
> Is there a convenient way to check for such operators?

I don't know how to check for such operators, so I didn't do it in the
patch.

- Richard