9 ms·

A great one to pile on here is null. Null is quite the unique thing within SQL, this post from some years ago highlights a lot of the unintuitive behavior aroun

A great one to pile on here is null. Null is quite the unique thing within SQL, this post from some years ago highlights a lot of the unintuitive behavior around it within Postgres - http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-with-nulls/ http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-wi...

Pxtl 7y ago
Yes this. I always see people raving about the mathematical beauty of three-value Boolean algebra, but any programming language where testing if foo = foo can return something mechanically equivalent to false is just plain broken. Yes, I get that it's pure and mathematically beautiful, but over here in the real world I've squished an endless supply of bugs caused by this absurdity, and in languages where there is no null-aware equality test, the composing the proper check is absurdly verbose.
- thedufer 7y ago
  > any programming language where testing if foo = foo can return something mechanically equivalent to false is just plain broken Isn't this pretty much any language? You're describing how IEEE float NaNs work. I'm not aware of many languages that don't have IEEE-compliant floats.
  - lmm 7y ago
    IEEE floats with non-signalling NaNs make some very aggressive tradeoffs (in the name of performance) that were maybe a reasonable default in 1985 but are certainly not so today. General-purpose programming languages should not be offering them unless very explicitly opted in to.
    - thedufer 7y ago
      Can you actually name a mainstream language that doesn't have NaNs with that behavior? Python, Ruby, Go, Rust, Java, JavaScript, etc all do. Or maybe what you're saying is really just that they shouldn't have them, and every modern language has gotten this wrong?
      - lmm 7y ago
        > Can you actually name a mainstream language that doesn't have NaNs with that behavior? Python, Ruby, Go, Rust, Java, JavaScript, etc all do. Rust doesn't have that behaviour: Rust floating-point types simply don't have Eq implementations (they have PartialEq only), passing them somewhere where you need an equality-comparable type is a compilation error. OCaml is the only other example I'm aware of (it treats nan as a normal value with a well-behaved place in the order). > Or maybe what you're saying is really just that they shouldn't have them, and every modern language has gotten this wrong? Yes. Similar to how modern high-level languages are starting to default to arbitrary-precision integers rather than fixed-size integers (e.g. Python), we should be defaulting to something like Python's decimal or Java's BigDecimal, and have IEEE floating-point as an opt-in case for when you need it.
        thedufer 7y ago
        > Rust floating-point types simply don't have Eq implementations (they have PartialEq only), passing them somewhere where you need an equality-comparable type is a compilation error. While this is true, you can still say `NaN == NaN` and get false. I claim that that's still the behavior I was describing. > OCaml is the only other example I'm aware of (it treats nan as a normal value with a well-behaved place in the order). Again, kind of true - for physical and structural equality it does a bitwise-comparison. However, most people recommend against those equality operators because they are misleading in many cases, and the float-specific equality operator does what I described. In what ways are bitwise-comparison wrong? For structured data (say, binary-tree backed maps) this is clearly bad because there are potentially multiple representations that we would consider equivalent. But even for floats it is bad - `0 == -0` should be true, but will not be. Further, two different NaNs will be compare as false even though two of the same NaN will compare as true (you're not supposed to be able to distinguish between different representations of NaN).
        lmm 7y ago
        > While this is true, you can still say `NaN == NaN` and get false. I claim that that's still the behavior I was describing. Point taken; I do think it would be better for the method on PartialEq to not be called "==". But Rust does offer a standard distinction between well-behaved equality and not, and floats are not considered to implement well-behaved equality, which comes close to what I'm asking for. Like, if JavaScript had `NaN === NaN` then I'd consider that morally a counterexample to what you're talking about, because === is (in a sense) the standard equality operator in JavaScript; in the same way, I'd consider Eq::== to be the standard equality operator in Rust. > Again, kind of true - for physical and structural equality it does a bitwise-comparison. However, most people recommend against those equality operators because they are misleading in many cases, and the float-specific equality operator does what I described. I think that's the right way to do it. Generic code using the generic == expects it to have the standard properties - in particular x == x for any x. Code that uses a float-specific operator can be prepared for float-specific behaviour. > In what ways are bitwise-comparison wrong? For structured data (say, binary-tree backed maps) this is clearly bad because there are potentially multiple representations that we would consider equivalent. But even for floats it is bad - `0 == -0` should be true, but will not be. Further, two different NaNs will be compare as false even though two of the same NaN will compare as true (you're not supposed to be able to distinguish between different representations of NaN). This is true as far as it goes. Equally, having 0 == -0 is bad (it breaks substitutability, since 1 / 0.0 != 1 / -0.0), and having x != x is super bad. Really there is just no good general-purpose comparison for IEEE 754 floats, and so they should either not implement generic == at all (in languages where that's possible), be avoided by default and hidden away in an "unsafe" area of the standard library, or both.
- adamzochowski 7y ago
  Sigh, In all other programming languages the code has to be along the lines of: if ((jane.car.colour == james.car.colour) and (jane.car.colour != null ) and ( null != james.car.colour)): print ("James and Jane have same car color") or attempted with a second variable to indicate if value is known if (( jane.car.colourKnown == True ) and (james.car.colourKnown == True ) and ( jane.car.colour == james.car.colour)): print ("James and Jane have same car colour") I like to think that SQL nulls are the most human way. I think of a scenario: James and Jane each own a car. I don't know neither car colour. Does it mean they are of same colour? All typical programming languages say "Yes". SQL says "I can't tell you, I don't know", and that is what a human would say.
  - stouset 7y ago
    Your brain is going to explode like mine did when you learn about Option types[1]. [1] https://en.m.wikipedia.org/wiki/Option_type https://en.m.wikipedia.org/wiki/Option_type
    - ericpauley 7y ago
      This doesn't really solve GP's problem, as the Nothing type is generally still equal to itself. Certainly useful for general purpose programming languages, though.
      - Smaug123 7y ago
        The problem is really that you're using the wrong equality type here. You get the right semantics if you use `= : A -> A -> Bool` and then apply it to a `Maybe A` and a `Maybe A` by lifting it: you end up with `Nothing` if either input was `Nothing`, and `Just answer` if both were known.
  - vimslayer 7y ago
    For what it's worth, in your "all other programming languages" example you don't need the second null check null != james.car.colour It's redundant when we know the first two are true.
- da_chicken 7y ago
  > composing the proper check is absurdly verbose. You're kidding, right? Instead of WHERE Field = 1 You have to specify WHERE Field = 1 OR Field IS NULL You might even have to add parenthesis! It's so verbose it puts Java to shame! Yes, I understand that real queries are much longer, but that's not the language's fault. That's the complexity of your data coming through. NULL doesn't behave the way it does out of some need for mathematical purity. Understanding the model just tells you when you know you need to handle your NULLs. The system behaves the way it does because the system doesn't know what you want to do when a value doesn't exist or is unknown. Logically, there is no answer. That's what you're complaining about here. It's like asking the system to add two numbers and only giving it one of them. What does a NULL datetime mean? The value wasn't recorded? It wasn't observed? It hasn't happened yet? That field doesn't make sense to record with the other data on the record (i.e., you denormalized your data)? That the value is actually known to be unknown? Or some combination of those? How is the system supposed to know? When you're telling the system what to do with NULL values, you're telling the system what NULL means for that query. That's all you're doing. You're just telling the system what your data actually means in that query. That's not hard. Complaining that the system that stores structured data actually requires you to understand what your data represent and how your data are being used is... strange.
  - stouset 7y ago
    That’s all great but it’s not like we haven’t figured out how to solve this in other languages with `Option` or `Maybe` types. Obviously SQL predates this, but that shouldn’t stop us from lamenting the fact that SQL repeated the Billion Dollar Mistake and discussing a future alternative that’s learned from this and doesn’t feature such a sharp edge.
    - wiml 7y ago
      Sum types like Option/Maybe (or C's NULL) don't do the same thing as SQL's NULL, though. By all means, if you don't need NULLs, then don't use them — `NOT NULL` should probably have been the default in table statements — but they serve a purpose.
  - 7y ago
- hathawsh 7y ago
  The fact that null != null (and NaN != NaN) is, to me, a helpful reminder that the mathematical model behind SQL is not as simple and elegant as newcomers would like it to be. It reminds me of the classic "proof" that 1 = 2, based on simple algebra: https://www.math.toronto.edu/mathnet/falseProofs/first1eq2.html https://www.math.toronto.edu/mathnet/falseProofs/first1eq2.h... If even basic algebra has a trap like that, then all of us need to be prepared for similar traps.
  - shezi 7y ago
    There is not a trap in algebra that is shown in that proof: there is a mistake in that proof. They do something that is not allowed, even if it's not obvious what that was at first glance. I don't think one can compare mistakes with thoughtful and powerful modelling.
    - hathawsh 7y ago
      By trap I mean a surprising exception to the common rules. Most high school graduates learn algebra, yet they rarely learn how to avoid accidental division by zero. It's a surprising exception. "null != null" is also a surprising exception to the common rules. I honestly wouldn't expect most developers to know it until their code breaks because of it. That doesn't mean the surprising rule is wrong, it just means people have to learn it. Once they learn it, they may try to find the deeper model where the rule is no longer a surprise but rather an important feature.
sjwright 7y ago
Interesting. The SQL concept of null always made perfect sense to me. Two people with the same middle name would have equality with respect to the string value of middleName. The same applies with two people known to have no middle name—where you store a zero length string. Two people who didn’t supply a middle name should not have equality with respect to middleName. Hence null. This acknowledged lack of data cannot be equal to a different lack of data.
- progval 7y ago
  > The same applies with two people known to have no middle name—where you store a zero length string. I think that's the thing. People use NULL to mean empty value, instead of unknown value. Not necessarily for strings because there's the empty string, but for numeric fields, including foreign keys
  - sjwright 7y ago
    Admittedly non-string fields do make me wish for two kinds of NULL so I can differentiate between zero, confirmed-nothing and unknown. But it's never been an issue for me in practice.
- rmrfchik 7y ago
  Except, Oracle treats zero length string as null.
  - progre 7y ago
    That's just stupid
  - sjwright 7y ago
    Seriously? That's the sort of thing I'd expect to find in MySQL 3-point-something, not Oracle.
    - Sharlin 7y ago
      Yes. It's horrible. But presumably it's a decades-old "feature" retained for backward compatibility…
necovek 7y ago
Since we are in the context of Postgresql, there is 'IS (NOT) DISTINCT FROM' for those who believe NULL's behaviour is reversed in (in)equality comparisons.
EmielMols 7y ago
It's fairly nice but indeed rather implicit. I would kill for a postgres setting where feeding (an evaluated) NULL to the where clause logs a warning. This would force developers to coalesce (at least on top level) and think about the null case when applicable.
scandox 7y ago
Yeah the big kick in the pants I got this year was using nullable columns in composite keys in MySQL and finding out that when their value is null, the result key is considered unique, because NULL is literally unknowable. So: col A, Col B null, 1 null, 1 Is not constrained, but: col A, Col B 1, 1 1, 1 <--- NOT ALLOWED I guess this is one of those things that is completely obvious to those that know it.

Pxtl 7y ago

Yes this. I always see people raving about the mathematical beauty of three-value Boolean algebra, but any programming language where testing if foo = foo can return something mechanically equivalent to false is just plain broken. Yes, I get that it's pure and mathematically beautiful, but over here in the real world I've squished an endless supply of bugs caused by this absurdity, and in languages where there is no null-aware equality test, the composing the proper check is absurdly verbose.

thedufer 7y ago
> any programming language where testing if foo = foo can return something mechanically equivalent to false is just plain broken Isn't this pretty much any language? You're describing how IEEE float NaNs work. I'm not aware of many languages that don't have IEEE-compliant floats.
- lmm 7y ago
  IEEE floats with non-signalling NaNs make some very aggressive tradeoffs (in the name of performance) that were maybe a reasonable default in 1985 but are certainly not so today. General-purpose programming languages should not be offering them unless very explicitly opted in to.
  - thedufer 7y ago
    Can you actually name a mainstream language that doesn't have NaNs with that behavior? Python, Ruby, Go, Rust, Java, JavaScript, etc all do. Or maybe what you're saying is really just that they shouldn't have them, and every modern language has gotten this wrong?
    - lmm 7y ago
      > Can you actually name a mainstream language that doesn't have NaNs with that behavior? Python, Ruby, Go, Rust, Java, JavaScript, etc all do. Rust doesn't have that behaviour: Rust floating-point types simply don't have Eq implementations (they have PartialEq only), passing them somewhere where you need an equality-comparable type is a compilation error. OCaml is the only other example I'm aware of (it treats nan as a normal value with a well-behaved place in the order). > Or maybe what you're saying is really just that they shouldn't have them, and every modern language has gotten this wrong? Yes. Similar to how modern high-level languages are starting to default to arbitrary-precision integers rather than fixed-size integers (e.g. Python), we should be defaulting to something like Python's decimal or Java's BigDecimal, and have IEEE floating-point as an opt-in case for when you need it.
      - thedufer 7y ago
        > Rust floating-point types simply don't have Eq implementations (they have PartialEq only), passing them somewhere where you need an equality-comparable type is a compilation error. While this is true, you can still say `NaN == NaN` and get false. I claim that that's still the behavior I was describing. > OCaml is the only other example I'm aware of (it treats nan as a normal value with a well-behaved place in the order). Again, kind of true - for physical and structural equality it does a bitwise-comparison. However, most people recommend against those equality operators because they are misleading in many cases, and the float-specific equality operator does what I described. In what ways are bitwise-comparison wrong? For structured data (say, binary-tree backed maps) this is clearly bad because there are potentially multiple representations that we would consider equivalent. But even for floats it is bad - `0 == -0` should be true, but will not be. Further, two different NaNs will be compare as false even though two of the same NaN will compare as true (you're not supposed to be able to distinguish between different representations of NaN).
        lmm 7y ago
        > While this is true, you can still say `NaN == NaN` and get false. I claim that that's still the behavior I was describing. Point taken; I do think it would be better for the method on PartialEq to not be called "==". But Rust does offer a standard distinction between well-behaved equality and not, and floats are not considered to implement well-behaved equality, which comes close to what I'm asking for. Like, if JavaScript had `NaN === NaN` then I'd consider that morally a counterexample to what you're talking about, because === is (in a sense) the standard equality operator in JavaScript; in the same way, I'd consider Eq::== to be the standard equality operator in Rust. > Again, kind of true - for physical and structural equality it does a bitwise-comparison. However, most people recommend against those equality operators because they are misleading in many cases, and the float-specific equality operator does what I described. I think that's the right way to do it. Generic code using the generic == expects it to have the standard properties - in particular x == x for any x. Code that uses a float-specific operator can be prepared for float-specific behaviour. > In what ways are bitwise-comparison wrong? For structured data (say, binary-tree backed maps) this is clearly bad because there are potentially multiple representations that we would consider equivalent. But even for floats it is bad - `0 == -0` should be true, but will not be. Further, two different NaNs will be compare as false even though two of the same NaN will compare as true (you're not supposed to be able to distinguish between different representations of NaN). This is true as far as it goes. Equally, having 0 == -0 is bad (it breaks substitutability, since 1 / 0.0 != 1 / -0.0), and having x != x is super bad. Really there is just no good general-purpose comparison for IEEE 754 floats, and so they should either not implement generic == at all (in languages where that's possible), be avoided by default and hidden away in an "unsafe" area of the standard library, or both.
adamzochowski 7y ago
Sigh, In all other programming languages the code has to be along the lines of: if ((jane.car.colour == james.car.colour) and (jane.car.colour != null ) and ( null != james.car.colour)): print ("James and Jane have same car color") or attempted with a second variable to indicate if value is known if (( jane.car.colourKnown == True ) and (james.car.colourKnown == True ) and ( jane.car.colour == james.car.colour)): print ("James and Jane have same car colour") I like to think that SQL nulls are the most human way. I think of a scenario: James and Jane each own a car. I don't know neither car colour. Does it mean they are of same colour? All typical programming languages say "Yes". SQL says "I can't tell you, I don't know", and that is what a human would say.
- stouset 7y ago
  Your brain is going to explode like mine did when you learn about Option types[1]. [1] https://en.m.wikipedia.org/wiki/Option_type https://en.m.wikipedia.org/wiki/Option_type
  - ericpauley 7y ago
    This doesn't really solve GP's problem, as the Nothing type is generally still equal to itself. Certainly useful for general purpose programming languages, though.
    - Smaug123 7y ago
      The problem is really that you're using the wrong equality type here. You get the right semantics if you use `= : A -> A -> Bool` and then apply it to a `Maybe A` and a `Maybe A` by lifting it: you end up with `Nothing` if either input was `Nothing`, and `Just answer` if both were known.
- vimslayer 7y ago
  For what it's worth, in your "all other programming languages" example you don't need the second null check null != james.car.colour It's redundant when we know the first two are true.
da_chicken 7y ago
> composing the proper check is absurdly verbose. You're kidding, right? Instead of WHERE Field = 1 You have to specify WHERE Field = 1 OR Field IS NULL You might even have to add parenthesis! It's so verbose it puts Java to shame! Yes, I understand that real queries are much longer, but that's not the language's fault. That's the complexity of your data coming through. NULL doesn't behave the way it does out of some need for mathematical purity. Understanding the model just tells you when you know you need to handle your NULLs. The system behaves the way it does because the system doesn't know what you want to do when a value doesn't exist or is unknown. Logically, there is no answer. That's what you're complaining about here. It's like asking the system to add two numbers and only giving it one of them. What does a NULL datetime mean? The value wasn't recorded? It wasn't observed? It hasn't happened yet? That field doesn't make sense to record with the other data on the record (i.e., you denormalized your data)? That the value is actually known to be unknown? Or some combination of those? How is the system supposed to know? When you're telling the system what to do with NULL values, you're telling the system what NULL means for that query. That's all you're doing. You're just telling the system what your data actually means in that query. That's not hard. Complaining that the system that stores structured data actually requires you to understand what your data represent and how your data are being used is... strange.
- stouset 7y ago
  That’s all great but it’s not like we haven’t figured out how to solve this in other languages with `Option` or `Maybe` types. Obviously SQL predates this, but that shouldn’t stop us from lamenting the fact that SQL repeated the Billion Dollar Mistake and discussing a future alternative that’s learned from this and doesn’t feature such a sharp edge.
  - wiml 7y ago
    Sum types like Option/Maybe (or C's NULL) don't do the same thing as SQL's NULL, though. By all means, if you don't need NULLs, then don't use them — `NOT NULL` should probably have been the default in table statements — but they serve a purpose.
- 7y ago
hathawsh 7y ago
The fact that null != null (and NaN != NaN) is, to me, a helpful reminder that the mathematical model behind SQL is not as simple and elegant as newcomers would like it to be. It reminds me of the classic "proof" that 1 = 2, based on simple algebra: https://www.math.toronto.edu/mathnet/falseProofs/first1eq2.html https://www.math.toronto.edu/mathnet/falseProofs/first1eq2.h... If even basic algebra has a trap like that, then all of us need to be prepared for similar traps.
- shezi 7y ago
  There is not a trap in algebra that is shown in that proof: there is a mistake in that proof. They do something that is not allowed, even if it's not obvious what that was at first glance. I don't think one can compare mistakes with thoughtful and powerful modelling.
  - hathawsh 7y ago
    By trap I mean a surprising exception to the common rules. Most high school graduates learn algebra, yet they rarely learn how to avoid accidental division by zero. It's a surprising exception. "null != null" is also a surprising exception to the common rules. I honestly wouldn't expect most developers to know it until their code breaks because of it. That doesn't mean the surprising rule is wrong, it just means people have to learn it. Once they learn it, they may try to find the deeper model where the rule is no longer a surprise but rather an important feature.

sjwright 7y ago

Interesting. The SQL concept of null always made perfect sense to me. Two people with the same middle name would have equality with respect to the string value of middleName. The same applies with two people known to have no middle name—where you store a zero length string. Two people who didn’t supply a middle name should not have equality with respect to middleName. Hence null. This acknowledged lack of data cannot be equal to a different lack of data.

progval 7y ago
> The same applies with two people known to have no middle name—where you store a zero length string. I think that's the thing. People use NULL to mean empty value, instead of unknown value. Not necessarily for strings because there's the empty string, but for numeric fields, including foreign keys
- sjwright 7y ago
  Admittedly non-string fields do make me wish for two kinds of NULL so I can differentiate between zero, confirmed-nothing and unknown. But it's never been an issue for me in practice.
rmrfchik 7y ago
Except, Oracle treats zero length string as null.
- progre 7y ago
  That's just stupid
- sjwright 7y ago
  Seriously? That's the sort of thing I'd expect to find in MySQL 3-point-something, not Oracle.
  - Sharlin 7y ago
    Yes. It's horrible. But presumably it's a decades-old "feature" retained for backward compatibility…

necovek 7y ago

Since we are in the context of Postgresql, there is 'IS (NOT) DISTINCT FROM' for those who believe NULL's behaviour is reversed in (in)equality comparisons.

EmielMols 7y ago

It's fairly nice but indeed rather implicit. I would kill for a postgres setting where feeding (an evaluated) NULL to the where clause logs a warning. This would force developers to coalesce (at least on top level) and think about the null case when applicable.

scandox 7y ago

Yeah the big kick in the pants I got this year was using nullable columns in composite keys in MySQL and finding out that when their value is null, the result key is considered unique, because NULL is literally unknowable. So: col A, Col B null, 1 null, 1 Is not constrained, but: col A, Col B 1, 1 1, 1 <--- NOT ALLOWED I guess this is one of those things that is completely obvious to those that know it.