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

151–160 of 293 posts

Re: SQL nulls are weird

#151

Post by [deleted] »

feoren wrote: post #117
int_19h wrote: post #80

Earlier quoted context omitted.

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

[deleted]

Re: SQL nulls are weird

#152

Post by bregma »

PhilipRoman wrote: post #104
chuckadams wrote: post #38

Earlier quoted context omitted.

Even null in programming languages isn't so bad if it's a distinct type. The problem with null in languages like Java is that null is part of every reference type (C's pointers are another world of broken, null being basically just another unsafe invalid address). Most languages nowadays do get nulls right, even PHP of all things.

>unsafe invalid address Ironically NULL is probably the safest pointer value in C, as any dereferences from it (and thousands of pages that follow it) are guaranteed to crash.

Well, that's a problem on many of the devices I've seen: zero is a valid memory address and dereferencing it does not cause any kind of crash. In fact some hardware requires reading or maybe even writing to that address. In an age of virtual memory there's no reason why zero should cause a crash and it wastes an entire page of memory for every application to make that happen, if it does.

Re: SQL nulls are weird

#153

Post by xd »

To me, "Unknown" almost implies the possiblity of a value, whereas I've always thought of NULL as being an absence of a value.

edit: an empty string, false, 0 are all values.

Re: SQL nulls are weird

#154

Post by zigzag312 »

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…

They are weird because they are inconsistent compared to nearly everything else.

Being based on someone's logic is not sufficient. Most weird things are based on some (weird) logic.

Re: SQL nulls are weird

#155

Post by rad_gruchalski »

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.

Because they're two orthogonal problems. It's not like you do select distinct from program variables group by scope in your frontend programming language.

Re: SQL nulls are weird

#156

Post by PhilipRoman »

bregma wrote: post #152
PhilipRoman wrote: post #104

Earlier quoted context omitted.

>unsafe invalid address Ironically NULL is probably the safest pointer value in C, as any dereferences from it (and thousands of pages that follow it) are guaranteed to crash.

Well, that's a problem on many of the devices I've seen: zero is a valid memory address and dereferencing it does not cause any kind of crash. In fact some hardware requires reading or maybe even writing to that address. In an age of virtual memory there's no reason why zero should cause a crash and it wastes an entire page of memory for every application to make that happen, if it does.

I haven't had the chance to work on any MMU-less devices, but I don't quite follow your remark about wasting a page. Crashing is just the default behaviour in the absence of a mapping and consumes no resources.

Re: SQL nulls are weird

#157

Post by mrighele »

mrkeen wrote: post #146
wodenokoto wrote: post #126

Earlier quoted context omitted.

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.

> No, it means you haven't gathered this data point and don't know. This is how it should be. > Somehow means he owns a car because you are using boolean logic? This is how it unfortunately is. There are 3 people, and there are 3 people who don't have a NULL car. Therefore George has a car. CREATE TABLE people(name text, carId uuid); INSERT INTO people values('Bill', '40c8a2d7-1eb9-40a9-b064-da358d6cee2b'); INSERT IN…

> There are 3 people, and there are 3 people who don't have a NULL car.

This is not what you are asking with your query: as someone else stated, NULL is meant to be "UNKNOWN", or "it could be any valid value".

So nothing is ever equal to something that can be anything, because even another NULL (i.e. unknown) value is in general different.

So in the line

    SELECT name FROM people WHERE carId = NULL
the condition will always be false. Now if instead if meant to search for the rows where carId is actually unknown you have to write

    SELECT name FROM people WHERE carId is NULL
And your query will return as one may expect 2.

Re: SQL nulls are weird

#158

Post by bballer »

cm2187 wrote: post #116
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.

And to further apply semantics to this just to be snide, the value is only "missing" if it could possibly ever be defined for that tuple. There are cases where you expect the value to be "missing", and it not being "missing" would be considered a data integrity issue. Fun.

Re: SQL nulls are weird

#159

Post by fauigerzigerk »

feoren wrote: post #121
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 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 declarative set operations with complex procedural code operating on individual objects.

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

Re: SQL nulls are weird

#160

Post by getnormality »

I don't see why this is weird. Unique means no duplicates. Nulls can't be duplicates of each other because they're not equal to each other.

If you don't like null semantics, you're free to use sentinel values. You can make all the sentinel values the same, or you can make them all different. Either way, you or someone who has to use your system will be back here tomorrow complaining about how weird it is.

Post reply on HN