Databasen - NULL dates

[Andy Wardley]

Ovid wrote:
> As your business rules get more complex, the more NULLs you have in a
> database, the more likely it is that your queries will return incorrect
> results.  

I've always adopted the policy that if a column can be NULL then the query
should explicitly account for that:

   where  o.id = i.order_id
     and  (i.date and i.date <> '25122007')
      or  (i.date and o.date and i.date <> o.date);

The same is true in Perl.  If a value can be undefined then any test should
begin with a clause that asserts that the value is defined.

    if ((defined $i_date && $i_date ne '25122007')
    ||  (defined $i_date && defined $o_date && $i_date ne $o_date)) {
	...etc...
    }

Otherwise you'll get "undefined value" warnings (at best) or unpredictable
results (at worst).

So unless I've missed something, it suggests that it's not so much a case
of NULL dates being at fault per se, but queries that fail to account for
the fact that a date (or any other value) can be NULL.

 > This isn't to say I haven't used NULL columns in databases, but you
 > have to try and judge whether potentially incorrect queries will cost
 > more than the extra effort to avoid NULLs.

Ack.  It's one thing saying "It's OK to use NULL dates if your queries are
written correctly" and another to know that every person who may ever write
queries for the DB understands that.

A