Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Learn about SQL three-valued logic before it bites you

twitter.com

Post reply on HN
Print view View original

11–18 of 18 posts

Re: Learn about SQL three-valued logic before it bites you

#11

Post by sattoshi »

eddd-ddde wrote: post #9

I don't find this behaviour crazy or unexpected at all. NULL doesn't refer to any specific 'missing value' state, instead it represents a larger group of possible values of which we don't know the actual value. So in a pile of values 'foo' and 'bar', NULL could mean either of them, so there is no way of knowing if a NULL row is foo or if it is bar. We can't even know if two NULLS are equal.

This reads like you have worked with SQL a lot and has a skewed perspective. As a newcomer to SQL, this could be very surprising given that this diverges from every language that Im aware of having nulls.

Re: Learn about SQL three-valued logic before it bites you

#12

Post by itishappy »

eddd-ddde wrote: post #9

I don't find this behaviour crazy or unexpected at all. NULL doesn't refer to any specific 'missing value' state, instead it represents a larger group of possible values of which we don't know the actual value. So in a pile of values 'foo' and 'bar', NULL could mean either of them, so there is no way of knowing if a NULL row is foo or if it is bar. We can't even know if two NULLS are equal.

    SELECT * FROM Foobar WHERE Val = NULL;
no results

I'm admittedly still learning SQL, but I'm very surprised by this.

The query should be:

    SELECT * FROM Foobar WHERE Val IS NULL;

Re: Learn about SQL three-valued logic before it bites you

#13

Post by steego »

itishappy wrote: post #10
steego wrote: post #7

Earlier quoted context omitted.

While I’m sure NULLs nary be found in the ivory tower, you should know they are pervasive in the rest of the kingdom amongst us common folk.

The comment you're replying to acknowledges this and suggests we plan accordingly.

Even from a “planning” perspective, that comment is about as unconstructive as they come.

How does it suggest we actually go about modeling missing values in the database?

I can think of a dozen ways to go about it and each one has its own tradeoffs and consequences.

When it comes down to it, is actually it worth it? Just so you don’t have to consider NULL scenarios for columns that are marked as nullable?

It seems like a silly overcorrection to me.

Re: Learn about SQL three-valued logic before it bites you

#15

Post by patmorgan23 »

eddd-ddde wrote: post #9

I don't find this behaviour crazy or unexpected at all. NULL doesn't refer to any specific 'missing value' state, instead it represents a larger group of possible values of which we don't know the actual value. So in a pile of values 'foo' and 'bar', NULL could mean either of them, so there is no way of knowing if a NULL row is foo or if it is bar. We can't even know if two NULLS are equal.

Is NULL = 'crazy', True?

If no one would expect NULL != 'crazy' to be True.

That's just intuitive, Null is not the string 'crazy'.

Re: Learn about SQL three-valued logic before it bites you

#16

Post by setr »

eddd-ddde wrote: post #9

I don't find this behaviour crazy or unexpected at all. NULL doesn't refer to any specific 'missing value' state, instead it represents a larger group of possible values of which we don't know the actual value. So in a pile of values 'foo' and 'bar', NULL could mean either of them, so there is no way of knowing if a NULL row is foo or if it is bar. We can't even know if two NULLS are equal.

Which is why these comparisons should be errors, rather than producing gibberish results. Trinary logic stuffed into Boolean operations is just psychotic

Re: Learn about SQL three-valued logic before it bites you

#17

Post by dmz73 »

euroderf wrote: post #2

The definition and use of markers for missing values belongs in the application, not the database. My 0,02€.

It is called Structured Query Language, it allows for complex logic that can be processed close to the data to speed up operations and reduce complexity and increase reliability of client applications. Sure, if you require Google scale of connectivity, it might not work but 99.9999% of people do not and their applications would be simpler, cleaner, more performant and more reliable if they learned and used their database correctly instead of re-implementing all that functionality (but badly) in their application layer.

Re: Learn about SQL three-valued logic before it bites you

#18

Post by Xcelerate »

setr wrote: post #16
eddd-ddde wrote: post #9

I don't find this behaviour crazy or unexpected at all. NULL doesn't refer to any specific 'missing value' state, instead it represents a larger group of possible values of which we don't know the actual value. So in a pile of values 'foo' and 'bar', NULL could mean either of them, so there is no way of knowing if a NULL row is foo or if it is bar. We can't even know if two NULLS are equal.

Which is why these comparisons should be errors, rather than producing gibberish results. Trinary logic stuffed into Boolean operations is just psychotic

Yeah, I agree. The focus here should be the principle of least surprise so your database works correctly. Throw an error to ensure that unintuitive edge cases are handled explicitly. This is SQL after all. It’s used for business purposes, not to demonstrate elegant type systems built around relational algebra.
Post reply on HN