7 ms·

You know who works on a platform with NULL but doesn't have quite so many problems with it? DBAs. There's some need to draw a distinction between the basic ide

You know who works on a platform with NULL but doesn't have quite so many problems with it? DBAs. There's some need to draw a distinction between the basic idea of NULL, and the way that NULL has been implemented in most high-level programming languages. In most RDBMSes, values can't be null unless you say they are. Sometimes explicitly, as in table definitions, sometimes implicitly, when you select a JOIN type. Either way, though, the fact that the developer is in control of when it can and cannot happen means that it always has a knowable meaning. (Or should, anyway.) The problem with many programming languages is, you're given it whether you want it or not. In a low-level language like C, that's reasonable, because it takes a sensible approach to how it works: Only pointers can be null, and all pointers are nullable for obvious (especially in the 1970s) reasons. More generally, I'm not going to fault languages from that era for trying it out, because this stuff was new, and things were still being felt out. So I don't really fault Tony Hoare for giving null references a try in ALGOL W. What seems much more bothersome is high level languages like Java and C# cargo culting this behavior. They could have followed the lead from languages like SQL and let the programmer be in control. They should have. They already throw exceptions when a memory allocation fails, and they allow inline variable initialization, and declaring variables at the point of usage, and composite data types have constructors, so they lack all of (early) C's reasons why ubiquitous nulls were a good idea. They could have, I think quite easily, made nullability optional. At which point it'd have basically the same semantics as optional types from functional programming, so I doubt we'd be worrying about it anymore. But they didn't.

lmm 8y ago
NULL in SQL is a notorious source of errors and confusion (particularly when it comes to e.g. tri-state boolean logic). It certainly can come from nowhere and surprise you - if anything the behaviour is even worse than in Java or C#. So I don't think there's anything to learn from there. (Rather what modern languages should have done - and increasingly do - is follow ML practice and avoid null entirely, implementing option types as ordinary library types where the programmer explicitly wants to represent absence).
- bunderbunder 8y ago
  I guess. My tendency is to think that it's more a problem for developers who are new to SQL, and are surprised to find out that, despite having the same name, nulls in SQL don't have the same semantics as nulls in other languages. Once you get a handle on the semantics, though, they make a lot of sense. The trick is to understand how SQL's NULL is rooted in mathematical formalism, not the pragmatics of dealing with pointers. It has more in common with NaN in floating-point numbers. So, for SQL, "null <> null" behaves like "NaN <> NaN". For C and friends, "null == null" for the same reason that "0 == 0".
  - nayuki 8y ago
    In SQL, NULL<>NULL yields false. You use IS NULL / IS NOT NULL to test for NULL values. In programming languages, NaN!=NaN yields true. You use x!=x to test for NaN values. Saying that SQL NULL is rooted in mathematical formalism doesn't explain anything, because anything (even nullptr and NaN) can be explained in mathematical formalism. What we want is a simple semantic model that a human can understand and one that lacks nasty unintuitive surprises.
    - da_chicken 8y ago
      > In SQL, NULL<>NULL yields false. No, NULL <> NULL yields UNKNOWN. That's why NULL <> NULL and NOT (NULL <> NULL) behave the same: they have the same value. UNKNOWN is a first-order truth value in ternary logic. The key is that in a WHERE clause, a record is only returned if the WHERE clause evaluates to TRUE. Not TRUE or UNKNOWN. TRUE.
    - masklinn 8y ago
      > In SQL, NULL<>NULL yields false. It yields NULL, not false. So do NULL = NULL or NOT NULL.
      - da_chicken 8y ago
        NULL isn't a Boolean value in ternary logic any more than 3.2 or 'Hello' or December 12, 2018, have Boolean values. It's UNKNOWN. UNKNOWN is related to NULL, but they don't work identically. NULL is a value that any column data type can potentially have. NULL is what comparison and evaluation operators work with. UNKNOWN is a ternary Boolean type, and the Boolean type is what Boolean operators work with (AND, OR, NOT) and nothing else. This Boolean type in an RDBMS is unavailable to the user and is for internal evaluation purposes only. RDBMSs that support a "bool" type are not implementing the same thing. You can never say UPDATE MyTable SET Col = Value1 AND Value2. That's not going to work. Many RDBMSs have a documentation page that explains this difference, like this one[0] from Microsoft SQL Server. Notably, NULL + 3 and NULL * 5 are both NULL. Any mathematic operation on NULL is NULL. But UNKNOWN AND FALSE is FALSE, and UNKNOWN OR TRUE is TRUE. [0]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/language-elements...
jdblair 8y ago
Lua's version of NULL, called nil, once bit me badly due to behavior in a sqlite library. The sqlite library I was using represented SQL NULL as nil, a perfectly reasonable choice. However, in lua there is also a convention to use nil as the end of table sentinel. This meant innocent looking code using ipairs() would stop iterating on a row of results once it hit a nil, which could occur in anywhere. It meant we were missing data (the lua code uploaded locally collected data to a server) until I figured out the cause and explicitly iterated over the expected size of the table.
nayuki 8y ago
NULL in SQL really isn't great. For one, nullable table columns is a bad default, and you have to explicitly write out "NOT NULL" to avoid this behavior. I'd say that 90% of the time I want not-null table columns, and only 10% of the time do I want a nullable column. Secondly, NULL has weird arithmetic. It turns out that NULL=NULL is false, and NULL<>NULL is also false. (This is unlike C/Java/Python/etc. by the way.) Thirdly, even if you design all your tables to have NOT NULL on all columns, your queries can still synthesize NULL values in the results. For example, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, (but not INNER JOIN). For example, computing max(column) on a table with zero rows.
- cpburns2009 8y ago
  I find NULL to be incredibly useful. I do agree that it has bad defaults in SQL, and equality is annoying similar to NaN in other languages.
- dpcx 8y ago
  I can get behind your first statement. Having NULLable as a default on columns is "probably" a bad idea. I'm not so sure I can agree with the other two. NULL<>NULL (and NULL=NULL) both return false for a very simple reason: truly missing data _can't_ be equal to anything, including missing data... Because it's missing. You cannot with certainty say that value1 is or is not equal to each other. For the third point... What should max(column) return when there's no data? You're telling the engine "give me the maximum value of something that doesn't exist". That is, in my experience, "missing data."
  - smadge 8y ago
    For example, if it were the case that NULL = NULL, really counterintuitive stuff would happen on joins because a null cell would match with every other null cell you are joining on: person name home_address --------------------------- "Alice" NULL "Bob" "123 Jump Street" letter return_address description ---------------------------------- NULL "Ransom Letter" NULL "Spy Document" "123 Jump Street" "Hello, from Bob" Then SELECT name, description FROM person INNER JOIN letter ON home_address = return_address would return name description ------------ "Alice" "Ransom Letter" "Alice" "Anonymous Spy Document" "Bob" "Hello from Bob" So now Alice is associated with a bunch of letters she didn't necessarily write because she doesn't have a home address.
empath75 8y ago
Are there any dbs based on modern type theory?

lmm 8y ago

NULL in SQL is a notorious source of errors and confusion (particularly when it comes to e.g. tri-state boolean logic). It certainly can come from nowhere and surprise you - if anything the behaviour is even worse than in Java or C#. So I don't think there's anything to learn from there. (Rather what modern languages should have done - and increasingly do - is follow ML practice and avoid null entirely, implementing option types as ordinary library types where the programmer explicitly wants to represent absence).

bunderbunder 8y ago
I guess. My tendency is to think that it's more a problem for developers who are new to SQL, and are surprised to find out that, despite having the same name, nulls in SQL don't have the same semantics as nulls in other languages. Once you get a handle on the semantics, though, they make a lot of sense. The trick is to understand how SQL's NULL is rooted in mathematical formalism, not the pragmatics of dealing with pointers. It has more in common with NaN in floating-point numbers. So, for SQL, "null <> null" behaves like "NaN <> NaN". For C and friends, "null == null" for the same reason that "0 == 0".
- nayuki 8y ago
  In SQL, NULL<>NULL yields false. You use IS NULL / IS NOT NULL to test for NULL values. In programming languages, NaN!=NaN yields true. You use x!=x to test for NaN values. Saying that SQL NULL is rooted in mathematical formalism doesn't explain anything, because anything (even nullptr and NaN) can be explained in mathematical formalism. What we want is a simple semantic model that a human can understand and one that lacks nasty unintuitive surprises.
  - da_chicken 8y ago
    > In SQL, NULL<>NULL yields false. No, NULL <> NULL yields UNKNOWN. That's why NULL <> NULL and NOT (NULL <> NULL) behave the same: they have the same value. UNKNOWN is a first-order truth value in ternary logic. The key is that in a WHERE clause, a record is only returned if the WHERE clause evaluates to TRUE. Not TRUE or UNKNOWN. TRUE.
  - masklinn 8y ago
    > In SQL, NULL<>NULL yields false. It yields NULL, not false. So do NULL = NULL or NOT NULL.
    - da_chicken 8y ago
      NULL isn't a Boolean value in ternary logic any more than 3.2 or 'Hello' or December 12, 2018, have Boolean values. It's UNKNOWN. UNKNOWN is related to NULL, but they don't work identically. NULL is a value that any column data type can potentially have. NULL is what comparison and evaluation operators work with. UNKNOWN is a ternary Boolean type, and the Boolean type is what Boolean operators work with (AND, OR, NOT) and nothing else. This Boolean type in an RDBMS is unavailable to the user and is for internal evaluation purposes only. RDBMSs that support a "bool" type are not implementing the same thing. You can never say UPDATE MyTable SET Col = Value1 AND Value2. That's not going to work. Many RDBMSs have a documentation page that explains this difference, like this one[0] from Microsoft SQL Server. Notably, NULL + 3 and NULL * 5 are both NULL. Any mathematic operation on NULL is NULL. But UNKNOWN AND FALSE is FALSE, and UNKNOWN OR TRUE is TRUE. [0]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/language-elements...

jdblair 8y ago

Lua's version of NULL, called nil, once bit me badly due to behavior in a sqlite library. The sqlite library I was using represented SQL NULL as nil, a perfectly reasonable choice. However, in lua there is also a convention to use nil as the end of table sentinel. This meant innocent looking code using ipairs() would stop iterating on a row of results once it hit a nil, which could occur in anywhere. It meant we were missing data (the lua code uploaded locally collected data to a server) until I figured out the cause and explicitly iterated over the expected size of the table.

nayuki 8y ago

NULL in SQL really isn't great. For one, nullable table columns is a bad default, and you have to explicitly write out "NOT NULL" to avoid this behavior. I'd say that 90% of the time I want not-null table columns, and only 10% of the time do I want a nullable column. Secondly, NULL has weird arithmetic. It turns out that NULL=NULL is false, and NULL<>NULL is also false. (This is unlike C/Java/Python/etc. by the way.) Thirdly, even if you design all your tables to have NOT NULL on all columns, your queries can still synthesize NULL values in the results. For example, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, (but not INNER JOIN). For example, computing max(column) on a table with zero rows.

cpburns2009 8y ago
I find NULL to be incredibly useful. I do agree that it has bad defaults in SQL, and equality is annoying similar to NaN in other languages.
dpcx 8y ago
I can get behind your first statement. Having NULLable as a default on columns is "probably" a bad idea. I'm not so sure I can agree with the other two. NULL<>NULL (and NULL=NULL) both return false for a very simple reason: truly missing data _can't_ be equal to anything, including missing data... Because it's missing. You cannot with certainty say that value1 is or is not equal to each other. For the third point... What should max(column) return when there's no data? You're telling the engine "give me the maximum value of something that doesn't exist". That is, in my experience, "missing data."
- smadge 8y ago
  For example, if it were the case that NULL = NULL, really counterintuitive stuff would happen on joins because a null cell would match with every other null cell you are joining on: person name home_address --------------------------- "Alice" NULL "Bob" "123 Jump Street" letter return_address description ---------------------------------- NULL "Ransom Letter" NULL "Spy Document" "123 Jump Street" "Hello, from Bob" Then SELECT name, description FROM person INNER JOIN letter ON home_address = return_address would return name description ------------ "Alice" "Ransom Letter" "Alice" "Anonymous Spy Document" "Bob" "Hello from Bob" So now Alice is associated with a bunch of letters she didn't necessarily write because she doesn't have a home address.

empath75 8y ago

Are there any dbs based on modern type theory?