6 ms·

> 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

> 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.
Svip 7y ago
Wouldn't one just use coalesce()? e.g. coalesce(Field, 1) = 1?
- da_chicken 7y ago
  No, that's a bad pattern that should be avoided. 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) And it's not actually that much shorter: WHERE COALESCE(Field, 1) = 1 Second of all, if Field is indexed, well, you probably just ignored that index or turned a seek into a scan (i.e., a slower operation). Putting a field into a function will often mean the query engine can't use the index to help with query performance. It means the query engine will have to do a whole lot more work. The term in the industry is "sargable": https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable
lmm 7y ago
> You might even have to add parenthesis! It's so verbose it puts Java to shame! 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-be-hard.html 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.
- da_chicken 7y ago
  > 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. 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.
Pxtl 7y ago
Do that for two arbitrary variables. Test that t1.f1 = t2.f2 including nulls, for an arbitrary type of f1 and 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))
- da_chicken 7y ago
  First of all, you can shorten that to this: ((t1.f1 IS NULL AND t2.f2 IS NULL) OR (t1.f1 = t2.f2)) That's logically equivalent because TRUE OR UNKNOWN evaluates to TRUE, while FALSE OR UNKNOWN evaluates to UNKNOWN, which is not TRUE and therefore gets excluded. 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)) Third, this is exactly why many RDBMSs support the IS [NOT] DISTINCT FROM syntax defined in ANSI SQL, which replaces the first: t1.f1 IS NOT DISTINCT FROM t2.f2 Or this which replaces the second: t1.f1 IS DISTINCT FROM t2.f2 MS SQL Server and MySQL don't, but Oracle and PostgreSQL do. It's been in the standard for over 20 years at this point. So it's not the language that sucks; it's your vendor.
  - Pxtl 7y ago
    And then somebody negates your "is equal" because they want "not equal" statement and tears their hair out.

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.

Svip 7y ago

Wouldn't one just use coalesce()? e.g. coalesce(Field, 1) = 1?

da_chicken 7y ago
No, that's a bad pattern that should be avoided. 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) And it's not actually that much shorter: WHERE COALESCE(Field, 1) = 1 Second of all, if Field is indexed, well, you probably just ignored that index or turned a seek into a scan (i.e., a slower operation). Putting a field into a function will often mean the query engine can't use the index to help with query performance. It means the query engine will have to do a whole lot more work. The term in the industry is "sargable": https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable

lmm 7y ago

> You might even have to add parenthesis! It's so verbose it puts Java to shame! 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-be-hard.html 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.

da_chicken 7y ago
> 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. 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.

Pxtl 7y ago

Do that for two arbitrary variables. Test that t1.f1 = t2.f2 including nulls, for an arbitrary type of f1 and 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))

da_chicken 7y ago
First of all, you can shorten that to this: ((t1.f1 IS NULL AND t2.f2 IS NULL) OR (t1.f1 = t2.f2)) That's logically equivalent because TRUE OR UNKNOWN evaluates to TRUE, while FALSE OR UNKNOWN evaluates to UNKNOWN, which is not TRUE and therefore gets excluded. 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)) Third, this is exactly why many RDBMSs support the IS [NOT] DISTINCT FROM syntax defined in ANSI SQL, which replaces the first: t1.f1 IS NOT DISTINCT FROM t2.f2 Or this which replaces the second: t1.f1 IS DISTINCT FROM t2.f2 MS SQL Server and MySQL don't, but Oracle and PostgreSQL do. It's been in the standard for over 20 years at this point. So it's not the language that sucks; it's your vendor.
- Pxtl 7y ago
  And then somebody negates your "is equal" because they want "not equal" statement and tears their hair out.