use Perl

• Not quite right (Score:1)

  by huxtonr (8912) on 2009.12.07 20:21 (#71344)

  Now consider a simpler, yet silly, example: SELECT first_name FROM employee WHERE salary > 50000; What happens if the salary field is NULL? You'll get a list of employees whose known salary is NULL.

  I can't figure out what you meant to say there, but the query doesn't seem to go with the text.

  In this scenario, it is the case that everyone has a salary; you just don't know what some of them are. So here's the kicker: SELECT first_name FROM employee WHERE salary = salary; That won't return anyone on the board of directors, even though you know they have a salary.

  You don't know that though. Not within the terms of the database. If you did, they'd have a numeric salary and not NULL.

  Furthermore, most would think it's self-evident that p = p, but in three value logic of databases, this is sometimes true and sometimes false.

  No, not quite right. The expression p = p will always be either TRUE or NULL. Anything compared to NULL returns NULL. This will behave the same as FALSE if you test for truth since the test will still fail.

  If you aren't enjoying bashing your head on these little "features" then I recommend staying well away from how nulls interact with record types. There appears to be no logic there at all.

  Reply to This

  • Re: (Score:2)

    by Ovid (2709)

    Ack, fixed the typo regard "known salary is null".

    You don't know that though. Not within the terms of the database. If you did, they'd have a numeric salary and not NULL.

    That's entirely the point. Database "logic", when combined with NULLs, returns answers that we really know are not true. I don't mean "within the terms of the database". p = p is true, regardless of whether or not a poorly designed query language says otherwise.