5 ms·

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,

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...
    - icedchai 8y ago
      NULL is an alias for UNKNOWN on many systems (like MySQL.) Other DBs don't even have UNKNOWN. UPDATE table set col=value1 and value2 works fine IF value1 and value2 are booleans.
      - da_chicken 8y ago
        That's a great example of MySQL creating a proprietary extension of ANSI SQL that does little more than deliberately mislead users.
        icedchai 8y ago
        According to https://en.wikipedia.org/wiki/Null_%28SQL%29#BOOLEAN_data_type https://en.wikipedia.org/wiki/Null_%28SQL%29#BOOLEAN_data_ty... NULL is the same as UNKNOWN. The standard also asserts that NULL and UNKNOWN "may be used interchangeably to mean exactly the same thing" In 20+ years of DB work, I have NEVER seen anyone use UNKNOWN. It is always NULL. Always.
        da_chicken 8y ago
        Alright, I will withdraw my criticism of MySQL on this issue. However.... > In 20+ years of DB work, I have NEVER seen anyone use UNKNOWN. I mean, I've already shown where Microsoft does just that [0]. Oracle pretty clearly does the same [1] [2]. People don't use it because you can almost never refer to it directly. The language intentionally hides it. About the only place I know that you can is PostgreSQL [3], which supports the "boolean_expression IS UNKNOWN" predicate. > The standard also asserts I assume you've got the 2003 draft standard that's around [4]. I will use that because I don't see any more recent version of 9075-2 that's freely available. Yes, the standard does say under 4.5 Boolean types: > This specification does not make a distinction between the null value of the boolean data type and the truth value Unknown that is the result of an SQL <predicate>, <search condition>, or <boolean value expression>; they may be used interchangeably to mean exactly the same thing. However, that's in the context of describing the Boolean user data type, a.k.a., BOOLEAN. You can tell because 4.2 describes character strings (CHAR, VARCHAR, etc), 4.3 describes binary strings, 4.4 describes the numeric data type, 4.6 describes DATETIME, and 4.7 describes user-defined types. The standard is not saying that UNKNOWN and NULL are the same. It's saying that the Boolean user data type can use NULL to represent UNKNOWN. It's saying that if you choose implement a BOOLEAN user data type, you can use NULL to represent UNKNOWN. If you choose to assign a boolean expression to a column, that is. Nevertheless, an SQL <predicate>, <search condition>, or <boolean value expression> has a value of True, False, or Unknown. This shown by looking at 6.34 <boolean value expression>: <truth value> ::= TRUE | FALSE | UNKNOWN Or by searching section 8 and seeing where every time they talk about one of the value expressions being the null value, then the predicate "is Unknown". [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... [1]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions004.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/condi... [2]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_e... [3]: https://www.postgresql.org/docs/11/functions-comparison.html https://www.postgresql.org/docs/11/functions-comparison.html [4]: http://www.wiscorp.com/sql_2003_standard.zip http://www.wiscorp.com/sql_2003_standard.zip Edit: Bit of cleanup.
- mercer 8y ago
  I thought in most (many?) languages NaN != NaN ?
lmm 8y ago
My degree's in mathematics and I share your disdain for pointer bit-twiddling. I still find SQL nulls difficult to reason about or diagnose. I'm sure there are times and places when their behaviour is what you want but most of the time they're just a big extra complication that you don't want or need.
- lugg 8y ago
  In the tables I define everything is not null with sane defaults by default. The places I do allow null are few and far between (e.g. updated_at) and I'm struggling to think of instances I've used them as anything other than absence indicators. In fact I don't think I ever treat it as anything other than that in code either. Was the purpose of null ever to mean anything other than I have not been defined/set? All my objects are statically typed so I never run into the issue of testing is thing.x a thing, it's always a thing, or it's a compile error. It's either set or not set, and thanks to the database convention I only have to worry about certain values having null, most of the time it makes sense anyway. Is updated_at turthy doubles as has been updated tests. Am I incorrect in this method? With this method I fail to see big extra complication. Will switching to option types help me? I debate they will not. But I'm happy to be convinced. I do avoid nulls. I just haven't seen a problem with them in my own code. (Not true for others) Specifically, with the caching problem, provided you constrain the cache to reason about null == not set. I see no problem. Cache.get(K) // null Cache.set(K,3) // void Cache.get(K) // 3 Cache.set(K, null) // deletes, void Cache.get(K) // null Cache.set(K, false) Cache.get(K) // false Only certain values of mine are going to potentially be null from the database, all of which will be contained within serialised objects. I just never see the issue the author has. The times K do see it are when people get too clever with default values. I understand it, I just don't see it in practice. Certainly my not frequently enough to make language changes. Title should just read "Stop abusing null" because the only time I've seen it be an issue is when people are dual encoding meaning.
  - lmm 8y ago
    > In fact I don't think I ever treat it as anything other than that in code either. > Was the purpose of null ever to mean anything other than I have not been defined/set? Different people understand null differently (it might mean "error", "value not in map", "invalid user input", ...) and there's never been a clear consensus. If "null" only ever has one meaning anywhere in your codebase, and any third-party libraries you use only ever use it to mean the same thing, you're probably ok. But as soon as there are multiple meanings you'll have confusion and bugs. > Specifically, with the caching problem, provided you constrain the cache to reason about null == not set. I see no problem. If you only have the one cache, sure. As soon as you have a two-level cache you start to have problems (you can no longer cache absent results, since you're using the same representation for absence from your outer cache). Or as soon as null shows up anywhere else. It's the same problem as stuff that relies on evaluation order, or threadlocals: it's ok most of the time, as long as you're not combining it with something else that does the same thing. The trouble is the times when these noncompositional constructs break down are when you have complex nested code - which is precisely when you most need everything to work the way you'd expect.

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...
  - icedchai 8y ago
    NULL is an alias for UNKNOWN on many systems (like MySQL.) Other DBs don't even have UNKNOWN. UPDATE table set col=value1 and value2 works fine IF value1 and value2 are booleans.
    - da_chicken 8y ago
      That's a great example of MySQL creating a proprietary extension of ANSI SQL that does little more than deliberately mislead users.
      - icedchai 8y ago
        According to https://en.wikipedia.org/wiki/Null_%28SQL%29#BOOLEAN_data_type https://en.wikipedia.org/wiki/Null_%28SQL%29#BOOLEAN_data_ty... NULL is the same as UNKNOWN. The standard also asserts that NULL and UNKNOWN "may be used interchangeably to mean exactly the same thing" In 20+ years of DB work, I have NEVER seen anyone use UNKNOWN. It is always NULL. Always.
        da_chicken 8y ago
        Alright, I will withdraw my criticism of MySQL on this issue. However.... > In 20+ years of DB work, I have NEVER seen anyone use UNKNOWN. I mean, I've already shown where Microsoft does just that [0]. Oracle pretty clearly does the same [1] [2]. People don't use it because you can almost never refer to it directly. The language intentionally hides it. About the only place I know that you can is PostgreSQL [3], which supports the "boolean_expression IS UNKNOWN" predicate. > The standard also asserts I assume you've got the 2003 draft standard that's around [4]. I will use that because I don't see any more recent version of 9075-2 that's freely available. Yes, the standard does say under 4.5 Boolean types: > This specification does not make a distinction between the null value of the boolean data type and the truth value Unknown that is the result of an SQL <predicate>, <search condition>, or <boolean value expression>; they may be used interchangeably to mean exactly the same thing. However, that's in the context of describing the Boolean user data type, a.k.a., BOOLEAN. You can tell because 4.2 describes character strings (CHAR, VARCHAR, etc), 4.3 describes binary strings, 4.4 describes the numeric data type, 4.6 describes DATETIME, and 4.7 describes user-defined types. The standard is not saying that UNKNOWN and NULL are the same. It's saying that the Boolean user data type can use NULL to represent UNKNOWN. It's saying that if you choose implement a BOOLEAN user data type, you can use NULL to represent UNKNOWN. If you choose to assign a boolean expression to a column, that is. Nevertheless, an SQL <predicate>, <search condition>, or <boolean value expression> has a value of True, False, or Unknown. This shown by looking at 6.34 <boolean value expression>: <truth value> ::= TRUE | FALSE | UNKNOWN Or by searching section 8 and seeing where every time they talk about one of the value expressions being the null value, then the predicate "is Unknown". [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... [1]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions004.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/condi... [2]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_e... [3]: https://www.postgresql.org/docs/11/functions-comparison.html https://www.postgresql.org/docs/11/functions-comparison.html [4]: http://www.wiscorp.com/sql_2003_standard.zip http://www.wiscorp.com/sql_2003_standard.zip Edit: Bit of cleanup.
mercer 8y ago
I thought in most (many?) languages NaN != NaN ?

lmm 8y ago

My degree's in mathematics and I share your disdain for pointer bit-twiddling. I still find SQL nulls difficult to reason about or diagnose. I'm sure there are times and places when their behaviour is what you want but most of the time they're just a big extra complication that you don't want or need.

lugg 8y ago
In the tables I define everything is not null with sane defaults by default. The places I do allow null are few and far between (e.g. updated_at) and I'm struggling to think of instances I've used them as anything other than absence indicators. In fact I don't think I ever treat it as anything other than that in code either. Was the purpose of null ever to mean anything other than I have not been defined/set? All my objects are statically typed so I never run into the issue of testing is thing.x a thing, it's always a thing, or it's a compile error. It's either set or not set, and thanks to the database convention I only have to worry about certain values having null, most of the time it makes sense anyway. Is updated_at turthy doubles as has been updated tests. Am I incorrect in this method? With this method I fail to see big extra complication. Will switching to option types help me? I debate they will not. But I'm happy to be convinced. I do avoid nulls. I just haven't seen a problem with them in my own code. (Not true for others) Specifically, with the caching problem, provided you constrain the cache to reason about null == not set. I see no problem. Cache.get(K) // null Cache.set(K,3) // void Cache.get(K) // 3 Cache.set(K, null) // deletes, void Cache.get(K) // null Cache.set(K, false) Cache.get(K) // false Only certain values of mine are going to potentially be null from the database, all of which will be contained within serialised objects. I just never see the issue the author has. The times K do see it are when people get too clever with default values. I understand it, I just don't see it in practice. Certainly my not frequently enough to make language changes. Title should just read "Stop abusing null" because the only time I've seen it be an issue is when people are dual encoding meaning.
- lmm 8y ago
  > In fact I don't think I ever treat it as anything other than that in code either. > Was the purpose of null ever to mean anything other than I have not been defined/set? Different people understand null differently (it might mean "error", "value not in map", "invalid user input", ...) and there's never been a clear consensus. If "null" only ever has one meaning anywhere in your codebase, and any third-party libraries you use only ever use it to mean the same thing, you're probably ok. But as soon as there are multiple meanings you'll have confusion and bugs. > Specifically, with the caching problem, provided you constrain the cache to reason about null == not set. I see no problem. If you only have the one cache, sure. As soon as you have a two-level cache you start to have problems (you can no longer cache absent results, since you're using the same representation for absence from your outer cache). Or as soon as null shows up anywhere else. It's the same problem as stuff that relies on evaluation order, or threadlocals: it's ok most of the time, as long as you're not combining it with something else that does the same thing. The trouble is the times when these noncompositional constructs break down are when you have complex nested code - which is precisely when you most need everything to work the way you'd expect.