4 ms·
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 s
by 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 agoI 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 agoIn 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 agoNULL 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 agoNULL 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 agoThat's a great example of MySQL creating a proprietary extension of ANSI SQL that does little more than deliberately mislead users.
- icedchai 8y agoAccording 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.
- mercer 8y agoI thought in most (many?) languages NaN != NaN ?
- lmm 8y agoMy 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 agoIn 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.
- da_chicken 8y agoThat's not really NULL's fault that it causes confusion in SQL. That's just ternary logic. People who don't handle NULLs in SQL aren't really mishandling the NULL. NULL is just a value. They're simply failing to understand the Boolean value of UNKNOWN and what that means. They're so used to thinking only in bivalent logic that the additional complexity throws them off. However, "It's more complex for me to think about," or, "I don't understand the convention," or even, "It's easy to forget the convention," are not a very convincing arguments. It's similar to arguments about little endian vs big endian. Yes, big endian is how we write our positional numbers, but little endian makes casts a noop. Or arguments about zero-based array indexing. These concepts aren't difficult. They're just more complex. Negative numbers aren't difficult, but they're more complicated than just cardinal numbers. Fractions and decimals aren't difficult, but they're more complex than integers. Multiplication and division aren't difficult, but they're more complex than addition and subtraction.
- lmm 8y ago> Or arguments about zero-based array indexing. These concepts aren't difficult. They're just more complex. Negative numbers aren't difficult, but they're more complicated than just cardinal numbers. Fractions and decimals aren't difficult, but they're more complex than integers. Multiplication and division aren't difficult, but they're more complex than addition and subtraction. We usually consider it a good thing when programming languages let you opt out of the complex thing. In a good language, you can do integer arithmetic if you don't want to deal with fractions or decimals. You can do cardinal arithmetic if you don't want to deal with negative numbers. You can do ordinary Boolean logic if you don't want to do ternary logic. The problem with SQL isn't that it has NULL. It's that it's too hard to not have NULL. Which is the problem with null in general.
- da_chicken 8y agoYeah, but saying "I want to use SQL" and "I don't want to use NULL or ternary logic" is a bit like saying "I want to use the existing datetime types" and "I want all years to have 10 months, all months to have 30 days, etc." Or like saying, "I want everything to use integers" and "I need fractional components." Your requirements break the abstraction not because the system is constrained, but because you're breaking the conceptual model that's the foundation of what you're trying to use. It's not a language problem. It's not a data problem. It's not a computing problem at all. It's applying the wrong conceptual model to meet your needs. That isn't a problem with the conceptual model, either, since plenty of people use it very successfully.