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

121–130 of 293 posts

Re: SQL nulls are weird

#121

Post by feoren »

bunderbunder wrote: post #32

> ... and this is even less obvious if you’re used to using ORMs. Which is why I continue to be such an ORM skeptic. I agree that they're convenient. But I do worry that we've now got an entire generation of engineers who regularly interact with relational databases, but have largely been spared the effort of learning how they actually work. As another commenter pointed out, if you've learned basic relational algebra…

Stop thinking of ORMs as trying to hide the details of SQL and you'll stop hating them. Instead think of them as a way to compose relational queries dynamically, with the full power of your primary language, instead of inside of database stored procedures in a language totally devoid of any support for basic software engineering best practices. They shouldn't be hiding SQL from your primary language, they should be exposing the relational model to it! SQL is not the only possible implementation of the relational model, and it's not even a particularly good one. Even SQL's founders don't think it implements EF Codd's relational model very faithfully. Good ORMs act as a domain-specific language for the relational model embedded inside the parent language.

Re: SQL nulls are weird

#122

Post by ChoHag »

labster wrote: post #77
magicalhippo wrote: post #25

Earlier quoted context omitted.

The result of comparisons involving NULL values can result[1][2] in UNKNOWN, and in PostgreSQL for example you can test[3] for this using IS UNKNOWN. That said, as someone self-taught in SQL, I agree NULL was not a good choice. Replacing NULL with UNKNOWN and the third boolean value as INDETERMINATE for example would have been better. [1]: https://stackoverflow.com/a/79270181 [2]: https://learn.microsoft.com/en-us/sq…

SQL was developed in the 1970s, there’s no way they’d waste all those bytes to spell out UNKNOWN and INDETERMINATE.

[dead]

Re: SQL nulls are weird

#123

Post by magicalhippo »

otteromkram wrote: post #58
magicalhippo wrote: post #25

Earlier quoted context omitted.

The result of comparisons involving NULL values can result[1][2] in UNKNOWN, and in PostgreSQL for example you can test[3] for this using IS UNKNOWN. That said, as someone self-taught in SQL, I agree NULL was not a good choice. Replacing NULL with UNKNOWN and the third boolean value as INDETERMINATE for example would have been better. [1]: https://stackoverflow.com/a/79270181 [2]: https://learn.microsoft.com/en-us/sq…

Also self-taught SQLer and I don't have an issue with NULL. I also don't use UNIQUE constraints, so maybe that has something to do with it.

I don't have an issue as such, I was a fairly experienced developer first time I had to dabble with SQL, but sometimes it can still surprise.

For example I learned the hard way that the DB we use at work does not index NULL values.

And once in a while if I'm tired or stressed I might forget about UNKNOWN and thus that "Col 42" does not return rows where Col is NULL.

Not that better naming would prevent such surprises, but I still think the current naming is less than optimal from a pedagogical perspective. At least I see this at times when teaching our support folks SQL (many have domain background and not a technical background).

Re: SQL nulls are weird

#124

Post by gigatexal »

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…

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

Re: SQL nulls are weird

#125

Post by hans_castorp »

Aloisius wrote: post #71
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…

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

> MS SQL Server treats NULLs as indistinct for UNIQUE constraints

Postgres lets you control that behaviour when creating the constraint (or index)

Re: SQL nulls are weird

#126

Post by wodenokoto »

bunderbunder wrote: post #51
foobarchu wrote: post #43

Earlier quoted context omitted.

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

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.

Re: SQL nulls are weird

#127

Post by afiori »

mnsc wrote: post #100
galaxyLogic wrote: post #90

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…

But in a _relational_ database lack of spouse would not be modeled with a nullable column "spouse" but rather an absence of a spouse row/relation. Which is very real-world-like.

As a sort of challenge I had an idea of building an app using SQL as a pseudo ECS system where every table was either a 1 column table with only an id or a 2 column table with an id and a value.

Re: SQL nulls are weird

#128

Post by dominicrose »

thayne wrote: post #85
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.

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

Re: SQL nulls are weird

#129

Post by afiori »

dalton_zk wrote: post #78
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

I feel like the same, Null equal null is null is totally right

I feel like a select for:

- col1 = 1 should not return NULLS

- !(col1 = 1) should return NULLS

- col1 1 should not return NULLS

Re: SQL nulls are weird

#130

Post by jampekka »

duncan-donuts wrote: post #13
demurgos wrote: post #2

> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them. Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword. Note also that since Postgresql 15, y…

Introducing “unknown” feels like another kind of hell like undefined in JavaScript.

JavaScript's undefined is great. It's sort of similar to a maybe monad. Or IEEE 754 NaN. JS could have nicer mechanisms to handle undefined though.
Post reply on HN