Thursday, 22 March 2018

Commitment vs. Forecast

One of my colleagues had pointed out (months ago) that the wording of the Scrum Guide has changed a little.

A bit late to mention it here perhaps, but the wording of the Scrum guide regarding Commitment has changed to Forecast.

The reasons for this can be found in the followoing article: Commitment vs. Forecast: A Subtle But Important Change to Scrum.

Thursday, 15 March 2018

Comparing NULLs in SQL

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);


StackOverflow - why is null not equal to null false
Baron Schwartz's Blog - Why NULL never compares false to anything in SQL