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

111–120 of 293 posts

Re: SQL nulls are weird

#111

Post by benzayb »

reshlo wrote: post #72
mplanchard wrote: post #46

Earlier quoted context omitted.

There's another comment in here that talks about thinking of NULL as UNKNOWN, and I quite like that. It makes a lot more sense, and I think it would've been a better choice to standardize on. UNDEFINED would also be an improvement.

UNDEFINED would not be accurate. If your signup form has an optional field for a full name which I don’t fill in, I still have a name. Just because a value is not known by your database doesn’t mean it isn’t defined. E. F. Codd thought about this issue.[0] > Codd indicated in his 1990 book The Relational Model for Database Management, Version 2 that the single Null mandated by the SQL standard was inadequate, and sho…

> If your signup form has an optional field for a full name which I don’t fill in, I still have a name. Just because a value is not known by your database doesn’t mean it isn’t defined.

There's the Closed World Assumption in a database.

Re: SQL nulls are weird

#112

Post by benzayb »

solumunus wrote: post #89
gxt wrote: post #83

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.

How would you handle unmatched outer joins?

By having a default value (non-null) for each declared type of those columns.

Or, the user must define a default value in the query itself.

Yes, tedious; but, precise and forces the programmer to really prepare for the "unknown" scenario.

Re: SQL nulls are weird

#113

Post by globular-toast »

thrance wrote: post #48
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…

My experience with ORMs is that most of the time you end up needing to write some queries by hand, in raw SQL. Usually these are the most complex, that you can't express in your ORM's DSL. My point being, I don't think using an ORM really shields you from having to learn how it works behind the scenes.

It's not even about having to write SQL by hand. In an ORM like Django that's exceedingly rare. But you still need to understand what's going on underneath. In other words, it's the most leaky abstraction there is. I think the popularity is mostly aesthetic and convenience. Most people into ORMs like Django don't really know about layered architecture and that you can keep all your SQL in one place in the data access layer. They just scatter that stuff everywhere in the codebase.

Re: SQL nulls are weird

#115

Post by bogeholm »

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…

How about Option for that? In SQL you could have a nullable Boolean column

Re: SQL nulls are weird

#116

Post by cm2187 »

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…

The problem is that in practice in a database NULL is a placeholder for a missing value, not an unknown value.

Re: SQL nulls are weird

#117

Post by feoren »

int_19h wrote: post #80
masklinn wrote: post #42

Earlier quoted context omitted.

Furthermore if null only means unknown then we need a value for “known absent”, there’s a reason why null is so often used as that.

From a purely relational perspective, if some piece of data can be absent, it's a 1:N relation where N (Of course, this is rather awkward in practice, and when NULL is there, it's inevitably going to be used for that instead.)

It is encoded as such. That's why most columns are made nullable. It's crazy to say you need to use the full power of a 1:N relation with some child table when you know N cannot be greater than 1, when a nullable column already exactly encodes a 1:(0..1) relation. I'm not trying to shill for null here: one of null's great problems is exactly the fact that null can represent "unknown", "known absent", "not applicable", or even some sentinel-ish "other" escape condition, each with their own subtle differences in how they should be handled.

Null has tons of problems, of course. But it's patently absurd to claim that you "ought to be" making a different 1-column table with a unique foreign key or shared primary key for every column that might be absent, because of some vague appeal to the fact that you can write it as a 1:N relation with Nnon-nullable column is a 1:N relation where N==1 and "should be encoded as such". It is encoded as such! That's what a column is!

Re: SQL nulls are weird

#118

Post by feoren »

andai wrote: post #92
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…

I remember from my databases course at university that NULL means that the database doesn't contain that data, and empty string means that it is known to be empty.

That's your professor's opinion, and probably one that does not come from industry experience. Look in 4 different databases and you'll see 9 different conventions. A common one is to have all strings non-null with a default value of empty string. And not all columns are strings; there is no "obviously empty" integer or boolean.

Re: SQL nulls are weird

#119

Post by [deleted] »

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.

[deleted]

Re: SQL nulls are weird

#120

Post by feoren »

globular-toast wrote: post #113
thrance wrote: post #48

Earlier quoted context omitted.

My experience with ORMs is that most of the time you end up needing to write some queries by hand, in raw SQL. Usually these are the most complex, that you can't express in your ORM's DSL. My point being, I don't think using an ORM really shields you from having to learn how it works behind the scenes.

It's not even about having to write SQL by hand. In an ORM like Django that's exceedingly rare. But you still need to understand what's going on underneath. In other words, it's the most leaky abstraction there is. I think the popularity is mostly aesthetic and convenience. Most people into ORMs like Django don't really know about layered architecture and that you can keep all your SQL in one place in the data access…

I don't know Django specifically but I'm always floored by how people talk about ORMs. They're only a leaky abstraction if you believe their point is to shield terrified junior devs of the inner workings of the scary relational database. That's an awful way to use ORMs, and the source of most of the flak they get. To be fair, some are designed that way, or at least strongly push you toward it.

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 SQL dynamically, with the full power of your language. SQL is an awful language to write application logic in, because it has horrible support for abstraction, composition, encapsulation, dependency injection, etc. The ORM gives you a way to produce SQL in an environment that actually supports basic software engineering principles. Scattering ORM logic everywhere in the codebase is the point: putting all your SQL in one data access layer is like putting all your arithmetic in one calculation layer. Why would you ever do that? What's wrong with seeing a plus sign in more than one file? What's wrong with seeing language-encoded relational logic in more than one file?

I can guarantee you the popularity is not "aesthetic". And convenience is a real thing that actually does reduce costs. People complain about ORMs, but have you seen the absolute horse-shit-level code that people jam into SQL functions and procedures to do the utterly most basic things? The standard for what ends up in SQL Stored procedures is the most unmaintainable garbage in the entire software engineering ecosystem.

Post reply on HN