Consider the following table "mm_mailtable", containing the following data:
| id | name | toname | whensent | subject | haveread | newmail | |
|---|---|---|---|---|---|---|---|
| 27999 | William | Joe | 2018-03-13 12:41:25 | Golf next week? | 1 | 0 | |
| 28000 | William | Linda | 2018-03-13 12:41:25 | I'm out! | 1 | 0 | |
| 28001 | Linda | William | 2018-03-13 12:41:25 | Okay! | 1 | 0 | |
| 28002 | Joe | William | 2018-03-13 12:41:25 | Sure thing! | 1 | 1 | |
| 28003 | Jim | William | 2018-03-13 12:41:25 | The house | 1 | NULL |
Also consider the following query I found in our source code somewhere.
FROM mm_mailtable
WHERE toname = :NAME
AND newmail = COALESCE(:NEWMAIL, newmail);
Let's say we try the query out with "William" as NAME and 0 as NEWMAIL.
We get an old mail to William with "Okay!" as subject.
Let's say we try the query out with "William" as NAME and 1 as NEWMAIL.
We get a new mail to William with "Sure thing!" as subject.
Let's say we try the query out with "William" as NAME and NULL as NEWMAIL.
Now I would expect to get all three entries returned, but the one containing the NULL value for "newmail" is not returned.
It takes some getting used to, but comparing null values is not possible in most databases, as it is undefined (a.k.a.. NULL).
See for more and better explanations the references below.
P.S. in this case, in our database, the column "newmail" should have been defined as "NOT NULL" and given a "DEFAULT" value, to prevent this sort of thing. Apparently it was forgotten.
Rewriting the query
This should work:
FROM mm_mailtable
WHERE toname = :NAME
AND (:NEWMAIL is null OR :NEWMAIL = newmail);
References
- StackOverflow - why is null not equal to null false
- https://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false
- Baron Schwartz's Blog - Why NULL never compares false to anything in SQL
- https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/
