Is Null Null or Not Null

One of the people on the AppDeploy forums recently asked for the option of having null fields be represented in the tables by an empty string instead of the string "<null>".

Another person responded by saying that that would be bad, because a null string is not the same as an empty string.

Well, usually that is correct, and can be quite an important distinction. In most databases, a null field is certainly different from an empty string. However in the case of Windows Installer databases, after storing an empty string in the database, calling MsiRecordIsNull on that field will return true. So a non-null string (empty) can be a null string, at least for Windows Installer.

Of course, I have already examined the issue of null integers being represented by a special number. Again, a non-null integer can be a null integer.

But, by the time we get to binary fields (the last remaining fundamental type), we find suddenly that null binary fields really are null, and are distinct from every other binary value.

That is, the only way to store a null binary field is to pass NULL to the MsiRecordSetStream function. Passing a path to an empty file results in a non-null binary field of 0 bytes.

Actually, it's not strictly true that passing NULL to the MsiRecordSetStream function is the only way to set a binary field to null. You can actually call MsiRecordSetInteger with the MSI_NULL_INTEGER value. This will happily set a binary field to null.

So null is not null but it is null.

As for the original request to use an empty string to display null values, I added the option to the next release (accessible only in the registry settings at the moment). In fact you can use any string you like to represent null now.

Unfortunately, having an empty string to represent null values makes scanning the rows a bit difficult (there is no grid to help out), and also is indistinguishable from a string of white space. Which is rare but probably not quite as rare as "<null>". Back to the problem of special values.