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

181–190 of 293 posts

Re: SQL nulls are weird

#181

Post by cglace »

int_19h wrote: post #79
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

Except they don't consistently behave that way. If NULL means "unknown", why do they show up in outer joins, or when you SUM an empty table?

The most annoying is having to order by DESC NULLS LAST to get the largest value from an aggregation.

Re: SQL nulls are weird

#182

Post by ludwik »

Even if we set three-value logic aside for a moment, this behavior of NULL still makes sense intuitively.

The value of NULL in a particular table cell is simply a way to indicate 'no value'. If you want the values in a column to be unique, cases where there are no values shouldn't be considered.

This plays out similarly in practice. For example, you may want to allow users to optionally reserve a username, and if they do, those usernames should be unique. It's hard to imagine a use case where by wanting a field to be both optional (nullable) and unique, you mean that the field should be optional for a single record (!) and required for all the rest. Of course, you mean that IF there is a value, THEN it should be unique.

Re: SQL nulls are weird

#183

Post by marcosdumay »

crazygringo wrote: post #67
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…

That 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 te…

Yes, ergonomics dictates some weird behavior for nulls.

Luckily, Postgres nowadays lets you declare the behavior of each null in unique constraints, like it should be. We can expect this to creep down to the other DBMSs with time.

Making nulls distinct on a "select distinct" or a "group by" would be completely useless.

Re: SQL nulls are weird

#184

Post by jfb »

iefbr14 wrote: post #147

When the null concept was introduced to me in the seventies, the only thing I could say was that it would be causing a lot of unnecessary confusion in the future. If you have missing values in your datarecord then that datarecord belongs in an exception-queue. And now some 45 years later people are still discussing it like we did then..

Sometimes you want UNKNOWN, sometimes you want MISSING.

Re: SQL nulls are weird

#185

Post by bunderbunder »

wodenokoto wrote: post #126
bunderbunder wrote: post #51

Earlier quoted context omitted.

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

There are two values, TRUE and FALSE. Null is not a value, it the the lack of a value. You have a list of people and you ask if they own a car. You didn't get around to asking George, so that, somehow means he owns a car because you are using boolean logic? Or does it mean he doesn't own a car, because you are using boolean logic? No, it means you haven't gathered this data point and don't know.

If there are exactly two possible values, TRUE and FALSE, you're working with Boolean logic.

If there are three possible values, TRUE, FALSE and NULL (unknown), then you're probably working with something like Kleene logic. You can't truly be working with Boolean logic, though, any more than you can be doing integer arithmetic when 15.37 is a member of your domain.

To put it another way, if we're talking about the mathematical definition of boolean algebra and not just some programming language's quirky implementation that happens to be called "bool", then boolean values would by definition be non-nullable. That logic that allows nulls has been implemented using the same unified set of keywords and operator names is a pragmatic decision that simplifies the language implementation and spec, not a principled one that tries to be pedantic about mathematical terminology.

Re: SQL nulls are weird

#186

Post by nilamo »

crazygringo wrote: post #67
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…

That 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 te…

Isn't "select distinct" wildly frowned upon anyway? It's the same as "group by", but with less options...

Re: SQL nulls are weird

#187

Post by nilamo »

mrkeen wrote: post #34
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…

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

Is this just being pedantic wrt uninitialized values?

Re: SQL nulls are weird

#188

Post by Pxtl »

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…

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

Re: SQL nulls are weird

#189

Post by Pxtl »

marcosdumay wrote: post #183
crazygringo wrote: post #67

Earlier quoted context omitted.

That 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 te…

Yes, ergonomics dictates some weird behavior for nulls. Luckily, Postgres nowadays lets you declare the behavior of each null in unique constraints, like it should be. We can expect this to creep down to the other DBMSs with time. Making nulls distinct on a "select distinct" or a "group by" would be completely useless.

If you're compromising on your high-minded and unorthodox purist concept for ergonomics, you may as well finish the job of ergonomics and just use normal nulls where X=X is true.

Re: SQL nulls are weird

#190

Post by GuB-42 »

crazygringo wrote: post #67
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…

That 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 te…

That's because "different" and "distinct" don't mean the same thing.

Two unknown values are assumed to be different, but they are not distinct from each other.

For example, take two boxes, in each box is a die, the value of the box is the value shown on the die inside. You don't know the value since you don't see the die, it may even change as you manipulate the box, so it is unknown, NULL in SQL. Because of that, you assume the two boxes have different values. They are, however, indistinguable, so, not distinct. All you need to know is that you hold two boxes with dices in it, which one you hold doesn't matter, and that's what "SELECT DISTINCT" tells you: that you have two boxes and it doesn't matter which is which, even though they have different values.

Post reply on HN