News for hackers. Stuff that matters.
Re:Things in MySQL that won't work as expected by tzs on Sunday November 07, 2010 @09:34PM (#1881147)
A lot of these come down to NULL makes things not work the way you might expect. Maybe C. J. Date is rightbwhen he says there should be no NULL in the relational model.
Re:Things in MySQL that won't work as expected by jerf on Sunday November 07, 2010 @10:24PM (#1881249)
Or possibly the NULL should behave more like a modern NULL and have a different truth table for it. I don't think it is coincidence that no modern NULL value in a programming language acts like the SQL NULL.

People will usually reply to my criticisms about SQL NULL by pointing out that it is logically consistent, which is true, but it's not the only logically consistent NULL we can define. I know the theory behind the SQL NULL quite well, well enough to observe just how rarely it matches SQL practice.

Re:Things in MySQL that won't work as expected by saurik on Monday November 08, 2010 @12:14AM (#1881478)
(I personally find the SQL null semantics incredibly clear and understandable, and find the weird hacks many languages take to have null mean something really confusing.)
Re:Things in MySQL that won't work as expected by dasil003 on Sunday November 07, 2010 @09:55PM (#1881190)
Is that what he meant? Because without having read any of his work, and relying only on my practice with relational DBs, I would say it would be nice if columns were not NULLable, thereby removing ambiguity from non-inner joins.
Re:Things in MySQL that won't work as expected by tzs on Sunday November 07, 2010 @10:52PM (#1881309)
Yes, he meant no NULL anywhere. The relational model as originally formulated by E.F. Codd did not have NULL. It was only much later that some tried to extend it by adding NULL (or even two different kinds of NULL).

In Date's book "Database in Depth" he gives some wonderful examples of how NULL in SQL breaks things. One I recall involved a couple simple tables and a simple question about the data. There were something like half a dozen or so reasonable ways one might write a query to answer the question--and there were many different answers depending on the exact query.

"Database in Depth" has been updated and refocused more on SQL, and is now called "SQL and Relational Theory". He sums up in the latter book the problem with NULL thusly:

    To sum up: If you have any nulls in your database, then
    you’re getting wrong answers to certain of your queries.
    What’s more, you have no way of knowing, in general,
    just which queries you’re getting wrong answers to and
    which not; all results become suspect. You can never
    trust the answers you get from a database with nulls.
    In my opinion, this state of affairs is a complete
    showstopper.
Re:Things in MySQL that won't work as expected by narrator on Sunday November 07, 2010 @10:46PM (#1881290)
It's possible to make a relational schema without nulls. Most people avoid it though because it adds extra tables and makes queries more complex, in some situations. In others it can make them simpler.