It’s not just a song by the Fixx!
If there is a single concept in software testing that I always have difficulty teaching to new testers, it is the concept of “null” vs. zero or blank. Even seasoned developers will sometimes argue with me. But it is a key concept. Null is NOT zero, and null is NOT a blank. Zeros and blanks are actual data. A VALUE is available or stored. A null is NO DATA – Empty! To paraphrase John Cleese, a null is like a dead parrot: “It has NO data! Nothing is stored. It is sans data. Empty. It does not exist.” You can’t sort on it, and you can’t do arithmetic operations on it.
According to the Wikipedia….
Null means ‘nothing’ or without value or consequence.
So how does this affect our testing? A good programmer will typically ensure the all relevant values are initialized. For a numeric value this may be zero. For character data it may be a blank. Regardless, the field/data is not left empty. There is a HUGE difference between a value – even if it is zero – and empty (without value). Most database systems, when the database is created, will leave a field empty, or null, on creation unless a “not null” constraint is added or initial data is specified.
The easiest way to identify the problem (in a black box environment anyway) is to try to sort the data or do some type of arithmetic operation. I usually find it in a table of data where a column sort is allowed. Try sorting every field. A null will sometimes produce an error (for Oracle its a “NaN” error). Some systems will sort the data but the result will look strange. For example, if we are sorting numeric data (ascending order) – null values may appear first, followed by zeros, ones, etc. Sometimes the code may convert nulls to zeros so both null data and zeros will appear together in the sort. The output will look strange though and it can be a difficult error to find. You will usually see a similar result with character data.
To my thinking – all data fields should contain some data value even if it is zero or null. It can prevent a lot of potential processing problems down stream. The simplest way to fix it is during database initialization. Or during the data creation process. So to my developer friends – if you are storing data, and the record includes “optional” fields, please send the optional fields a zero or a blank!
You know what Smokey says – “Only you can prevent data errors!” Or something like that.