Skip to content
GitLab
  • Menu
Projects Groups Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in
  • CiviCRM Core CiviCRM Core
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
  • Issues 1,465
    • Issues 1,465
    • List
    • Boards
    • Service Desk
    • Milestones
  • Deployments
    • Deployments
    • Releases
  • Wiki
    • Wiki
  • Activity
  • Graph
  • Create a new issue
  • Commits
  • Issue Boards
Collapse sidebar
  • Development
  • CiviCRM CoreCiviCRM Core
  • Issues
  • #2253
Closed
Open
Created Dec 14, 2020 by JonGold@JonGoldDeveloper

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.

To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Assignee
Assign to
Time tracking