Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL nulls are weird

jirevwe.github.io

Post reply on HN
Print view View original

81–90 of 293 posts

Re: SQL nulls are weird

#81

Post by int_19h »

wvenable wrote: post #26
demurgos wrote: post #19

Earlier quoted context omitted.

Just to clarify, I'm not advocating to introduce a new `unknown` keyword. I'm saying that the existing `null` in SQL was not named properly and that the name `unknown` would have been more fitting. SQL's `null` already has the semantics of `unknown` as explained in the part of the article that I quoted.

SQL's use of "null" is probably one of the oldest instances of that concept in computing. It's exactly equivalent to unknown. That is its definition.

SQL NULL is not "exactly equivalent" to unknown. E.g. in an outer join, there's nothing unknown about the result that is missing a row from one side, yet SQL semantics is to fill it with nulls.

In practice, it behaves as "unknown" in some contexts, as "missing value" in other contexts, and sometimes it's just plain WTF like SUM() returning NULL rather than 0 if there are no rows.

Re: SQL nulls are weird

#82

Post by kijin »

lolpanda wrote: post #76

I actually like how NULLs behave in SQL. They mean "I don't know" In the modern programming language we all care about Null safety. But no matter how you model your data, you will always run into the situations when you don't know everything. So I believe NOT NULL is not very practical. NULLs in SQL handle these case very well - when the input is unknown your output is unknown

Agreed. If SQL didn't have NULL, we'd have other special values meaning "I don't know" or "no data" all over the place.

Too many newbies hear that NULL is bad, so they declare all columns as NOT NULL and end up inserting ad hoc values like 0, -1, '', or {} when they inevitably come across cases where they don't have data. Which is even worse than NULL.

Re: SQL nulls are weird

#83

Post by gxt »

This has always made queries unpredictable in many scenarios and it should be a feature to turn nulls off entirely and swap them out with Option instead.

Re: SQL nulls are weird

#84

Post by Spivak »

bdangubic wrote: post #70
Spivak wrote: post #66

Earlier quoted context omitted.

But 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…

The meaning of NULL in general being "value not specified" lends itself pretty nicely to "either true or false." You mean neither true or false?

I 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."

Re: SQL nulls are weird

#85

Post by thayne »

hiAndrewQuinn wrote: post #27

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 UNK…

If only it had a name that was more indicative of that, like UNKNOWN, or UNDEFINED or INDERTIMINATE or something.

Re: SQL nulls are weird

#86

Post by remywang »

hiAndrewQuinn wrote: post #27

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 UNK…

Except 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

Re: SQL nulls are weird

#87

Post by solumunus »

bdangubic wrote: post #70
Spivak wrote: post #66

Earlier quoted context omitted.

But 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…

The meaning of NULL in general being "value not specified" lends itself pretty nicely to "either true or false." You mean neither true or false?

It could be true or false, but it’s unknown. For example. a user doing a survey is yet to fill in the answer. That doesn’t mean there is no answer, it’s just unrecorded.

Re: SQL nulls are weird

#88

Post by 8n4vidtmkvmk »

Spivak wrote: post #84
bdangubic wrote: post #70

Earlier quoted context omitted.

The meaning of NULL in general being "value not specified" lends itself pretty nicely to "either true or false." You mean neither true or false?

I 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."

It 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.

Re: SQL nulls are weird

#90

Post by galaxyLogic »

In Object Oriented Context "null" is useful to indicate that some object doesn't have value for that property.

What's interesting is, do we mean that in our data that attribute has no value? Or do we mean the real-world object represented by the data does not have that attribute?

Does null mean

a) We don't know the value of this attribute for this object, or

b) We do know that there is no value for this attribute in the real-world object represented by our data.

In JavaScript because there is both null and undefined it is easy to assume that undefined means we don't know the value and null means we do know it has no value.

EXAMPLE: The attribute 'spouse'. Some people have a spouse some don't. So what does it mean if the value of the field 'spouse' is null? That we know there is no spouse, or that we don't know who the spouse is if any.

In practical terms we can say null means "We don't know" which includes the case that there is no spouse.

Post reply on HN