Most of the existing answers focus on the non-SQL parts of an application, but there may be a problem in SQL too:
If instructed to filter out records where a user's last name is not available, someone who doesn't understand SQL very well may write a filter
WHERE u.lastname != 'NULL'. Because of the way SQL works, this will appear to check whether
u.lastname IS NOT NULL: all
NULL records get filtered out. All non-
NULL records remain.
Except of course for records where
u.lastname == 'NULL', but there may not have been any such record available during testing.
This becomes more likely if the SQL is generated by some sort of framework, where that framework doesn't expose an easily accessible way to check for non-
NULL-ness with parameters, and someone notices "hey, if I pass in the string
NULL, it does exactly what I want!"