The Perils of Multi-valued logic


Perils of NULL usage:


I know there is a religious war about the use of NULLs and I don’t want to go down that path.  But about 10 years ago I worked for another one of Bill Gate’s companies and I had to prepare a sales report for him from our database.  To cut to the chase – I almost sent a report that was missing a large amount of the sales because I forgot about nulls.  And I have remembered it ever since.


And that is the point I want to make.  When you allow nulls in your database design, you run the risk of developers or report writers forgetting about them and producing and making business decisions on incorrect data.  I don’t consider this to be an acceptable risk for the most part – as confidence in your data is critical.


And we just don’t think about them.  For example:


_________________________________________________________


declare @nvar1 nchar(1)


declare @nvar2 nchar(1)


set @nvar1 = ‘A’


set @nvar2 = ‘B’


select @nvar1 + @nvar2


_________________________________________________________


We know this should return ‘AB’


But what does it return if we do this:


_________________________________________________________


set @nvar2 = NULL


_________________________________________________________


The answer is: it depends. 


It depends on the CONCAT_NULL_YIELDS_NULL setting.  If this setting is set ON for the session it will return NULL – as you cannot compare something with a null- which is the ANSI standard.  If the setting is OFF it will return ‘A’, which is the older behavior.  Also, please note that the CONCAT_NULL_YIELDS_NULL setting on the connection takes precedence over the database setting.


We need both options – one for the ANSI standard, and one for backward compatibility.  But it means that you as a developer must be thinking about nulls all the time.


So let’s take a look at 3-valued logic in general.


When we write SQL expressions we are very aware of expected behavior when these expressions evaluate to TRUE or FALSE.  However, we tend to forget about the third value – UNKNOWN.  This can lead to results that we did not anticipated; they are not incorrect, but we forgot to test for them.


So, here is a little exam just to point out how easy it is to be confused about 3-valued logic (cover the answers before you take the test J)  


Complete each box for two expressions that are ANDed.  (i.e., if exp1 evaluates to TRUE AND exp2 evaluates to TRUE, the Boolean AND of both is TRUE)
























AND


TRUE


FALSE


UNK


TRUE


 


 


 


FALSE


 


 


 


UNK


 


 


 


Now complete each box for two expressions that are ORed.  (i.e., if exp1 evaluates to true OR exp2 evaluates to TRUE, the Boolean OR of both is TRUE)
























OR


TRUE


FALSE


UNK


TRUE


 


 


 


FALSE


 


 


 


UNK


 


 


 


 


 


These are the correct answers:
























AND


TRUE


FALSE


UNK


TRUE


TRUE   


 FALSE


UNK


FALSE


FALSE


 FALSE


 FALSE


UNK


UNK


 FALSE


UNK


 
























OR


TRUE


FALSE


UNK


TRUE


TRUE


TRUE


TRUE


FALSE


TRUE


FALSE


UNK


UNK


TRUE


UNK


UNK


If you didn’t get it correct, then you need to consider the others in your team that won’t get it correct as well.  So best practice is to take an approach for null usage in all your code – and then publicize it and ensure compliance. 


Comments (0)