Wednesday, July 21, 2010

Why null sucks. Reason 641.

A little bit of sql code here:

DECLARE @test DATETIME

SET @test = NULL

IF (@test <> '7/31/2010')
SELECT '@test is not equal to 7/31/2010'
ELSE SELECT '@test is equal to 7/31/2010'

Amazing. We've just figured out that a null date is equal to 7/31/2010. Who would have guessed? The reason this is problematic is that there is no way in the current implementations of sql to express n-value logic results. Comparing null to 7/31/2010 should result in null or unknown (or something else depending how many values are in your logic table). The If construct in SQL is boolean, but the test I showed above is not.

9 comments:

  1. I get a FALSE, works here.

    ReplyDelete
  2. Go lower.

    Deeper into memory management. Null is cool. Null is your friend. Null is better than when the Greeks invented the concept of zero.

    Null takes up no memory. Zero takes up as much memory as One.

    In database terms, in an INT(11) storing zero takes up as much memory as storing the number -2147483647 or 2147483647, or if you set it to UNSIGNED then 4294967295.

    So, save some space. use null when appropriate.

    Null is not appropriate with dates. Default it to NOW() or allow '0000-00-00 00:00:00'.

    And ALWAYS use IFNULL(). That function is your best friend.

    ReplyDelete
  3. Right, so the statement @test <> 7/31/2010 evaluates to false, the converse would be the natural conclusion, that @test is equal to 7/31/2010. That's why null values in boolean logic suck.

    I changed the printouts to make the statement a little more obvious.

    ReplyDelete
  4. @Anonymous2: I don't really care about storage space for one character. And adding a default value is somewhat workable, but really inappropriate for items where the date is being tested against.

    I would tend to agree with the camp that rejects the record for having a null or for eliminating the need for the null by refactoring into two tables.

    ReplyDelete
  5. Null really is nice. Not because of a saved byte, but because it has clear meaning - which is no value at all.

    Take a numeric field, for example, if some of your values are NULL then they don't affect aggregate calculations (AVG, for example). If you had a number, like 0 or -1, as the value then it would throw of your calculations.

    You only demonstrated why understanding NULL is important.

    Your test should either have used the clearer (and more concise "@test = '7/31/2010'" or you should have made it accurate by using "@test <> '7/31/2010' OR @test IS NULL".

    I realize in places like this that NULL can add some difficulty, but overall it is a beautiful thing (once you get used to it).

    ReplyDelete
  6. for null fields always check for null when doing comparisons. Null is a data type.

    if (@myvar is not null and @myvar <> 'some value')
    select 'equal'
    else
    select 'is null or not equal'

    ReplyDelete
  7. NULL suck? Surely not. It allows you to explicitly say, 'there is no data here'. Sure it has its foibles but they I think they don't outweigh the benifit and one only has to be aware of them. In TSQL:

    DECLARE @test DATETIME

    SET @test = NULL

    SELECT
    CASE WHEN @test IS NOT NULL AND @test = '7/31/2010'
    THEN '@test is equal to 7/31/2010'
    ELSE '@test is not equal to 7/31/2010'
    END

    ReplyDelete
  8. I always tell myself that NULLs are like black holes. They hardly really exist. You can't append anything to them, you can't add or subtract from them, you can't measure them, and you certainly can't use ANY equality operator with them (=, !=, <, >). Any comparison to a NULL value will return false. (Even NULL = NULL is false!) The only exception is the "IS" keyword in SQL which (unlike its ColdFusion twin) is not introspecting the value of the variable or column but instead asking a question about its existence.

    You know, this sounds too much like theology. I'd like to change my analogy from NULL being like a blackhole-- NULLs are more like God. You can't compare, measure, or represent them; you can only ask if they exist. :)

    ReplyDelete
  9. I'm like two years and a half late to this party, but I see nobody pointed out that this behavior, for Microsft SQL Server, depends on the ANSI_NULL setting. That's probably the reason why it works for the first commenting Anon.

    Comparing something to NULL, like in your code, is neither true nor false. The if statement is going through the ELSE because the IF expression is not true, period.

    If you SET ANSI_NULLS OFF; your code would behave as you expected. Otherwise you have to check for NULLs using something like IF(@test IS NOT NULL AND @test <> ...)

    Regards

    ReplyDelete