4 ms·

"Fixing the Billion Dollar Mistake" Option/Maybe types and exhaustive pattern matching was an amazing contribution of ML and similar languages. I would like t

"Fixing the Billion Dollar Mistake" Option/Maybe types and exhaustive pattern matching was an amazing contribution of ML and similar languages. I would like to be able to do that in SQL, as well. NULL is arguably in much worse shape in SQL.

tsimionescu 6y ago
I think NULL in SQL is very different from NULL in most PLs, and is much better behaved. It is on fact much closer to an Optional than to a 0 pointer. First of all, in SQL NULL is entirely your choice - no column is forced to accept NULL values. Secondly, most SQL functions treat NULL just like mapping over an optional - a < b works just like a.map(|a| {b.map(|b| {a < b})}). The only exception are aggregation functions, that also do the more sa e thing in my opinion, at least for most use cases - AVERAGE() over (a, NULL, b) is a+b/2.
- jeff-davis 6y ago
  "First of all, in SQL NULL is entirely your choice - no column is forced to accept NULL values." That's not true. Aggregate functions and outer joins produce NULLs even without NULLs in your data. This absolutely can cause weirdness. "The only exception are aggregation functions..." It's more weird and subtle than that. See: 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...
  - zodiac 6y ago
    Well, I think that's analogous to the max function having signature (say) Array<Int> -> Optional<Int>
    - jeff-davis 6y ago
      But why is the SUM of no elements NULL, but the COUNT-star of no elements zero? Sure, for any given case you can come up with some explanation. But there is no general explanation that covers many cases. And that leads to weirdness and surprises and bugs. Option types are way better and always do what you expect.
      - christophilus 6y ago
        Because 0 is a valid sum for non empty sets, so it’s nice to be able to differentiate between: the items add up to 0 and: there are no items to sum.
        petters 6y ago
        It is useful to have the sum and product of empty lists be 0 and 1, respectively. This means that sum(A ++ B) = sum(A) + sum(B). (++ is concatenation)
        magicalhippo 6y ago
        And you can have that by using COALESCE(SUM(A), 0) for example.
        heavenlyblue 6y ago
        But COUNT is just a SUM over ones for every row in the output
        tsimionescu 6y ago
        By what definition? If anything, sums are defined as cardinalities of sets, rather than defining cardinalities in terms of sums.
        heavenlyblue 6y ago
        Then what is a NULL cardinality?
      - zodiac 6y ago
        > Option types are way better and always do what you expect. No, I can implement the exact same weird functions with option types, with signatures sum: Array<Int> -> Optional<Int> count_elements: Array<Int> -> Int FWIW, I personally prefer both functions to not return Optionals, but the point is that the mere presence of Option types in your type system (and the absence of null pointers) doesn't magically eliminate bad library design
  - tsimionescu 6y ago
    From that list I think the only really surprising one is "3 NOT IT (1, 2, NULL)" returning NULL. The fact that ARRAY_AGG (C) and COUNT(C) treat NULL differently is also a bit surprising, though in terms of usefulness I think the current behavior is not bad. I also think the definitions is relatively clear: NULL means "unknown"/"missing data". The MIN, MAX, and AVERAGE of an empty list is N/A. SUM could have been 0, but then SUM(X) / COUNT(X) would be different from AVERAGE(X). The count of tuples (COUNT(*)) in an empty relation is 0. The count of pieces of data on column X (COUNT(X)) of a table ((1), (NULL)) is 1. If we have 2 tables A(ID, C1), VALUES ((1, 2)), B(ID, C2), VALUES ((2, 3)), and try to ask what value B.C2 has for A.ID=1, the answer can only be NULL. IS NULL and IS DISTINCT FROM NULL are explicitly treating NULL differently, so it's not that surprising that they can be both true and both false. Records and Strings are not the same KIND of types, so I don't see anything surprising about the fact that one can contain NULLs while the other can't. It's like complaining that a record in Haskell can have a Maybe field, while a String can't have a Maybe character.
    - kd5bjo 6y ago
      > The MIN, MAX, and AVERAGE of an empty list is N/A. SUM could have been 0, but then SUM(X) / COUNT(X) would be different from AVERAGE(X). 0/0 is mathematically undefined— it is a reasonable calculation to produce NULL on its own, and then SUM could do the sensible thing of returning the additive identity.
      - tsimionescu 6y ago
        I still find it much more useful in practice for SUM to return a NULL and not 0. Especially when plotting data over a period of time, having the chart go down to 0 and then go back up to some value is extremely annoying, and the query that would produce the current behavior of SUM would be much more annoying if it were implemented the other way around, while it is extremely easy to transform NULL to 0 if you want.
- rstuart4133 6y ago
  > I think NULL in SQL is very different from NULL in most PLs, and is much better behaved. It is on fact much closer to an Optional than to a 0 pointer. I beg to differ. Dereferencing NULL in C is generally well defined: SIGSEGV. Rust's unwrap() is a modest improvement on that, it reliably panics. Rust's main contribution it's type system forces you to acknowledge the possibly it might be null by making you to write unwrap(). I'd take that any day over what SQL does when you "dereference" a NULL, by say 1 < null. It just propagates the error so it poisons whatever expression contains it. I avoid nulls whenever possible to avoid that. If SQL was in fact closer to Rusts Option then an attempt to use a possibly null value/columns with an operator that can't handle null would produce a syntax error. So writing could_be_null > 0 would be illegal, you would have to write ifnull(could_be_null, 0) > 0.
  - rkangel 6y ago
    Dereferencing Null in C when running on a hosted OS is fairly predictable. Don't forget that a lot of C (and increasingly a lot of Rust) is written for embedded targets where that is usually not true (sometimes address 0 is the start of flash). The only things you can rely on are what the C Spec says you can rely on, and that does not include the behaviour of address 0.
  - pjmlp 6y ago
    That looks like a failure to use NVL and similar operators to me.
  - tsimionescu 6y ago
    Dereferencing NULL in C is undefined behavior - it has no semantics in the C abstract machine. In practice, with a modern optimizing compiler, it can have very surprising behavior, including omitting further null checks after the dereference. While it's true that an explicit dereference is very likely to cause a SIGSEGV (these are Unix semantics, not C), dereferncing NULL + something is likely to lead to memory corruption (very ugly in v[length - 1] or v->field for v==NULL). Also, the recommended way of working with Maybe/Optional values is map() or do-notation, not unwrapping. And map/do have exactly the same semantics as most NULL-based operations in SQL.

tsimionescu 6y ago

I think NULL in SQL is very different from NULL in most PLs, and is much better behaved. It is on fact much closer to an Optional than to a 0 pointer. First of all, in SQL NULL is entirely your choice - no column is forced to accept NULL values. Secondly, most SQL functions treat NULL just like mapping over an optional - a < b works just like a.map(|a| {b.map(|b| {a < b})}). The only exception are aggregation functions, that also do the more sa e thing in my opinion, at least for most use cases - AVERAGE() over (a, NULL, b) is a+b/2.

jeff-davis 6y ago
"First of all, in SQL NULL is entirely your choice - no column is forced to accept NULL values." That's not true. Aggregate functions and outer joins produce NULLs even without NULLs in your data. This absolutely can cause weirdness. "The only exception are aggregation functions..." It's more weird and subtle than that. See: 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...
- zodiac 6y ago
  Well, I think that's analogous to the max function having signature (say) Array<Int> -> Optional<Int>
  - jeff-davis 6y ago
    But why is the SUM of no elements NULL, but the COUNT-star of no elements zero? Sure, for any given case you can come up with some explanation. But there is no general explanation that covers many cases. And that leads to weirdness and surprises and bugs. Option types are way better and always do what you expect.
    - christophilus 6y ago
      Because 0 is a valid sum for non empty sets, so it’s nice to be able to differentiate between: the items add up to 0 and: there are no items to sum.
      - petters 6y ago
        It is useful to have the sum and product of empty lists be 0 and 1, respectively. This means that sum(A ++ B) = sum(A) + sum(B). (++ is concatenation)
        magicalhippo 6y ago
        And you can have that by using COALESCE(SUM(A), 0) for example.
      - heavenlyblue 6y ago
        But COUNT is just a SUM over ones for every row in the output
        tsimionescu 6y ago
        By what definition? If anything, sums are defined as cardinalities of sets, rather than defining cardinalities in terms of sums.
        heavenlyblue 6y ago
        Then what is a NULL cardinality?
    - zodiac 6y ago
      > Option types are way better and always do what you expect. No, I can implement the exact same weird functions with option types, with signatures sum: Array<Int> -> Optional<Int> count_elements: Array<Int> -> Int FWIW, I personally prefer both functions to not return Optionals, but the point is that the mere presence of Option types in your type system (and the absence of null pointers) doesn't magically eliminate bad library design
- tsimionescu 6y ago
  From that list I think the only really surprising one is "3 NOT IT (1, 2, NULL)" returning NULL. The fact that ARRAY_AGG (C) and COUNT(C) treat NULL differently is also a bit surprising, though in terms of usefulness I think the current behavior is not bad. I also think the definitions is relatively clear: NULL means "unknown"/"missing data". The MIN, MAX, and AVERAGE of an empty list is N/A. SUM could have been 0, but then SUM(X) / COUNT(X) would be different from AVERAGE(X). The count of tuples (COUNT(*)) in an empty relation is 0. The count of pieces of data on column X (COUNT(X)) of a table ((1), (NULL)) is 1. If we have 2 tables A(ID, C1), VALUES ((1, 2)), B(ID, C2), VALUES ((2, 3)), and try to ask what value B.C2 has for A.ID=1, the answer can only be NULL. IS NULL and IS DISTINCT FROM NULL are explicitly treating NULL differently, so it's not that surprising that they can be both true and both false. Records and Strings are not the same KIND of types, so I don't see anything surprising about the fact that one can contain NULLs while the other can't. It's like complaining that a record in Haskell can have a Maybe field, while a String can't have a Maybe character.
  - kd5bjo 6y ago
    > The MIN, MAX, and AVERAGE of an empty list is N/A. SUM could have been 0, but then SUM(X) / COUNT(X) would be different from AVERAGE(X). 0/0 is mathematically undefined— it is a reasonable calculation to produce NULL on its own, and then SUM could do the sensible thing of returning the additive identity.
    - tsimionescu 6y ago
      I still find it much more useful in practice for SUM to return a NULL and not 0. Especially when plotting data over a period of time, having the chart go down to 0 and then go back up to some value is extremely annoying, and the query that would produce the current behavior of SUM would be much more annoying if it were implemented the other way around, while it is extremely easy to transform NULL to 0 if you want.
rstuart4133 6y ago
> I think NULL in SQL is very different from NULL in most PLs, and is much better behaved. It is on fact much closer to an Optional than to a 0 pointer. I beg to differ. Dereferencing NULL in C is generally well defined: SIGSEGV. Rust's unwrap() is a modest improvement on that, it reliably panics. Rust's main contribution it's type system forces you to acknowledge the possibly it might be null by making you to write unwrap(). I'd take that any day over what SQL does when you "dereference" a NULL, by say 1 < null. It just propagates the error so it poisons whatever expression contains it. I avoid nulls whenever possible to avoid that. If SQL was in fact closer to Rusts Option then an attempt to use a possibly null value/columns with an operator that can't handle null would produce a syntax error. So writing could_be_null > 0 would be illegal, you would have to write ifnull(could_be_null, 0) > 0.
- rkangel 6y ago
  Dereferencing Null in C when running on a hosted OS is fairly predictable. Don't forget that a lot of C (and increasingly a lot of Rust) is written for embedded targets where that is usually not true (sometimes address 0 is the start of flash). The only things you can rely on are what the C Spec says you can rely on, and that does not include the behaviour of address 0.
- pjmlp 6y ago
  That looks like a failure to use NVL and similar operators to me.
- tsimionescu 6y ago
  Dereferencing NULL in C is undefined behavior - it has no semantics in the C abstract machine. In practice, with a modern optimizing compiler, it can have very surprising behavior, including omitting further null checks after the dereference. While it's true that an explicit dereference is very likely to cause a SIGSEGV (these are Unix semantics, not C), dereferncing NULL + something is likely to lead to memory corruption (very ugly in v[length - 1] or v->field for v==NULL). Also, the recommended way of working with Maybe/Optional values is map() or do-notation, not unwrapping. And map/do have exactly the same semantics as most NULL-based operations in SQL.