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

241–250 of 293 posts

Re: SQL nulls are weird

#241

Post by tanelpoder »

mulmen wrote: post #201
nilamo wrote: post #186

Earlier quoted context omitted.

Isn't "select distinct" wildly frowned upon anyway? It's the same as "group by", but with less options...

It’s not and it’s not, respectively.

Yeah nothing wrong with “select distinct” itself if it’s used correctly for its intended reasons.

But when I see select distinct at the start of a big and complex query, I do immediately suspect that the developer might have missed some join condition down the line and “got too many rows” back from the query. And since the rows look like duplicates due to the missing join predicate, for a junior (or careless) developer, the quick “solution” is to just apply distinct in the top level query to get rid of these pesky duplicates, declare success and move on.

Re: SQL nulls are weird

#242

Post by Izkata »

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…

> In JavaScript because there is both null and undefined it is easy to assume that undefined means we don't know the value and null means we do know it has no value.

Javascript objects have two kinds of undefined that are both represented by the same value. You have to use another method to see which it is, and I've seen "foo" in this example used for the same thing as "null" in your example:

  >> z = {foo: undefined}
  Object { foo: undefined }

  >> z.foo
  undefined

  >> z.bar
  undefined

  >> z.hasOwnProperty('foo')
  true

  >> z.hasOwnProperty('bar')
  false
This is something you have to account for because the key is still there if you try to remove a value by just setting it to undefined:

  >> Object.keys(z)
  Array [ "foo" ]

  >> for (let k in z) { console.info(k); }
  foo
This is the right way to remove the key:

  >> delete z.foo
  true

  >> z
  Object {  }

  >> Object.keys(z)

Re: SQL nulls are weird

#243

Post by munk-a »

buttercraft wrote: post #213
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…

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

I wanted to briefly reinforce this point with the fact that SQL has multiple equality operators - there is both `=` and `IS NOT DISTINCT FROM`. The later operator will treat null values as equal in alignment with the `DISTINCT` and `DISTINCT ON` operators.

It is extremely easy using partial uniques and the proper operators to treat nulls as non-distinct values and the approach we have allows some very important functionality that aligns with standard statistical data treatment which was influential to how SQL logic was originally designed.

Re: SQL nulls are weird

#244

Post by hiAndrewQuinn »

Tostino wrote: post #209
bunderbunder wrote: post #193

Earlier quoted context omitted.

Why not both? In my career, I have met countless people who are experts in programming in general, but with relatively modest skills in database systems. Which is fine! It's really hard to be truly expert in both. There's a reason why "programmer" and "database administrator" used to be two different professions. I'd like to think that I'm better than your average developer at flogging RDBMSes, but most DBAs I've wor…

At a lot of companies, there are still full teams of people slinging t-sql or pl/SQL all day long to support their organization. Not DBAs, just developers who primarily work inside the database system their entire life.

I keep my old SQL Server Anki cards alive for just such a use case. It's been a minute since I had to jump into a 3-digit-LOC SQL script that does some arcane financial processing or what have you, but there's a nice steady niche there in case I ever want to throw my hat back into the ring.

Re: SQL nulls are weird

#245

Post by Pxtl »

dragonwriter wrote: post #240
Pxtl wrote: post #188

Earlier quoted context omitted.

> SQL NULLs aren't weird, they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! Kleene's TRUE-FALSE-UNKNOWN logic is weird. SQL nulls effectively violate the reflexive property of equality, because X=X does not result in a value of TRUE. And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause. So that means that X=X is effectively FALSE in SQL*. That is a clown langua…

> And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause. NULL is not equivalent to FALSE, it is neither FALSE nor TRUE. It has the same effect as FALSE as the final result of evaluating a WHERE clause condition only because WHERE clause conditions allow a row to be included only when they evaluate strictly to TRUE. But if NULL were equivalent to FALSE in a WHERE clause, th…

No, because NOT NULL/UNKNOWN is still NULL/UNKNOWN.

I realized earlier I was using the term NULL, but going forwards let's use the ANSI SQL concept where the null state of a Boolean is called UNKNOWN. You'll have to forgive me for using the term NULL this far, but in my defense the concept of NULL column-values and UNKNOWN boolean expression results are pretty intertwingled.

    SELECT * FROM foo WHERE NOT (1 = NULL)
resolves to

    SELECT * FROM foo WHERE UNKNOWN
which is the same value that

    SELECT * FROM foo WHERE 1 = NULL
resolves to.

So the WHERE clause is treating UNKNOWN/NULL as equivalent to false. The rest of the Boolean algebra is not.

SQL likes to have it both ways. Sometimes UNKNOWN/NULL is equivalent to false sometimes it's not.

It does this because UNKNOWN/NULL Booleans are incredibly inconvenient and tedious and sometimes they'd rather not think about them.

I'd like to do that as well, but this hoary old language won't give me the same permission it gives itself.

Re: SQL nulls are weird

#246

Post by Dylan16807 »

SigmundA wrote: post #227
shawnz wrote: post #224

Earlier quoted context omitted.

The SQL null is a database-specific keyword and not something that's part of the domain of your measurements. If you want some kind of sentinel value for your measurements that doesn't have the "unknown" meaning of SQL null, then you should use your own sentinel value and not reuse null for that purpose

Sentinel values suck especially when the language already has a perfectly good one built in. Is 0 for a temp measurement unknown sentinel or an actual measurement, how about 2,147,483,647 great probably not a measurement now its always included in greater than queries same with max negative with less than. Null separates the value into its own distinct group and prevents it from being including in range queries due t…

Null is not your value that the database is making assumptions about, it's the database's value that you are making assumptions about.

A real sum type would be nice, but when you're using null then you need to accept that null was not designed with your specific use case in mind.

Re: SQL nulls are weird

#247

Post by ozim »

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.

In a world where there was less false bullshit people believed smoking is fine and sugar is healthy.

Amount of false bullshit doesn’t make qualitative difference.

Only difference to make is that people should not take something as truth just because it is written in a book or in a blog post or if person has a degree or not.

Re: SQL nulls are weird

#248

Post by gigatexal »

mulmen wrote: post #233
gigatexal wrote: post #232

Earlier quoted context omitted.

It’s not? https://modern-sql.com/concept/three-valued-logic

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?

Re: SQL nulls are weird

#249

Post by wvenable »

recursive wrote: post #216
chongli wrote: post #137

Earlier quoted context omitted.

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.

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.

Re: SQL nulls are weird

#250

Post by wvenable »

recursive wrote: post #69
wvenable wrote: post #55

Earlier quoted context omitted.

I wouldn't necessarily define `null` as "unknown" -- it's just "no value" -- which is really the same thing and also somewhat equivalent to "unset". But null pointers aren't unset as pointers aren't initialized to null in C and you can explicitly set a pointer to null. E.F. Codd added nulls to relational model in 1970 so that does pre-date C. The concept is even older than that I imagine.

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.

> There is a value, we just don't know what it is (yet).

In all my years, I've never used null for that. If I don't have a value yet then generally I'm not writing any part of the record. I only ever use nulls for the absence of a value. Creating a new table for every potential optional column is the "academic relational way" and also insane. :)

Post reply on HN