"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.
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.
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.
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-wi...
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.
This means that sum(A ++ B) = sum(A) + sum(B). (++ is concatenation)
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
What is the sum of an empty list in Haskell? What is its length?
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.
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.
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.
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.
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.