The most painful reason NULLs are evil

I keep harping on about doing null: false everywhere, especially for strings and booleans, but sometimes there are sneaky exceptions for number fields, where a default of 0 does not make sense and the values will not be available for a time, some draft records etc.

You have to be extremely careful then because apparently NULLs are not "not equal" to anything. What do I mean?

Consider these User records:

id: 1, age: 20
id: 2, age: 25
id: 3, age: nil

How would you query for all users who are not 20?
where.not(age: 20), right? Sorry to say, but User#3 will be omitted from such queries. 😫

You have two options:

• denullify the age column (may be impossible)
• tweak the query to handle the silly null edge-case:

where.not(age: 20).or(where(age: nil))

Comments

[Vlastimil Pospíchal]

ID3 is maybe 20 years old, maybe not.