Proposal: API4 should add `IS NULL` to `!=` queries
Andrew Hunt named this problem in a blog post a while back - the database schema often allows NULL where a reasonable person would assume that is identical to FALSE. Most notably with is_deceased, but there are lots of other places in the schema.
As we move Search Kit into mainstream use, we're going to run into problems where common sense and SQL disagree.
Consider the search kit results for "Prefix is Mr." and "Prefix is not Mr.". An end user would expect the sum of both of those to be everyone in the database. However, anyone without a prefix is excluded.
At first I thought the answer was an overhaul of the schema - but there's simply too many instances where 0 and NULL mean two different things.
I propose that when the "not equals" operator is used, we should add OR <field> IS NULL. I'm not sure whether an OR here breaks indexing but this seems like it's going to become a much more frequent complaint soon.