4 ms·

> In logic, this is called the principle of bivalence: there are only two values that a proposition can have, true or false. But Python has None, many other pro

> In logic, this is called the principle of bivalence: there are only two values that a proposition can have, true or false. But Python has None, many other programming languages have null or nil, and Zen has mu. Maybe adding another value would help? Hoare labeled his invention of null a "billion dollar mistake". SQL NULL can't be far behind. Boolean logic has 4 unary and 16 binary operators, ternary logic has 27 and 19683, respectively. That's not helpful, that's intractable! I cannot recommend C. J. Date's books enough.

jewel 3y ago
> SQL NULL can't be far behind. SQL's NULL is far more useful though, since NULL != NULL. That lets you do things like join two tables with a column and not have the NULLs match. Consider, for example: select * from users where id = null; That will give 0 results, even if somehow you have a user with a null id. It's also really nice for non-ID, non-reference fields to represent N/A. For example, user.birthdate can be NULL if the birthdate is not known. SQL also lets you choose when you want NULL to be allowed for a column, so it's less likely to be a gotcha.
- tomp 3y ago
  For anyone wondering how to fix the above query so that it works for arbitrary X (which can be NULL or non-NULL): SELECT * FROM users WHERE status IS NOT DISTINCT FROM $x
- neuromanser 3y ago
  > SQL's NULL is far more useful though, since NULL != NULL. That's not true! (Nor is it false.) NULL = NULL IS NULL, NULL != NULL IS NULL. I can't overstate this: as soon as either (or both) operand of any binary relational operator is NULL, the whole expression is NULL! I'm not being an asshole, this is the exact mistake that makes SQL NULL the other billion dollar sink! This is really funny, because i wasn't aware of laying such a beatiful trap.
  - jerf 3y ago
    SQL NULL has to be the quintessential example of why programming can't just blindly follow math. The NULL truth table makes all the sense in the world in a mathematical context.. and it is a total pain to program with. Letting NULL == NULL be "true" and NULL == anything else be "false" might be slightly less principled, but it would be soooo much safer and have prevent so very many bugs. (And it's not like that's some sort of mathematical nonsense either. It makes perfect sense to not create a special value that represent "outside my universe of values" and then refuse to equate any two of them. It makes perfect sense to declare a distinguished "outside my universe of values" and let it be equal to any other such value on the grounds that it is mathematically impossible to witness any differences between them anyhow so within the system they are indeed equal.)
    - maxbond 3y ago
      Let's say we're in a situation like this: create table people ( name text primary key, age integer ); insert into people (name, age) values ('Bob', 50), ('Alice', null), ('Mallory', null); We know Bob is 50, but we don't know the age of Alice or Mallory. Are Bob and Alice the same age? We don't know. Are Alice and Mallory the same age? We don't know. If `null` were equal to `null,` we couldn't express missing information like this. Naive queries would still have bugs and conclude that Alice and Mallory were the same age. Null is a property of living in an uncertain universe. When you start taking measurements of the real world (which every production business process is), you run into the many faces of null. For example, I used to take data in a lab. Often we'd measure three replica samples. Sometimes I'd spill one of them. Scientific ethics demand I still write down the two measurements I did collect, even if I go get three more samples. For the third, I'd write the pen and paper equivalent of null (striking out that cell in the table). Were any two of those missing measurements equal? Does a dog have Buddha nature?
      - vlovich123 3y ago
        I think the mistake here was the name chosen. The NaN concept from floating point has an identical definition and behavior, so calling it something like NaV (not a value) or undefined (similar but not identical behavior in JS) instead might have caused less confusion vs NULL typically having a definition of an implicit Option<T>
- neuromanser 3y ago
  > Consider, for example: > select * from users where id = null; > That will give 0 results, even if somehow you have a user with a null id. Why is it useful? Can you elaborate?
  - maxbond 3y ago
    Not GP, but let's say that null in the query wasn't a literal but was the result of another query that returned null. Let's say that the query returned null because not user existed who met the criteria specified by the query, but because we were doing a complex join or aggregation or something, we didn't simply get back 0 rows. In that case it would be a bug if we matched with this errant user who's id is null.
Smaug123 3y ago
The mistake of `null` is _not_ that it exists at all - it's often the case that you can't answer a question, and it's extremely useful to be able to model your inability to answer a question! The mistake of `null` is instead that it forces every reference type to be an optional. This is a mistake because some things can in fact be known. In practice, when everything is optional, it's so boilerplate-heavy to enforce the condition "this is known" at runtime for every property that everyone inevitably ends up not enforcing that condition most of the time; the bugs arise when one side of a boundary assumes for expediency that the other side has enforced it. It is in general very reasonable for a function to return an optional bool; think of `List.tryHead [false, true, false]`. What is unreasonable is for all boolean functions to return optional bools.

jewel 3y ago

> SQL NULL can't be far behind. SQL's NULL is far more useful though, since NULL != NULL. That lets you do things like join two tables with a column and not have the NULLs match. Consider, for example: select * from users where id = null; That will give 0 results, even if somehow you have a user with a null id. It's also really nice for non-ID, non-reference fields to represent N/A. For example, user.birthdate can be NULL if the birthdate is not known. SQL also lets you choose when you want NULL to be allowed for a column, so it's less likely to be a gotcha.

tomp 3y ago
For anyone wondering how to fix the above query so that it works for arbitrary X (which can be NULL or non-NULL): SELECT * FROM users WHERE status IS NOT DISTINCT FROM $x
neuromanser 3y ago
> SQL's NULL is far more useful though, since NULL != NULL. That's not true! (Nor is it false.) NULL = NULL IS NULL, NULL != NULL IS NULL. I can't overstate this: as soon as either (or both) operand of any binary relational operator is NULL, the whole expression is NULL! I'm not being an asshole, this is the exact mistake that makes SQL NULL the other billion dollar sink! This is really funny, because i wasn't aware of laying such a beatiful trap.
- jerf 3y ago
  SQL NULL has to be the quintessential example of why programming can't just blindly follow math. The NULL truth table makes all the sense in the world in a mathematical context.. and it is a total pain to program with. Letting NULL == NULL be "true" and NULL == anything else be "false" might be slightly less principled, but it would be soooo much safer and have prevent so very many bugs. (And it's not like that's some sort of mathematical nonsense either. It makes perfect sense to not create a special value that represent "outside my universe of values" and then refuse to equate any two of them. It makes perfect sense to declare a distinguished "outside my universe of values" and let it be equal to any other such value on the grounds that it is mathematically impossible to witness any differences between them anyhow so within the system they are indeed equal.)
  - maxbond 3y ago
    Let's say we're in a situation like this: create table people ( name text primary key, age integer ); insert into people (name, age) values ('Bob', 50), ('Alice', null), ('Mallory', null); We know Bob is 50, but we don't know the age of Alice or Mallory. Are Bob and Alice the same age? We don't know. Are Alice and Mallory the same age? We don't know. If `null` were equal to `null,` we couldn't express missing information like this. Naive queries would still have bugs and conclude that Alice and Mallory were the same age. Null is a property of living in an uncertain universe. When you start taking measurements of the real world (which every production business process is), you run into the many faces of null. For example, I used to take data in a lab. Often we'd measure three replica samples. Sometimes I'd spill one of them. Scientific ethics demand I still write down the two measurements I did collect, even if I go get three more samples. For the third, I'd write the pen and paper equivalent of null (striking out that cell in the table). Were any two of those missing measurements equal? Does a dog have Buddha nature?
    - vlovich123 3y ago
      I think the mistake here was the name chosen. The NaN concept from floating point has an identical definition and behavior, so calling it something like NaV (not a value) or undefined (similar but not identical behavior in JS) instead might have caused less confusion vs NULL typically having a definition of an implicit Option<T>
neuromanser 3y ago
> Consider, for example: > select * from users where id = null; > That will give 0 results, even if somehow you have a user with a null id. Why is it useful? Can you elaborate?
- maxbond 3y ago
  Not GP, but let's say that null in the query wasn't a literal but was the result of another query that returned null. Let's say that the query returned null because not user existed who met the criteria specified by the query, but because we were doing a complex join or aggregation or something, we didn't simply get back 0 rows. In that case it would be a bug if we matched with this errant user who's id is null.

Smaug123 3y ago

The mistake of `null` is _not_ that it exists at all - it's often the case that you can't answer a question, and it's extremely useful to be able to model your inability to answer a question! The mistake of `null` is instead that it forces every reference type to be an optional. This is a mistake because some things can in fact be known. In practice, when everything is optional, it's so boilerplate-heavy to enforce the condition "this is known" at runtime for every property that everyone inevitably ends up not enforcing that condition most of the time; the bugs arise when one side of a boundary assumes for expediency that the other side has enforced it. It is in general very reasonable for a function to return an optional bool; think of `List.tryHead [false, true, false]`. What is unreasonable is for all boolean functions to return optional bools.