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-wi...
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.
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
That interpretation makes sense to me when I read the docs, but is way too easy to miss when I'm actually doing comparisons.
Counter example: SAP HANA doesn't support NULLs, and as a result if I see 0 in any column in the database I have to wonder was it 0 that user submitted or 0 that system added as a placeholder. I would need to go back to documentation and see if the field is mandatory etc. Better have it explicitly stored in the data itself.
For example: a shopping cart might contain a tee-shirt in blue colour and expedited shipping in NULL colour. That blue tee-shirt might be the same colour as some blue socks... but that NULL-coloured shipping isn't the same colour as a NULL-coloured charitable donation.
Especially for (composite) unique indexes on NULLable foreign keys, where NULL signifies the non-existence of a different table entry (if you don't want to resort to a special "not existing" entry in that table), this behavior can be surprising and definitely got me a few times.
If two different products have the price of $0 and therefore free, their prices are equal.
If two different products both have prices which are "not known", their unknown prices are not equal.
If two different products both have prices which are "known to not exist" their undefined prices are also not equal.
If SQL servers had algebraic types and I could roll my own "Maybe", then this bizarre decision would work. But they don't, so it doesn't.
The problem with having a value called “none” is that it would be tempting to make none = none return true, which is madness.
(I do agree it would be nice if we could have multiple kinds of null instead of just one. But I would want them all to have the same equality properties as NULL.)
SELECT 'foo' WHERE bar = baz FROM quux
UNION ALL
SELECT 'bar' WHERE bar <> baz FROM quux
But that's not what SQL Servers do. They're all pedantic about how NULL means unknown right up until the rubber hits the road of the WHERE clause, when suddenly NULL means false.
Which means that they're not saying that foo = bar is unknown, they're instead making a definitive assertion that both foo = bar is false and foo <> bar is false. And that's disastrously wrong.
Were it any other way, joins wouldn’t work properly.
create table test(a bool);
insert into test(a) values (NULL);
select case a when a then true else false end from test;
Since null = null is false, your case statement will return the else value. You hard coded it to be false, therefore it will return false.
If you want the lack of data to be acknowledged in a case statement, it’s up to you to handle it. SQL isn’t a magical fairy that does what you want without asking.
col A, Col B
null, 1
null, 1
col A, Col B
1, 1
1, 1 <--- NOT ALLOWED
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.
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.
Or maybe what you're saying is really just that they shouldn't have them, and every modern language has gotten this wrong?
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.
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).
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.
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")
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")
null != james.car.colour
traverse (flip when (putStrLn "James and Jane have same car color")) $
liftA2 == james.car.colour jane.car.colour
Simple point:
SELECT 'foo' WHERE bar = baz FROM quux
UNION ALL
SELECT 'bar' WHERE bar <> baz FROM quux
1) The result is a single row containing "foo" or "bar".
2) The result is an error message that the truthiness of 'bar = baz' cannot be determined.
However, ansi-null-compatible-SQL just will give you an empty resultset if bar or baz are null. That's not the server saying "I don't know" to "bar = baz", that's the server saying "neither" which is very freaking different. "I don't know" is an acceptable answer. "neither" is a lie.
That is unacceptable, and I'm utterly bewildered that people can insist that this is okay.
Because, by definition, the WHERE clause returns a record when it evaluates to TRUE. FALSE and UNKNOWN are not TRUE.
Second, you absolutely can do what you're trying to here, but not the way you've done it:
SELECT CASE WHEN bar = baz THEN 'foo' ELSE 'bar' END AS corge
FROM quux
You're kidding, right? Instead of
WHERE Field = 1
WHERE Field = 1 OR Field IS NULL
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.
First of all, what happens if the value of Field is actually 1? Did you want that record affected? Because now it will be. It's not exactly clear from your query that you did, is it? This is explicit:
WHERE (Field = 1 OR Field IS NULL)
WHERE COALESCE(Field, 1) = 1
It's a serious problem, because in the real world programmers are not willing to double the length of their code for the sake of catching bugs that happen maybe 1 in 100 times. If the language doesn't make it easier to do the right thing than not, you'll end up with bugs. http://www.haskellforall.com/2016/04/worst-practices-should-...
> 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.
The problem is that there's no way to have a model that doesn't include that. At least 80% of the time, probably more, having no value for a given field is just an error, there is no correct way to handle it, so you want to mark it as non-nullable and get on with your life. But in SQL there's no type system that can distinguish nullable things from non-nullable, so you have to either treat every single column and intermediate value as nullable, or make error-prone manual checks, or just get weird results every so often.
No, the problem is that programmers don't like to understand the problem before writing the solution. The problem is worshiping code brevity instead of understanding what their code does. No, that's not a new problem, but it doesn't mean that it becomes the language's problem that you have to actually understand what you're telling something to do. Platitudes like "worst practices should be hard" are all well and good, but they're just that. It should be really difficult to include a buffer overflow, or a dereference a null pointer. In practice, languages that do that have a cost overhead. You still had to write that extra code, the compiler or library just did it for you.
I get it. Developers have a lot to know these days, and stacks and frameworks change rapidly. But the relational model has been largely unchanged for 50 years, and SQL has been basically the same for 30 years. The relational model is very old, and it hasn't changed for very good reasons. There's like 5 to 10 basic patterns for queries that you have to know, and that will cover you in 95% of cases if your system is well designed and you actually understand the data.
Either you understand what your application is doing and you understand what your query is doing, or you don't understand either of those things. I'm sorry, there isn't a middle ground, and there never will be regardless of what data store you choose, what language you use, etc. The guy at the keyboard still has to know what he's doing.
> At least 80% of the time, probably more, having no value for a given field is just an error
Then set the column to have a NOT NULL constraint and now it's impossible to store a null value. You'll get an error just like trying to store a character value into an integer field.
> But in SQL there's no type system that can distinguish nullable things from non-nullable
That's not true. Every column in a table can have a NOT NULL constraint. If your data is actually required and guaranteed to have a value, you just specify that the table's column can't be NULL. You will have to literally break the database engine to get a null value stored in that column.
Yes, you might have situations where you need to use an outer join which will have null values. Yes, you will have to handle NULLs from an Nth normal form database in your application in this case. It's still not difficult. You're still going to have non-nullable fields in your output unless your design is incredibly poor.
Good language design means accepting that programmers are human and writing for use by programmers as they actually exist, not for some imaginary perfect programmer.
> It should be really difficult to include a buffer overflow, or a dereference a null pointer. In practice, languages that do that have a cost overhead. You still had to write that extra code, the compiler or library just did it for you.
This is a dangerous myth. Most of the problems with C - and SQL - are just deadweight losses. You actually can just use languages that don't have those problems.
> Either you understand what your application is doing and you understand what your query is doing, or you don't understand either of those things. I'm sorry, there isn't a middle ground, and there never will be regardless of what data store you choose, what language you use, etc. The guy at the keyboard still has to know what he's doing.
Languages are tools for understanding. There are good and bad languages, and it's easier to make better languages than make smarter programmers.
> Then set the column to have a NOT NULL constraint and now it's impossible to store a null value. You'll get an error just like trying to store a character value into an integer field.
Right, but there's no way to distinguish that at the point of querying, and no practical way to have NOT NULL constraints on your intermediate variables in the middle of a query.
Test that
t1.f1 = t2.f2
This is actually a hellscape of gotchas as people try to find a short way to do it and then discover that their short version is treating NULL as equivalent to FALSE, which means it will fail when negated. Or they throw in a COALESCE and then fail because they've now treated a special value as equivalent to NULL.
The correct answer is
((t1.f1 IS NULL AND t2.f2 IS NULL) OR (t1.f1 IS NOT NULL AND t2.f2 IS NOT NULL AND t1.f1 = t2.f2)) ((t1.f1 IS NULL AND t2.f2 IS NULL) OR (t1.f1 = t2.f2))
Second, the complicated one that shows up in MERGE or UPSERT operations is the other way:
((t1.f1 IS NULL AND t2.f2 IS NOT NULL) OR (t1.f1 IS NOT NULL AND t2.f2 IS NULL) OR (t1.f1 <> t2.f2))
t1.f1 IS NOT DISTINCT FROM t2.f2
t1.f1 IS DISTINCT FROM t2.f2
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.
I don't think one can compare mistakes with thoughtful and powerful modelling.
"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.