8 ms·
SQL NULLs aren't weird, they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! If you want you can read NULL as UNKNOWN and suddenly a whole bunch of ope
by hiAndrewQuinn 2y ago
SQL NULLs aren't weird, they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic!
If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive:
1. TRUE OR UNKNOWN = TRUE, because you know you have at least one TRUE already.
2. TRUE AND UNKNOWN = UNKNOWN, because you don't know whether you have two TRUEs or not. It's just out there.
3. UNKNOWN XOR UNKNOWN = UNKNOWN, because it could darn near be anything: TRUE XOR TRUE, TRUE XOR FALSE, FALSE XOR FALSE, FALSE XOR TRUE... Internalizing this is where SQL's use of NULL / UNKNOWN really becomes intuitive.
4. (TRUE AND FALSE) XOR (TRUE OR UNKNOWN) = (FALSE) XOR (TRUE) per #1 = TRUE. See, it's consistent, you just need to keep in mind that if you have a lot of known UNKNOWNs they're quite parasitic and your final outcome is likely to be, itself, an UNKNOWN. Just like in real life!
- mrkeen 2y agoIt's unfortunate to namesquat on 'boolean' if your elements have three possible values. Just call it 'ternary logic' and let individuals decide which system to use.
- chuckadams 2y agoMaybe GP was edited, but it doesn't use the word "boolean" anywhere.
- deleted 2y ago[deleted]
- hiAndrewQuinn 2y agoCorrect, I edited "boolean" out prior to ^^P's comment. My apologies.
- foobarchu 2y agoWho's name squatting boolean? The bool column is exactly what it claims to be, you just have the option of introducing unknowability if you define it allow nulls.
- bunderbunder 2y agoThe distinction is that not all formal logic systems are Boolean. Meaning that it is nonsensical and confusing to use "Boolean" as a generic synonym for "truth value" in the same way that it's nonsensical to use "Pantone" as a generic synonym for "color value", including when the specific kind of color value you're talking about is CMYK or HSV and definitely not Pantone.
- Spivak 2y agoBut it is a boolean value, there's only two possible values TRUE and FALSE. But because it's SQL you can define any column as TYPE | NULL. You could say that a boolean column with a NULL value is FALSE like how a lot of programming languages coerce it but if you wanted that you would just make a default of FALSE. The meaning of NULL in general being "value not specified" lends itself pretty nicely to "either true or false."
- bdangubic 2y agoThe meaning of NULL in general being "value not specified" lends itself pretty nicely to "either true or false." You mean neither true or false?
- Spivak 2y agoI think I mean either. So yes NULL is a distinct value from true and false so I think it's also right to say it's neither true nor false. But the value NULL does represent is roughly "could be true or false, we don't know yet."
- 8n4vidtmkvmk 2y agoIt could also be neither. It's whatever you define it to be. Null could mean you don't know if it's true or if it's false, or it could mean you know it's neither true nor false.
- deleted 2y ago[deleted]
- dominicrose 2y agoA null boolean good very well mean true, if the default value is true (in the code if not in the DB).
- nilamo 2y agoIs this just being pedantic wrt uninitialized values?
- btown 2y ago> quite parasitic This is the mental model that I use and teach: a NULL is a virus that will likely make everything it touches NULL... unless cordoned off by a COALESCE or IFNULL or something else that explicitly speaks about its NULL-sensitive behavior in its documentation. So if you see a bug where a WHERE or FILTER clause is unexpectedly failing, and you're in a time crunch, rather than pounding your head against the wall, start sprinkling COALESCE statements around (while being thoughtful about the domain meaning of what you're doing) and it's more likely than not to solve your problem!
- crazygringo 2y agoThat doesn't address anything in the second half of the post though, starting with this pull quote: > The fact that NULLs are distinct for UNIQUE columns but are indistinct for SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs should be either distinct everywhere or nowhere. And the SQL standards documents suggest that NULLs should be distinct everywhere. Yet as of this writing, no SQL engine tested treats NULLs as distinct in a SELECT DISTINCT statement or in a UNION.
- Aloisius 2y agoMS SQL Server treats NULLs as indistinct for UNIQUE constraints, SELECT DISTINCT and for UNION. Indeed, the sqlite page the pull quote is from says as much.
- hans_castorp 2y ago> MS SQL Server treats NULLs as indistinct for UNIQUE constraints Postgres lets you control that behaviour when creating the constraint (or index)
- swiftcoder 2y agoAlthough only in relatively recent versions. I had to hack around this in TypeOrm, because their Postgres backend hasn't exposed the option yet.
- sitharus 2y agoThis is confusing when you know that NULLs are not comparable, but it makes some sense if you consider the result of distinct/union as the output of a GROUP BY. You can consider everything that's NULL to be part of the same group, all the values are unknown. So NULLs are not comparable but they are part of the same set.
- adammarples 2y agoIf nulls are distinct then group by should not group them together, this just ignores the problem. Why does group by treat them as equal?
- thayne 2y agoIf only it had a name that was more indicative of that, like UNKNOWN, or UNDEFINED or INDERTIMINATE or something.
- dominicrose 2y agoJavascript has both null and undefined and I'm not sure that's a good idea. At least in SQL we only have one of them, but it can mean unknown or it can mean N/A or even false. It's like a joker, what it means depends on how you use it.
- hun3 2y agoOr VBA, which has Empty, Null, and Nothing: https://excelbaby.com/learn/the-difference-between-empty-null-and-nothing-in-vba/ https://excelbaby.com/learn/the-difference-between-empty-nul... (and sometimes Missing)
- hobs 2y agoNo, it's not those other things, that's just using the tool incorrectly. A NULL is definitely "we dont know", not false, not N/A, especially not any known value.
- swiftcoder 2y agoExcept in every other programming language with a null, null is the definite absence of something
- hobs 2y agoSure, and we're talking about SQL nulls in this context, which is why I am strict in my definition.
- dominicrose 2y agoYes and I think that Clojure handles nil pretty well, and it's a functional programming language like SQL. It's also interesting to see that Typescript has added an unknown type. So something that's a value in SQL (null being unknown) is a type in TS.
- remywang 2y agoExcept that NULL is not the same as UNKNOWN! NULL is a data value (like integers) that can appear in data expressions (like NULL + 1) and comparisons (like NULL = 1) whereas UNKNOWN is a truth value that can appear in boolean/logical expressions constructed from logical connectives like AND, OR, NOT. A data expression always evaluates to a data value, and usually whenever any part of the expression is NULL, the entire expression evaluates to NULL. A comparison evaluates to a truth value, and usually when a comparison invovles a NULL it returns UNKNOWN. This leads to weird behaviors where both `SELECT 3 WHERE NULL = NULL;` and `SELECT 3 WHERE NULL <> NULL;` returns nothing (because the query engine does not output a row if the predicate returns UNKNOWN on it). What you listed above only comes into play for boolean/logical connectives like AND, OR, NOT, and in that case we follow 3-valued logic. And there's more annoying corner cases when you deal with DISTINCT. The situation is so hopeless that SQLite has a whole table documenting divergent behaviors of NULL in different systems: https://www.sqlite.org/nulls.html https://www.sqlite.org/nulls.html
- hiAndrewQuinn 2y agoIndeed, they're not identical - that's why I just said "based on", and that's likely why the word UNKNOWN itself isn't used in SQL. Nevertheless I find it a useful intuition pump. I wager that most people reading `UNKNOWN = UNKNOWN` or `UNKNOWN <> UNKNOWN` and thinking about the examples above would stop and say, "Wait, I actually don't know the value of that statement for sure either, since the LHS and the RHS could be completely different things," and would then double check what their SQL dialect would actually do in this situation.
- remywang 2y ago> the word UNKNOWN itself isn't used in SQL That leads to an even more confusing point, that some systems (at least SQLite) overloads NULL to mean UNKNOWN, for example `SELECT 1 WHERE NULL = (NULL = NULL)`. And the dangerous thing about NULLs is not when they are explicitly used in the query as a literal (as I did for brevity), but when they appear in tables. It's perfectly reasonable to assume `SELECT COUNT( * ) FROM t;` should be the same as `SELECT COUNT( * ) from t WHERE t.x = t.x OR t.x <> t.x`, but they are not the same because the latter does not return NULL rows. This has lead to real query optimizer bugs. For more examples see this paper https://dl.acm.org/doi/10.14778/3551793.3551818 https://dl.acm.org/doi/10.14778/3551793.3551818
- gigatexal 2y agoThis is the correct way of thinking about things. Null is one of the hardest things for traditional software engineers in my experience as a guy who came up as a data admin.
- drpossum 2y agoNull in not-SQL (which is most things) usually isn't this tortured and isn't hard.
- rad_gruchalski 2y agoBecause they're two orthogonal problems. It's not like you do select distinct from program variables group by scope in your frontend programming language.
- mathw 2y agoThat's because null in not-SQL is a rather different concept, and while it's pretty easy to understand it's absolutely is hard to actually work with. Hoare didn't call it a "billion dollar mistake" on a whim.
- swiftcoder 2y agoI don't know about that. null/undefined in javascript gives it a pretty good run for it's money
- gigatexal 2y agoYeah the 3 valued logic of SQL trips people up, me too from time to time
- deleted 2y ago[deleted]
- mulmen 2y agoSQL is not three valued. Neither is NULL. BOOLEAN is accused of being three-valued but it has two values and like all values they can be unknown. Similarly a SMALLINT has 65,536 possible values not 65,537.
- tessierashpool9 2y agothere is a pattern starting to emerge here on hackernews of highly voted posts by people who present themselves as experts and thought leaders who shamelessly put their lack of understanding at display. it's frightening.
- lynguist 2y agoLike on Reddit etc, which I deliberately avoid for this reason. The hiding of the vote count and the heavy moderation still help a lot that HN is still a massively better platform than any of its alternatives.
- ozim 2y agoI see it the other way around. People think if someone wrote blog post with technical details and it got upvoted - somehow it has to be an expert.
- tessierashpool9 2y agogo the extra mile and click on about and then check out the linkedin profile. i quote: "I graduated top of my class with a BSc in Computer Science [...]. I have a strong background in software engineering and technical leadership"
- zie 2y agoWho doesn't think of themselves as an expert? That doesn't mean they are one.
- bunderbunder 2y agoWhy not both? In my career, I have met countless people who are experts in programming in general, but with relatively modest skills in database systems. Which is fine! It's really hard to be truly expert in both. There's a reason why "programmer" and "database administrator" used to be two different professions. I'd like to think that I'm better than your average developer at flogging RDBMSes, but most DBAs I've worked with can still run circles around me when it comes to information modeling and database & query optimization.
- zigzag312 2y agoThey are weird because they are inconsistent compared to nearly everything else. Being based on someone's logic is not sufficient. Most weird things are based on some (weird) logic.
- Pxtl 2y ago> SQL NULLs aren't weird, they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! Kleene's TRUE-FALSE-UNKNOWN logic is weird. SQL nulls effectively violate the reflexive property of equality, because X=X does not result in a value of TRUE. And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause. So that means that X=X is effectively FALSE in SQL*. That is a clown language. *(yes, I have the same opinion about ANSI NaN, but that wouldn't come up so often if Javascript didn't love NaN for some stupid reason.)
- tpmoney 2y ago> And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause. I don't think any databases treat `NULL` as `FALSE` in the WHERE clause. `SELECT * FROM foo WHERE bar = NULL` doesn't return rows with a NULL in the bar column. `SELECT * FROM foo WHERE bar != NULL` doesn't return rows without NULL in the bar column. `SELECT * FROM foo WHERE (bar = 'a') = NULL;` doesn't return rows where bar is not equal to `a`[1]. As far as I know every DB treats NULL as what it is, an unknown value. It also doesn't to my mind violate the reflexive property because NULL is not equal to anything. It is a marker for an unknown value, not a value in and of itself. If you have a database of every person in a room and what color shirt they're wearing, and in your database, Alice and Bob both have NULL in their "shirt_color" column, that does not mean that Alice and Bob have the same color shirt. Nor does it mean that they don't have the same color shirt. Nor does it mean that someone with a green colored shirt has the same color shirt as Bob or Alice. It doesn't mean they don't have a shirt either. It means you don't have a record of/don't know what color their shirts are. You can't violate the reflexive property because you can't say what color shirt they have. You're not doing `X = X -> false`, you're doing `X = UNKNOWN -> UNKNOWN` [1]: https://www.db-fiddle.com/f/iVDDRJos1pUqxnuy1jTEEe/0 https://www.db-fiddle.com/f/iVDDRJos1pUqxnuy1jTEEe/0
- Pxtl 2y agoIt treats the NULL/unknown value of the boolean as false 1 <> NULL => Boolean UNKNOWN, so SELECT * FROM foo WHERE 1 <> NULL returns nothing. 1 = NULL => Boolean UNKNOWN, so SELECT * FROM foo WHERE 1 = NULL returns nothing. That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself. > You're not doing `X = X -> false`, you're doing `X = UNKNOWN -> UNKNOWN` That's not how "=" works. If you want a relationship for testing equality than handles unknown, don't call it equality. Basic properties of equality, from Wikipedia https://en.wikipedia.org/wiki/Equality_(mathematics) https://en.wikipedia.org/wiki/Equality_(mathematics) - Reflexivity: for every a, one has a = a. - Symmetry: for every a and b, if a = b, then b = a. - Transitivity: for every a, b, and c, if a = b and b = c, then a = c. edit: We can also see the incoherence of this concept when we look at set theory. Because UNKONWN booleans are neither true or false, if you use them in a WHERE clause you get the ugly result that the set of X ⋃ Xᶜ is not everything.
- tshaddox 2y agoEven simpler, note that the first two examples are essentially just two of the most basic boolean algebra identities. Replace UNKNOWN with a variable and change the notation to the more conventional boolean algebra notation and you've got: 1. x ∧ 1 = 1 (identity law for conjunction) 2. x ∨ 0 = 1 (identity law for disjunction)
- deleted 2y ago[deleted]