In the real world, you cannot have an amount that is at the same time not 10 and not not 10. It has to be one of both.

My eldest son's age is neither 10 nor not 10. I don't have any children.

If you were to make a list of everyone whose eldest son is either 10 or not-10 years old, I would be rightly excluded from that list, just as I would be if you queried select from monks where son_age = 10 or not son_age = 10 and my son_age were NULL.

So a query that asks for all rows where the amount is 10 or the amount is not 10, must return all rows. SQL does not return the rows where the amount is "NULL" and that is where it is flawed and gives a wrong result.

I would argue that the flaw in this case is not NULL, but rather the lack of higher-order logic which is capable of recognizing that "X or not X" should always be true. By the Closed World Assumption that you mentioned, X and not X must each individually be considered false when dealing with an unknown value and "false or false" is false.

Even if we go to tri-state logic (or N-state), the truth values of "unknown number is 10" and "unknown number is not 10" are both "maybe" and the result of "maybe or maybe" is another "maybe". The only way to get a "true" result from "X or not X" when X is unknown is to recognize that one condition is the negation of the other and, therefore, one of them must be true even if we can't identify which it is. This has absolutely nothing to do with NULL, beyond NULL being a common way (probably the most common way) of indicating an unknown value.

And that still wouldn't properly address cases such as the age of my son, where X and not-X really are both false, despite one being the logical negation of the other.

If you want to have a value for "unknown", you must have TWO values for unknown (as per CODD).

I'm not particularly concerned with close adherence to the relational model, but I have already said a couple times here that things would be improved if there were distinct values for "UNKNOWN" and "EMPTY" rather than rolling both functions into NULL. You've mentioned your two desired unknowns as "Missing but Applicable" and "Missing but Inapplicable", which strike me as orthogonal to my two: I don't consider a value which is known to be nonexistent to be "missing" (although I suppose that might be the intended meaning of "missing but inapplicable") and your two still don't provide a clear way of indicating a value whose state is entirely unknown (it's definitely missing, but we don't know whether it's applicable or not).

In reply to Re^6: DBI: passing undef as an argument by dsheroh
in thread DBI: passing undef as an argument by fws

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.