5 ms·

> SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. Nah. One

> SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. Nah. One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is". This is a fundamental feature needed in data management. So say you've got data on two balls A and B. You don't know what the color of either ball is. Is color(A) = color(B)? What's the best answer to this question? Obviously it's "we don't know", and the value we use to communicate "we don't know" is null. SQL nulls aren't broken. They're just a third option outside of True/False that needs its own handling, because reality is complicated. PS: Contrary to popular opinion in certain coding circles, floating point arithmetic isn't broken either. It's a beautiful, delicate tool that works the way it does because it must in order to support its use cases (e.g. numerical linear algebra and representing continuous physical quantities). If it doesn't work exactly the way you want, you may need a couple guardrails or a different system like fixed point arithmetic.

wyager 5y ago
> One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is" The correct way to represent this, which has been widely used for several decades now, is sum types. If your value might be unknown, you need to explicitly represent this in the type of your value (e.g. Optional<Foo>) instead of relying on a hack that allows subverting the type system.
- civilized 5y ago
  I agree, and more sophisticated analytics data structures, like R's data frame, do have "typed nulls" (except in R they're called NAs). I don't know, and would love to know, why databases have been so slow to adopt this.
- wongarsu 5y ago
  You could argue any SQL value not marked as NOT NULL is an Optional<Foo>, with NULL being the None value. It's not really subverting the type system, it's just an unconventional choice.
  - Pxtl 5y ago
    Yes, but I can't think of any language where, given Optional<Foo> x and y, x.equals(y) would return null instead of either true, false, or an exception if y is null. That's what makes SQL null-values distinct - the fact that expressions that return booleans can return NULLs.
- ttfkam 5y ago
  You're approaching this at too low a level. SQL is a 4th generation language, quite possibly the only successful 4GL. Unlike every other major language, it doesn't deal with "how" you get the answer. You see the "how" with commands like EXPLAIN. Instead it focuses on "what" you want and letting the engine figure out how to get there. Optional<Foo> lends itself well to functional programming and prevents things like null pointer errors, but that's still at 3GL level, the EXPLAIN level. SQL doesn't throw null pointer exceptions. Ever. The query syntax is correct or it isn't. The same concerns in a programmatic language simply do not apply, which is why Optional<Foo> is a poor fit in this context. Null in SQL is not the null in JavaScript or C++. Same name, but not the same animal, and I think this is what trips folks up. In your mind, replace SQL's null with a new keyword, UNKNOWN, and it makes more logical sense. SELECT ... FROM ... WHERE x = UNKNOWN; Even if x were UNKNOWN in a row, is it the same unknown? For example, if you see two girls, but you don't know their birthdays. Their birthdays are unknown. WHERE Keasha.birthday = Cynthia.birthday This partial statement would always be false no matter what until you know both of their actual ages. Just because something is unknown doesn't mean it's the same unknown. Set theory, not a programmatic series of steps as found in most languages. And that's what SQL is: a DSL for set theory. I believe it's advantageous to avoid nullable columns in relational schema design, but that's a separate issue from the concept of NULL in SQL as distinct from programmatic languages.
  - matwood 5y ago
    > I believe it's advantageous to avoid nullable columns in relational schema design, but that's a separate issue from the concept of NULL in SQL as distinct from programmatic languages. Agreed, but they should be used where appropriate. My favorite example of a nullable column is an end date. Until an end date occurs, it's unknown - thus null. Representing the unknown end date with anything other than null is semantically incorrect, and in many cases can lead to errors.
  - wyager 5y ago
    Supporting sum types is not in any way incompatible with being a query or logic language.
- housecarpenter 5y ago
  In the relational model used by SQL, types are atomic. Sum types are inherently non-atomic. I'm sure you could graft them onto the language, the same way Postgres can have JSON type fields and all that, but I don't think you'd be able to really integrate them into SQL without fundamentally changing what kind of language SQL is.
  - wyager 5y ago
    The query algebra is completely compatible with sum types. I don't think "atomic" is describing anything central to the QA model.
Pxtl 5y ago
If 3-value logic is such a good idea, why doesn't any modern general-purpose language use it? And even if it were a good idea, leaving the user with no built-in operators, not even a simple COALESCE(bool) to properly manage the null complexity? That's unacceptable. Given a SQL boolean expression `f(b)` that can return true, false, or null, how do I test if `f(b)` has returned NULL? How do I handle that NULL case? ANSI SQL gives users a strange new paradigm for boolean algebra and then says "GLHF" and gives you zero tools to handle the situation, not even linguistic features that would let you roll your own boolean-based functions, since you can't write SVFs that take boolean parameters. I mean this trashboat of a language doesn't even let you bind boolean to a variable! You have to deal with the PITA of converting from Booleans to bits because those are totally different things.
- civilized 5y ago
  Because you can implement 3-valued logic using boolean logic in a general-purpose programming language if you need it, and most applications don't benefit from the extra nuance and complexity. But data management sure does, as I illustrated in my post with the color example. Notice that databases, with their 3-valued logic, are written in a systems language like C/C++, a general-purpose programming language with boolean logic. Some systems are best thought of using non-boolean logic. Quantum systems are another example. You can simulate just about anything with a Turing-complete language, but it doesn't mean that standard computation with boolean logic is the best way to think about every domain.
  - Pxtl 5y ago
    > Because you can implement 3-valued logic using boolean logic in a general-purpose programming language if you need it, and most applications don't benefit from the extra nuance and complexity. Surely if it were beneficial you'd see more people doing it, but I have literally never encountered software outside of SQL that does it. I could probably implement it in C++ or C# in an afternoon using enums and operator overloading, at least for the boolean-boolean operators. A little longer to make comparison operators for the other basic types that would yield nulls when compared to null. But nobody does this. Why?
aidos 5y ago
Yup. The semantics are great for joins. Two missing bits of data do not make a found.
- Pxtl 5y ago
  Then why doesn't it error out when faced with NULL? I mean, we hear over and over again when learning SQL "NULL bool and FALSE are different things"... But the SQL engine itself treats NULL and FALSE as equivalent all the time. DECLARE @x INT = NULL SELECT 'foo' WHERE @x = 1 If three-value logic were truly embraced, that should be an error, because we don't know the state of @x. But SQL talks out of both sides of its mouth about Booleans. It freely just assumes that null is false, which we, as developers, are never ever ever allowed to do. There isn't even an operator included in ANSI SQL to let us convert null boolean values into false, because for some reason bits and bools are two different things and you can't just say COALESCE(x = y, false) It's not just that three-value-logic is painful, it's that SQL makes working with them miserable because, at a linguistic level, it seems to politely forget that three-value-logic exists.
  - Taywee 5y ago
    Because then you couldn't do a join across non-mandatory foreign keys. Practically, making all NULL comparisons error out would make them almost entirely useless.
  - piaste 5y ago
    I don't know why OP described SQL NULLs as 'three-valued logic' because it's anything but. SQL boolean operators can only result true or false, there isn't a third option. NULL is a data value for all data types, which happens to return 'true' to the boolean operation 'is null', and 'false' to any other boolean operation. NULL = NULL or NULL = 1 don't return a third-option NULL, there is no such thing, they return a plain old 'false'. (NULL = NULL returns false because otherwise it would make outer joins terrible) As you pointed out, SQL does not implicitly convert boolean values (bits) to or from the result value of boolean operators. Bits are values and can be NULL. Boolean results are not values and can only be true or false. Now, is this optimal? Hell no. (Sum types would probably be the way to go if SQL were designed 50 years later.) But it's consistent.
Sankozi 5y ago
> Nah. One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is". This is a fundamental feature needed in data management. I rarely use nulls like that. And then only for things outside of SQL databases like lazy loading or caching. And even then I still prefer null == null to be true. If nulls are for what you have written then why we are calling them "null" - no value? Why not calling them "unknown". Even then what is benefit in introducing non boolean logic to a relational query language? I have never seen anyone using any of three-valued logic benefits when writing SQL. Every time I get unknown logical value it means I have made a mistake in my query, which would be better handled by rising an error.
- civilized 5y ago
  > I rarely use nulls like that. Well, what can I say? Other people need them, and I illustrated why. > And even then I still prefer null == null to be true. Some databases have alternate operators, like <=>, that make null == null true. And you can always coalesce null to a sentinel value if your dialect doesn't have this operator. Heck, if you're writing SQL using templates, you could have the template language replace a <=> b with coalesce(a, 99999999) = coalesce(b, 99999999) (or whatever other sentinel value you want to use).
FedericoRazzoli 5y ago
The problem, though, is that SQL standard treats NULL as unknown or as missing. One word for two meanings. For example, AVG(col) and COUNT(col) treat it as a missing value (NULLs do not alter the average or the count), but 1 + NULL treats it as an unknown value (the result is unknown). The complete list of NULL-related inconsistencies would be long. It would have been better to use different words for different semantics, or not implementing NULL at all. Also, in some cases it can lead to poor query performance.
- civilized 5y ago
  I emphatically reject the idea that having an imperfect null is worse than no null at all, but I understand frustrated programmers may have different beliefs.

wyager 5y ago

> One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is" The correct way to represent this, which has been widely used for several decades now, is sum types. If your value might be unknown, you need to explicitly represent this in the type of your value (e.g. Optional<Foo>) instead of relying on a hack that allows subverting the type system.

civilized 5y ago
I agree, and more sophisticated analytics data structures, like R's data frame, do have "typed nulls" (except in R they're called NAs). I don't know, and would love to know, why databases have been so slow to adopt this.
wongarsu 5y ago
You could argue any SQL value not marked as NOT NULL is an Optional<Foo>, with NULL being the None value. It's not really subverting the type system, it's just an unconventional choice.
- Pxtl 5y ago
  Yes, but I can't think of any language where, given Optional<Foo> x and y, x.equals(y) would return null instead of either true, false, or an exception if y is null. That's what makes SQL null-values distinct - the fact that expressions that return booleans can return NULLs.
ttfkam 5y ago
You're approaching this at too low a level. SQL is a 4th generation language, quite possibly the only successful 4GL. Unlike every other major language, it doesn't deal with "how" you get the answer. You see the "how" with commands like EXPLAIN. Instead it focuses on "what" you want and letting the engine figure out how to get there. Optional<Foo> lends itself well to functional programming and prevents things like null pointer errors, but that's still at 3GL level, the EXPLAIN level. SQL doesn't throw null pointer exceptions. Ever. The query syntax is correct or it isn't. The same concerns in a programmatic language simply do not apply, which is why Optional<Foo> is a poor fit in this context. Null in SQL is not the null in JavaScript or C++. Same name, but not the same animal, and I think this is what trips folks up. In your mind, replace SQL's null with a new keyword, UNKNOWN, and it makes more logical sense. SELECT ... FROM ... WHERE x = UNKNOWN; Even if x were UNKNOWN in a row, is it the same unknown? For example, if you see two girls, but you don't know their birthdays. Their birthdays are unknown. WHERE Keasha.birthday = Cynthia.birthday This partial statement would always be false no matter what until you know both of their actual ages. Just because something is unknown doesn't mean it's the same unknown. Set theory, not a programmatic series of steps as found in most languages. And that's what SQL is: a DSL for set theory. I believe it's advantageous to avoid nullable columns in relational schema design, but that's a separate issue from the concept of NULL in SQL as distinct from programmatic languages.
- matwood 5y ago
  > I believe it's advantageous to avoid nullable columns in relational schema design, but that's a separate issue from the concept of NULL in SQL as distinct from programmatic languages. Agreed, but they should be used where appropriate. My favorite example of a nullable column is an end date. Until an end date occurs, it's unknown - thus null. Representing the unknown end date with anything other than null is semantically incorrect, and in many cases can lead to errors.
- wyager 5y ago
  Supporting sum types is not in any way incompatible with being a query or logic language.
housecarpenter 5y ago
In the relational model used by SQL, types are atomic. Sum types are inherently non-atomic. I'm sure you could graft them onto the language, the same way Postgres can have JSON type fields and all that, but I don't think you'd be able to really integrate them into SQL without fundamentally changing what kind of language SQL is.
- wyager 5y ago
  The query algebra is completely compatible with sum types. I don't think "atomic" is describing anything central to the QA model.

Pxtl 5y ago

If 3-value logic is such a good idea, why doesn't any modern general-purpose language use it? And even if it were a good idea, leaving the user with no built-in operators, not even a simple COALESCE(bool) to properly manage the null complexity? That's unacceptable. Given a SQL boolean expression `f(b)` that can return true, false, or null, how do I test if `f(b)` has returned NULL? How do I handle that NULL case? ANSI SQL gives users a strange new paradigm for boolean algebra and then says "GLHF" and gives you zero tools to handle the situation, not even linguistic features that would let you roll your own boolean-based functions, since you can't write SVFs that take boolean parameters. I mean this trashboat of a language doesn't even let you bind boolean to a variable! You have to deal with the PITA of converting from Booleans to bits because those are totally different things.

civilized 5y ago
Because you can implement 3-valued logic using boolean logic in a general-purpose programming language if you need it, and most applications don't benefit from the extra nuance and complexity. But data management sure does, as I illustrated in my post with the color example. Notice that databases, with their 3-valued logic, are written in a systems language like C/C++, a general-purpose programming language with boolean logic. Some systems are best thought of using non-boolean logic. Quantum systems are another example. You can simulate just about anything with a Turing-complete language, but it doesn't mean that standard computation with boolean logic is the best way to think about every domain.
- Pxtl 5y ago
  > Because you can implement 3-valued logic using boolean logic in a general-purpose programming language if you need it, and most applications don't benefit from the extra nuance and complexity. Surely if it were beneficial you'd see more people doing it, but I have literally never encountered software outside of SQL that does it. I could probably implement it in C++ or C# in an afternoon using enums and operator overloading, at least for the boolean-boolean operators. A little longer to make comparison operators for the other basic types that would yield nulls when compared to null. But nobody does this. Why?

aidos 5y ago

Yup. The semantics are great for joins. Two missing bits of data do not make a found.

Pxtl 5y ago
Then why doesn't it error out when faced with NULL? I mean, we hear over and over again when learning SQL "NULL bool and FALSE are different things"... But the SQL engine itself treats NULL and FALSE as equivalent all the time. DECLARE @x INT = NULL SELECT 'foo' WHERE @x = 1 If three-value logic were truly embraced, that should be an error, because we don't know the state of @x. But SQL talks out of both sides of its mouth about Booleans. It freely just assumes that null is false, which we, as developers, are never ever ever allowed to do. There isn't even an operator included in ANSI SQL to let us convert null boolean values into false, because for some reason bits and bools are two different things and you can't just say COALESCE(x = y, false) It's not just that three-value-logic is painful, it's that SQL makes working with them miserable because, at a linguistic level, it seems to politely forget that three-value-logic exists.
- Taywee 5y ago
  Because then you couldn't do a join across non-mandatory foreign keys. Practically, making all NULL comparisons error out would make them almost entirely useless.
- piaste 5y ago
  I don't know why OP described SQL NULLs as 'three-valued logic' because it's anything but. SQL boolean operators can only result true or false, there isn't a third option. NULL is a data value for all data types, which happens to return 'true' to the boolean operation 'is null', and 'false' to any other boolean operation. NULL = NULL or NULL = 1 don't return a third-option NULL, there is no such thing, they return a plain old 'false'. (NULL = NULL returns false because otherwise it would make outer joins terrible) As you pointed out, SQL does not implicitly convert boolean values (bits) to or from the result value of boolean operators. Bits are values and can be NULL. Boolean results are not values and can only be true or false. Now, is this optimal? Hell no. (Sum types would probably be the way to go if SQL were designed 50 years later.) But it's consistent.

Sankozi 5y ago

> Nah. One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is". This is a fundamental feature needed in data management. I rarely use nulls like that. And then only for things outside of SQL databases like lazy loading or caching. And even then I still prefer null == null to be true. If nulls are for what you have written then why we are calling them "null" - no value? Why not calling them "unknown". Even then what is benefit in introducing non boolean logic to a relational query language? I have never seen anyone using any of three-valued logic benefits when writing SQL. Every time I get unknown logical value it means I have made a mistake in my query, which would be better handled by rising an error.

civilized 5y ago
> I rarely use nulls like that. Well, what can I say? Other people need them, and I illustrated why. > And even then I still prefer null == null to be true. Some databases have alternate operators, like <=>, that make null == null true. And you can always coalesce null to a sentinel value if your dialect doesn't have this operator. Heck, if you're writing SQL using templates, you could have the template language replace a <=> b with coalesce(a, 99999999) = coalesce(b, 99999999) (or whatever other sentinel value you want to use).

FedericoRazzoli 5y ago

The problem, though, is that SQL standard treats NULL as unknown or as missing. One word for two meanings. For example, AVG(col) and COUNT(col) treat it as a missing value (NULLs do not alter the average or the count), but 1 + NULL treats it as an unknown value (the result is unknown). The complete list of NULL-related inconsistencies would be long. It would have been better to use different words for different semantics, or not implementing NULL at all. Also, in some cases it can lead to poor query performance.

civilized 5y ago
I emphatically reject the idea that having an imperfect null is worse than no null at all, but I understand frustrated programmers may have different beliefs.