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

171–180 of 293 posts

Re: SQL nulls are weird

#171

Post by drzaiusx11 »

Ah yes, someone discovering the existence of three value logic in SQL and expecting 2VL behavior. Classic. We've all been there, right?

Personally I wish more languages were like python or ruby and had chosen None or Nil over Null which alleviates the confusion a bit, as those names better indicates that it's NOT an "unknown" (1 unknown value != 1 other unknown, which intuitively makes sense.) In ruby or python it's more obvious that None and Nil are "nothing" types and therefore equivalence makes sense (nil == nil, None == None are both true)

Re: SQL nulls are weird

#172

Post by globular-toast »

feoren wrote: post #120
globular-toast wrote: post #113

Earlier quoted context omitted.

It's not even about having to write SQL by hand. In an ORM like Django that's exceedingly rare. But you still need to understand what's going on underneath. In other words, it's the most leaky abstraction there is. I think the popularity is mostly aesthetic and convenience. Most people into ORMs like Django don't really know about layered architecture and that you can keep all your SQL in one place in the data access…

I don't know Django specifically but I'm always floored by how people talk about ORMs. They're only a leaky abstraction if you believe their point is to shield terrified junior devs of the inner workings of the scary relational database. That's an awful way to use ORMs, and the source of most of the flak they get. To be fair, some are designed that way, or at least strongly push you toward it. Stop thinking of ORMs a…

Are you thinking of something like LINQ or SQLAlchemy Core? You do not need to use an ORM to interface with a SQL database with basic software engineering principles.

The reason for a data layer is because the underlying data representation might change. For example, you might change the schema to handle some new performance requirement. Now you have to hunt down and change everywhere you've queried that table in your whole code base. Every time you directly call SQL you are coupling your code strongly to the database schema which is ultimately an implementation detail.

> And convenience is a real thing that actually does reduce costs.

I know convenience is a real thing. I also know that it very often increases costs in the long run.

Re: SQL nulls are weird

#173

Post by swiftcoder »

hans_castorp wrote: post #125
Aloisius wrote: post #71

Earlier quoted context omitted.

MS SQL Server treats NULLs as indistinct for UNIQUE constraints, SELECT DISTINCT and for UNION. Indeed, the sqlite page the pull quote is from says as much.

> MS SQL Server treats NULLs as indistinct for UNIQUE constraints Postgres lets you control that behaviour when creating the constraint (or index)

Although only in relatively recent versions. I had to hack around this in TypeOrm, because their Postgres backend hasn't exposed the option yet.

Re: SQL nulls are weird

#174

Post by criloz2 »

It is not supposed that null is the bottom value in the universe of all the values that your program can recognize? Why people need to complicate it?, and yeah in that definition `null == null`, but a `null_pointer != null` because null pointer is at the bottom of all the possible pointer value, and null by itself is not a pointer. The same for (0,null), (false, null) and ("", null). null should only be equal to itself.

And lastly undefined != null, because undefined is related with structures indicating that a field was not defined when the structure was created

Re: SQL nulls are weird

#176

Post by swiftcoder »

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.

I don't know about that. null/undefined in javascript gives it a pretty good run for it's money

Re: SQL nulls are weird

#177

Post by daswerth »

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.

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.

Re: SQL nulls are weird

#178

Post by swiftcoder »

hobs wrote: post #166
dominicrose wrote: post #128

Earlier quoted context omitted.

Javascript has both null and undefined and I'm not sure that's a good idea. At least in SQL we only have one of them, but it can mean unknown or it can mean N/A or even false. It's like a joker, what it means depends on how you use it.

No, it's not those other things, that's just using the tool incorrectly. A NULL is definitely "we dont know", not false, not N/A, especially not any known value.

Except in every other programming language with a null, null is the definite absence of something

Re: SQL nulls are weird

#179

Post by acuozzo »

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

> If you have missing values in your datarecord then that datarecord belongs in an exception-queue.

This depends on the context, no? I doubt there exists someone with a contact list on their phone which has every single field for every single contact populated.

There needs to be some way to codify that a field in a datarecord is unpopulated. Using the "zero value" for the type of the field (e.g., the empty string) is reasonable, but is this necessarily better than NULL? I reckon an argument can be made that this approach is just as likely to lead to bugs.

I'm not necessarily in favor of NULL, for what it's worth, but I can't think of an adequate replacement which doesn't reduce to "NULL in sheep's clothing".

Re: SQL nulls are weird

#180

Post by zie »

tessierashpool9 wrote: post #144
ozim wrote: post #140

Earlier quoted context omitted.

I see it the other way around. People think if someone wrote blog post with technical details and it got upvoted - somehow it has to be an expert.

go the extra mile and click on about and then check out the linkedin profile. i quote: "I graduated top of my class with a BSc in Computer Science [...]. I have a strong background in software engineering and technical leadership"

Who doesn't think of themselves as an expert? That doesn't mean they are one.
Post reply on HN