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

261–270 of 293 posts

Re: SQL nulls are weird

#261

Post by tpmoney »

Pxtl wrote: post #239
tpmoney wrote: post #237

Earlier quoted context omitted.

> And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause. I don't think any databases treat `NULL` as `FALSE` in the WHERE clause. `SELECT * FROM foo WHERE bar = NULL` doesn't return rows with a NULL in the bar column. `SELECT * FROM foo WHERE bar != NULL` doesn't return rows without NULL in the bar column. `SELECT * FROM foo WHERE (bar = 'a') = NULL;` doesn't return rows w…

It treats the NULL/unknown value of the boolean as false 1 NULL => Boolean UNKNOWN, so SELECT * FROM foo WHERE 1 NULL returns nothing. 1 = NULL => Boolean UNKNOWN, so SELECT * FROM foo WHERE 1 = NULL returns nothing. That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself. > You're not doing `X = X -> false`, you're doing `X = UNKNOWN -> UNKNOWN` That's not how "=" works. If you wan…

> That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself.

I think the difference between how we're looking at this is for me there is no "value" of NULL. NULL has NO value which is why you can't do `1 = NULL` or `1 NULL` and have to specifically use `1 IS NOT NULL` or `1 IS DISTINCT FROM NULL`

>That's not how "=" works. If you want a relationship for testing equality >[that] handles unknown, don't call it equality. >Basic properties of equality, from Wikipedia >https://en.wikipedia.org/wiki/Equality_(mathematics) >- Reflexivity: for every a, one has a = a. >- Symmetry: for every a and b, if a = b, then b = a. >- Transitivity: for every a, b, and c, if a = b and b = c, then a = c.

Sure, that's all well and good, but equality only can work IF you know what the values are on either side. NULL means you don't know what a value is and therefore you can't answer whether or not a = NULL because NULL isn't known yet.

Or let me put it another way, is the launch price of the iPhone 17 != $799? The answer is neither true nor false, because the launch price of the iPhone 17 is unknown. We can make reasonable guesses, but if I give you a database with entries for iPhones from the first model and placeholder rows for the 17, 18, 19 and 20 models, none of them belong in the list when someone asks "which iPhones cost $799 at launch?" But equally true that none of them belong in the list when someone asks "which iPhones did not cost $799 at launch?"

Re: SQL nulls are weird

#262

Post by rawgabbit »

gigatexal wrote: post #248
mulmen wrote: post #233

Earlier quoted context omitted.

It’s not. Your link makes the same mistake I already addressed. It conflates nullable booleans with tri-state logic. Null is not a value. It is the absence of a value. > The SQL null value basically means “could be anything”. This is wrong. Null means it could be any valid value but that value is unknown. If the datatype is DATE then the value cannot be boolean TRUE or the string ‘purple’.

How is that different than “anything”? If I’m comparing a value of type date to a null I still think it works as it should if value is “unknown”. What greater insight or context do we have if it’s a small-int null?

Suppose you have table with two columns to represent a classroom’s exam scores. It has the columns student_id (varchar) and score (int).

If a student is sick and has not taken the exam, yes you could enter -99 to represent they did not take the test. But if you want to find the class average, you would have to do something like this:

select average(case when score =-99 then null else score end) as class_avg from …

Or you could have entered null to begin with.

Re: SQL nulls are weird

#263

Post by rawgabbit »

recursive wrote: post #259
wvenable wrote: post #249

Earlier quoted context omitted.

Ah but then how do you record that the measurement actually happened but did not produce a value? I want a record of that failure. I mean sure, you could do yet another table. But honestly that level of normalization is much more work than it's worth. Just because it's some academic definition doesn't make it right.

You put a record in the Measurement table, and none in the Value table. > But honestly that level of normalization is much more work than it's worth Yes. I question whether it's worth anything to begin with.

     Select M.*, V.* from Measurement as M left outer join Value_table as V…
You end up with nulls again.

Re: SQL nulls are weird

#264

Post by DangitBobby »

izacus wrote: post #229
daswerth wrote: post #177

Earlier quoted context omitted.

The idea that someone should refrain from publishing a blog post about _anything_ unless they are a certifiable expert is not reasonable. Many people (correctly) write to learn, and even if they are publishing just to "present themselves as experts", it's on the reader to determine value.

In a world filled with false bullshit, crating more false unchecked writing instead of educating yourself is not a benefit to anyone.

What was false in the article?

Re: SQL nulls are weird

#265

Post by [deleted] »

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…

[deleted]

Re: SQL nulls are weird

#266

Post by [deleted] »

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.

[deleted]

Re: SQL nulls are weird

#267

Post by DangitBobby »

iefbr14 wrote: post #147

When the null concept was introduced to me in the seventies, the only thing I could say was that it would be causing a lot of unnecessary confusion in the future. If you have missing values in your datarecord then that datarecord belongs in an exception-queue. And now some 45 years later people are still discussing it like we did then..

Missing values are not always an exception. There's a reason modern languages almost universally include an Option type which may contain a Null and allow you to pass it around as a first class value. Good data representations allow you to express missing values.

Re: SQL nulls are weird

#268

Post by DangitBobby »

hot_gril wrote: post #108
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…

If you can avoid learning SQL or the underlying DBMS then great, I have no problem with people ignoring things that are ignore-able. Problem is you can't. You will need to learn the DBMS, and the ORM will end up just getting in the way of what you really want to do.

I've used ORMs extensively in my career, and I've seen it trotted out as a Boogeyman here a million times. Why do I still prefer writing queries with a good ORM over awkwardly composing SQL by conditionally concatenating strings? Is buggy, error prone string concatenation with a bunch of if statements the true way? What am I missing?

Re: SQL nulls are weird

#269

Post by DangitBobby »

mnsc wrote: post #100
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…

But in a _relational_ database lack of spouse would not be modeled with a nullable column "spouse" but rather an absence of a spouse row/relation. Which is very real-world-like.

And yet when you do a join because you need to actually use that data, the resulting table will have a column with nulls in it. Any way you squeeze it, you need a way to represent empty values for your database to be useful.

Re: SQL nulls are weird

#270

Post by [deleted] »

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.

[deleted]
Post reply on HN