NSPredicate, Core Data, and NULL
Douglas Hill (via Peter Steinberger):
I’ve been implementing support for Apple Shortcuts in my reading app so users can create automated workflows. I noticed certain property-based article queries using
EntityPropertyQueryweren’t returning the expected number of articles. I had fourteen articles saved on the iPad simulator. Four of these articles were written by me. However when I searched for articles where the author was not “Douglas Hill”, there were only two results instead of the expected ten.[…]
I did some research online and second-hand sources supported my hypothesis.
NULLis not considered equal or unequal to anything in SQL, or in other words, comparisons with null are neither true nor false.
It’s confusing because—unless you explicitly check for NULL—rows where NULL values would be used in a comparison don’t participate in matching. This break all sorts of intuition. For example, combining the matches for P and !P does not give you everything.
From a programmer’s point of view, I wouldn’t say either way to handle null is unequivocally better. However I’d expect consistency from
NSPredicate. The surprising thing to me is that Core Data doesn’t smooth over this behaviour of SQL in order to match how comparisons usually work on Apple’s software stacks.
That might be confusing, too, though, and I’m not sure whether it would be possible in the general case, with more complex expressions. NULL also affects areas beyond comparisons. It’s just an unavoidable part of working with a database, which Core Data essentially is, despite Apple’s protestations.
What’s also weird, but not surprising when you think about it, is that NSPredicate is not only inconsistent when you use it directly vs. with Core Data but also within Core Data itself. One of the cool things about Core Data is that you can query all the objects in a context, including pending changes. But what this means is that the same Core Data fetch request can return different objects before and after saving, even if you didn’t change the context in between. It’s just that unsaved objects use the in-memory NSPredicate comparisons, whereas saved ones use SQL and its different handling of NULL.
1 Comment RSS · Twitter · Mastodon
This quirk is known to any good data analyst or engineer. It’s just a sucky thing because of fragmented SQL implementations. Best way to get around it? Don’t. Learn your SQL dialect and be okay with it. Use prudence when you’re unsure of a certain syntax or usage.
