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

211–220 of 293 posts

Re: SQL nulls are weird

#211

Post by SigmundA »

dragonwriter wrote: post #206
SigmundA wrote: post #205

Earlier quoted context omitted.

>That's because "different" and "distinct" don't mean the same thing. The literal definition distinct is: >recognizably different in nature from something else of a similar type. If you want to get down to it nothing is "equal" or the same. Is a temperature measurement 25C the same as another of 25C? No these measurements are an approximation of the actual values which are actually not equal to each other they are di…

> Is a temperature measurement 25C the same as another of 25C? Yes, the measurements are the same. The actual temperatures probably are not, but measurements are not the same as the thing measured.

>Yes, the measurements are the same.

By the logic two unknown (null) measurements are the same regardless of the actual value which I agree with.

Re: SQL nulls are weird

#212

Post by rqtwteye »

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…

"compose relational queries dynamically"

That's an important one. It would be super nice to have a SQL dialect that works more like LINQ where you can compose your queries easily. I always hate it when I have to write SQL directly. It's super powerful but the syntax just isn't designed well. To me it feels like a throwback to the good old FORTRAN or COBOL days: you can get stuff done but modern languages are so much better.

Re: SQL nulls are weird

#213

Post by buttercraft »

SigmundA wrote: post #205
GuB-42 wrote: post #190

Earlier quoted context omitted.

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, yo…

>That's because "different" and "distinct" don't mean the same thing. The literal definition distinct is: >recognizably different in nature from something else of a similar type. If you want to get down to it nothing is "equal" or the same. Is a temperature measurement 25C the same as another of 25C? No these measurements are an approximation of the actual values which are actually not equal to each other they are di…

> The literal definition distinct is

Irrelevant. What matters is the meaning in the context of SQL.

> weird and inconsistent and a waste of time. For all the language bugs due to the existence of null

There are necessary, semantic cases that need to be dealt with. How else would you do it?

Also, it's really weird to use "bugs" to refer to well defined and well documented behavior.

Re: SQL nulls are weird

#214

Post by dragonwriter »

SigmundA wrote: post #211
dragonwriter wrote: post #206

Earlier quoted context omitted.

> Is a temperature measurement 25C the same as another of 25C? Yes, the measurements are the same. The actual temperatures probably are not, but measurements are not the same as the thing measured.

>Yes, the measurements are the same. By the logic two unknown (null) measurements are the same regardless of the actual value which I agree with.

An unknown measurement isn't a measurement value its a statement of (lack of) knowledge about a measurement, that doesn't tell you what the measurement is. Knowledge about a measurement is as different from the measurement as the measurement itself is from the thing measured.

Whether two unknown measurements are the same is unknown.

Re: SQL nulls are weird

#215

Post by fauigerzigerk »

zzzeek wrote: post #170
fauigerzigerk wrote: post #159

Earlier quoted context omitted.

>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

All ideas that were popular for a while are basically guaranteed to be in some successful applications. That includes bad ideas.

Re: SQL nulls are weird

#216

Post by recursive »

chongli wrote: post #137
recursive wrote: post #69

Earlier quoted context omitted.

In nth normal form, you can't have 'no value'. That would mean your model is wrong. In academic relational data books, null does mean "unknown". There is a value, we just don't know what it is (yet). If there might actually not be such a value, you're supposed to change your schema to reflect that.

What happens if your data is produced by some automated process such as a sensor reading and occasionally the sensor fails to return a value? NULL seems exactly the appropriate value to use.

Then you're supposed to use another table with a foreign key to canonical measurement record. This is the concept of fully normalized schemas.

What you're describing is closer to how people do it in practice.

Re: SQL nulls are weird

#217

Post by GuB-42 »

SigmundA wrote: post #205
GuB-42 wrote: post #190

Earlier quoted context omitted.

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, yo…

>That's because "different" and "distinct" don't mean the same thing. The literal definition distinct is: >recognizably different in nature from something else of a similar type. If you want to get down to it nothing is "equal" or the same. Is a temperature measurement 25C the same as another of 25C? No these measurements are an approximation of the actual values which are actually not equal to each other they are di…

Changing the emphasis.

> recognizably different in nature from something else of a similar type.

But anyways, the point wasn't to justify the choices of SQL but rather as a way to make intuitive sense of its logic. SQL is one of the oldest and most successful programming languages in existence, we are not going to change it, and it is not going to disappear anytime soon, so we have to go with it, like it or not. There have been some attempts at alternatives, both at changing the paradigm (NoSQL) and at cleaning up the language, which, to be fair, would be a good thing, but without much success. The relational paradigm just works, and SQL is usable enough to make the cost of switching not worth it.

Edit:

And writing things like "value=param or (param is null and value is null)" is usually the sign of a poor understanding of the NULL logic. You are using it wrong basically. Sometimes, it is the right thing to do, but if that pattern starts appearing all over the place, it is usually a result of thinking "NULL is broken, I have to use this pattern to handle NULL properly". That's cargo culting, don't fix problems you don't understand by copy-pasting code you don't understand.

Note: this is not addressed to "you" in particular, there can be good reasons, no offense intended. But I think that in general, it is a code smell.

Re: SQL nulls are weird

#218

Post by AlienRobot »

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…

It's possible that this is due to the underlying implementation.

In a unique column normally you'll have an index, so NULL becomes a special value in an index, but in SELECT DISTINCT you probably won't have an index, which means a full scan is performed, then every row has to be compared with every other row.

Re: SQL nulls are weird

#219

Post by SigmundA »

dragonwriter wrote: post #214
SigmundA wrote: post #211

Earlier quoted context omitted.

>Yes, the measurements are the same. By the logic two unknown (null) measurements are the same regardless of the actual value which I agree with.

An unknown measurement isn't a measurement value its a statement of (lack of) knowledge about a measurement, that doesn't tell you what the measurement is. Knowledge about a measurement is as different from the measurement as the measurement itself is from the thing measured. Whether two unknown measurements are the same is unknown.

Whether two measurements of 25C are the same is unknown, these are just values recorded in a database. 25 is a value, null is a value.

The values in the db are the same in both cases which is what I would like my db language to deal with and not make assumptions about what that value actually means.

I see no value in treating null special when in comes to equality in a sql db, in fact it is a hinderance that it does so in my experience.

Re: SQL nulls are weird

#220

Post by SoftTalker »

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…

If you're including possibly NULL columns in a distinct or group by and you want to treat them in a particular way, use the COALESCE() or NVL() or whatever similar function to give a real value to the NULL for that purpose.
Post reply on HN