Hacker News Reader

Comment by ak39 | Hacker News Reader

From the article: "The SQL null value basically means “could be anything”."

I wouldn't call the NULL an unknown or as a "could be anything". It's tempting to think it's the Schrodinger's Cat phenomenon, but I choose to interpret the NULL in a less sexy way: Unknown means that the state DOES NOT EXIST. It's as simple as that. Remember in relational modeling (cardinality & ordinality) the thinking is always: zero, 1 or more than 1. The NULL is simply the zero here ( which means the state does NOT exist). Interpret it as "irrelevant".

Interpreting it as either true or false (because it is unknown) is over explaining.

View on HN

NULLs are built into the algebraic and arithmetic machinery of SQL. You can see how the equality operator (=) acts in the face of NULLs.

    = | F | T | N
   ---+---+---+---
    F | 1 | 0 |
   ---+---+---+---
    T | 0 | 1 |
   ---+---+---+---
    N |   |   |

Using the SELECT 1=NULL; syntax of SQLite we can see that SQLite does not even attempt to provide an answer. What would we say of something that cannot be determined? We say that it is _indeterminate_.

So we can find out how NULLs behave by seeing what our database does (or reading the SQL spec.) But how we _interpret_ NULL is context and application specific. It's completely valid to interpret NULL as "data-is-missing" or "does-not-exist" if you so wish. Maybe the more reasonable interpretation is "unknown" as the article urges.

However you have urged us to regard it (not the result of its operations which is another thing) as – at one and the same time – "the state does-not-exist", "zero", "irrelevant" which is unreasonable. Treating NULLs as unknowns and the results of operations on NULLs as indeterminate is a better recommendation I think even though ultimately the interpretation is application and context specific.

I like to think of it as 'outside the current set'. Saying it doesn't exist is like saying sqrt(-1) doesn't exist, yet it does, it's just 'outside' the set of real numbers.

In a strongly typed language with a boolean type, a comparison to null would require a type change. It's 'outside' the set of the boolean type.

> I like to think of it as 'outside the current set'.

Yes, if it exists (as an element of some set), then it is definitely outside the declared set of values. The main question is how we can formalize it, particularly, by defining the set NULL is a member of. For example, the following general approaches are possible:

o Extend (any or some) set of values with a special element (NULL)

o NULL = 0 (empty set)

o NULL = <> (empty tuple)

o Treat NULL as non-existence which entails having no-op if it is met, for example, 2 + NULL + 3 = 5 (just delete NULL which means skip it).

> but I choose to interpret the NULL is a less sexy way: Unknown means that the state DOES NOT EXIST

That's wrong.

Why is it wrong?

It represents something that is there that you don't know the value of. So when you use it as part of an expression even as a counterpart to a term that you know the value of, it "corrupts/interacts" so that the return value of the expression is unknown whereever the expression's return value is dependent on the value that we had chosen to represent with NULL.

This is different semantics from say, taking the AND with a term that is not present, which in most conventions would have us substitute the identity value (i.e. TRUE), so that the return value is TRUE if and only if the known value is TRUE; instead, we have that the return value in SQL is NULL.

Because it represents (and behaves) as unknown and not as absent.

Practical example is `1 not in (null)`

Just my 2c from SQL standards perspective:

> “Every [SQL] data type includes a special value, called the null value,” “that is used to indicate the absence of any data value”.

(http://modern-sql.com/concept/null)

For the Boolean type, null = unknown:

> Note that the truth value unknown is indistinguishable from the null for the Boolean type. Otherwise, the Boolean type would have four logical values.

(http://modern-sql.com/concept/three-valued-logic)

Although they are very closely related, I believe the general `null` value (for any data type) and three-valued logic (null for Boolean) should be separated (that's why I have two articles).

> Because it represents (and behaves) as unknown and not as absent.

It can represent both. For example, in a table of biographical data, a NULL value for DateOfDeath could represent N/A for someone still living, unknown for a dead person whose date of death is not known, and total uncertainty for a missing person who could be alive or dead.

I've seen this design in the wild quite often. It's most often attributed to the abuse of an intended original design. The original designer most likely intended the value populated in the "DateofDeath" column to imply death. The intention didn't account for someone down the system's evolution to imply "ALLOW NULL" to be interpreted as optional.

If your table is expected to answer "how many dead people whose date of death is unknown" then you'll most certainly also need an "IsDead" (true/false) NOT NULL column. (Best separate the table into "Death" with NOT NULL DateofDeath.)

Operational concerns might push you otherwise, but I think in terms of correctly representing the world what makes the most sense is a separate table of deaths with a primary key that's also a foreign key into the table of people, and which has a nullable date of death. That lets you represent all three of "they are living", "they are dead but we don't know when they died", and "they are dead and we know when they died".

I agree with the first part of your statement:

"Because it represents (and behaves) as unknown".

But I'm also inclined to think of the unknown BECAUSE of (or as a consequence of) absence of data. And most importantly, this absence of data (non-existence) is useful too.

I have a problem with the logic that intends to over fit absence/unknown to mean "it could be true or it could be false" ... or worse ... "it could be both".

If you are doing this in SQL WHERE predicates:

    (T-SQL)

    WHERE IsNull(IsHivPositive, 0) = 1

I would strongly argue to reimagine the use of that "boolean" column.

However you interpret a NULL (as "value existent but unknown", "value not existent", or "no information, i.e., value might exist or not"), you will have a hard time matching your interpretation with SQL's behaviour, no matter what the standard says. In fact, interpreting NULLs as "value existent but unknown" is the most troublesome of the three interpretations, both because of SQL's rules (for instance, select * from T where A=A is empty if A is NULL, but an existent value should be certainly equal to itself even if you don't know what it is) and due to the intrinsic computational complexity of related problems (basically, related to the intractability of recognising tautologies).

The closest to a sensible semantics that I have seen is a 2012's paper titled "On the Logic of SQL Nulls", which argues that the "correct" interpretation of NULL is "value not existent". It is a quite theoretical paper, though, and it covers only a subset of SQL.

If you want to use NULLs meaning "value exists but is unknown" I recommend that you the papers written by Hans-Joachim Klein, mostly in the '90s. His "switching semantics" provides some very interesting rules for writing queries with NULLs, which are at least guaranteed to output a subset of the correct answers wrt some reasonable definition of "correctness" (some of his results were rediscovered recently by Libkin btw).

Why would NULL equal itself? You're basically asking for SQL to special-case the Boolean logic so that if a null value is compared to itself it returns true. Who needs that?

If you assume that NULL is a placeholder for a value that exists, you must require the basic property that A=A be true, even when A is NULL, because any value equals itself. This does not mean that NULL=NULL should always be true. So, this is just to point out one of the many examples of SQL being incompatible with a "value existent, but unknown" semantics. NULLs in SQL are quite a mess.

Reply on news.ycombinator.com