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

161–170 of 293 posts

Re: SQL nulls are weird

#161

Post by zo1 »

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.

Let's also all be reminded about how Oracle DB doesn't let you insert empty strings, and instead treats them as NULLS even if you gave it an empty string initially.

https://stackoverflow.com/questions/203493/why-does-oracle-9...

That was a fun bug to find out, after having dealt with quite a few other DBs over the years. It was one of those "No, but surely" and "This can't be! This is Oracle!" moments. Found it while porting some old code that needed to store an empty string as being distinct from a NULL in that same column.

Re: SQL nulls are weird

#162

Post by adammarples »

sitharus wrote: post #74
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…

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

If nulls are distinct then group by should not group them together, this just ignores the problem. Why does group by treat them as equal?

Re: SQL nulls are weird

#163

Post by mathw »

drpossum wrote: post #138
gigatexal wrote: post #124

Earlier quoted context omitted.

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.

Null in not-SQL (which is most things) usually isn't this tortured and isn't hard.

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

Re: SQL nulls are weird

#164

Post by zo1 »

irrational wrote: post #5

I expected the article to mention how in Oracle NULLs are equal to empty strings. Now that is weird.

It's weirder. If you insert an empty string into a VARCHAR field in Oracle, it returns Null back to you when you query that same field. At the very least, I'd expect a software system to behave in a deterministic way. I.e. either throw an error because you're not doing something right (whatever Oracle deems right in this case), or give you back what you gave it, especially for database software who's entire role of existence is to persist data without side-effects.

Re: SQL nulls are weird

#165

Post by hobs »

tessierashpool9 wrote: post #134
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…

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

I think that's more a pattern of your understanding growing over time.

Most technical writing is actually at the start of the technical journey, there's more people there and its more interesting to talk about strategy and ideas when its not "messy" with internal details and trade offs you make.

I encourage folks at any technical level to write, and I encourage folks to check authors work with a critical eye no matter how long the source has been in the industry, no amount of experience can protect you from being wrong.

Re: SQL nulls are weird

#166

Post by hobs »

dominicrose wrote: post #128
thayne wrote: post #85

Earlier quoted context omitted.

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.

No, 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.

Re: SQL nulls are weird

#167

Post by trollbridge »

And SQL null shares the feature with many other languages that any type can be a NULL (although a column can be set NOT NULL). Much like Java, it is no end of grief that a type that claims to be, say, a “NUMBER” is actually “NUMBER | NULLType”.

Re: SQL nulls are weird

#168

Post by getnormality »

adammarples wrote: post #162
sitharus wrote: post #74

Earlier quoted context omitted.

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

If nulls are distinct then group by should not group them together, this just ignores the problem. Why does group by treat them as equal?

It doesn't treat them as equal, it treats them as one group. It does this because the result is more useful.

It is not the case that nulls are always the same as one another. It is also not the case that nulls are always distinct from each other. Thus, the normal rule of grouping, that same values are combined and distinct values make different groups, does not apply. Another principle is needed to determine the result of grouping with nulls.

Logic which allows for an unknown value can't be shoehorned into always giving definite true or false answers.

Re: SQL nulls are weird

#169

Post by zzzeek »

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…

I was actually going to comment on how breathtakingly ignorant the blog post author was for making this statement. What on earth ORM would make a UNIQUE constraint against multiple columns without an explicit instruction, much less where some of the columns are NULLable and then leave the user entirely unaware that anything like that was happening? ORMs do nothing like this and the developer would always have built this constraint explicitly and therefore is working at the DDL layer. ORMs have nothing to do with whether or not someone knows SQL just like cars have nothing to do with whether or not someone knows how to walk.

Re: SQL nulls are weird

#170

Post by zzzeek »

fauigerzigerk wrote: post #159
feoren wrote: post #121

Earlier quoted context omitted.

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

>They shouldn't be hiding SQL from your primary language, they should be exposing the relational model to it! But this has never been their primary purpose and it's not what they are good at. ORMs are supposed to map the relational model into an object oriented model so that you can work with objects rather than sets of tuples. And that's exactly how people use them. ORMs incentivise people to replace simple and decl…

> ORMs are just a terrible idea - conceptually messy, hard to debug and optimise, full of needless complexity.

and that's why ORMs are so unpopular and entirely absent from successful production applications for the past 30 years

Post reply on HN