Databasen - NULL dates

[Ovid]

--- Jess Robinson <castaway at desert-island.demon.co.uk> wrote:
> >  select o.id, i.id
> >  from   orders o, invoices i
> >  where  o.id = i.order_id
> >    and  ( o.date <> i.date or i.date <> '25122007' );
> >
> [ snip explanation]
> 
> I'd call that a spurious example, the question was for "unknown
> dates", if 
> you're creating a system of invoices, and you expect the dates to be 
> unknown at some point, something is very wrong. I would expect anyone

> designing the thing to a) know when to set nullable, and when not,
> and b) 
> set it on purpose, and thus also have it in mind when they're
> creating such queries..

Except ... that query gives a logically incorrect answer.  Go ahead and
rename those columns and business rules any way you want to until you
can call the dates "unknown".  That SQL will still give a logically
incorrect answer.

NULLs in databases lead to problems like this.  What's worse is when
you return thousands of rows of data and only have a handful which are
incorrectly excluded.  The problem might very well go undetected. 
NULLs should only be used with great caution.  People are quite
cavalier about their use and happily throw them in any ol' table just
because it's easier than figuring out the underlying problem
(disclaimer:  I've done the same thing, so "je m'accuse").

Cheers,
Ovid



--

Buy the book -- http://www.oreilly.com/catalog/perlhks/
Perl and CGI -- http://users.easystreet.com/ovid/cgi_course/