On Wed, Feb 4, 2026 at 6:47 PM Richard Guo <[email protected]> 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
v2-0001-Convert-NOT-IN-sublinks-to-anti-joins-when-safe.patch
Description: Binary data
