3 ms·

Why is it wrong?

by ak39 9y ago

Why is it wrong?

the_mitsuhiko 9y ago
Because it represents (and behaves) as unknown and not as absent. Practical example is `1 not in (null)`
- MarkusWinand 9y ago
  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 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 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).
- dghf 9y ago
  > 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.
  - ak39 9y ago
    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.)
    - dllthomas 9y ago
      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".
- ak39 9y ago
  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.
  - lifepillar 9y ago
    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).
    - cwyers 9y ago
      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?
jhanschoo 9y ago
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.

Why is it wrong?

by ak39 9y ago

Why is it wrong?

the_mitsuhiko 9y ago

Because it represents (and behaves) as unknown and not as absent. Practical example is `1 not in (null)`

MarkusWinand 9y ago
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 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 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).
dghf 9y ago
> 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.
- ak39 9y ago
  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.)
  - dllthomas 9y ago
    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".
ak39 9y ago
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.
- lifepillar 9y ago
  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).
  - cwyers 9y ago
    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?

jhanschoo 9y ago

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.