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
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.