Live data from Hacker News

SQL nulls are weird

jirevwe.github.io

201–210 of 293 posts

Re: SQL nulls are weird

#201
post #186

Earlier quoted context omitted.

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…

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.

Re: SQL nulls are weird

#203

Earlier quoted context omitted.

Null in not-SQL (which is most things) usually isn't this tortured and isn't hard.

Yeah the 3 valued logic of SQL trips people up, me too from time to time

SQL is not three valued. Neither is NULL. BOOLEAN is accused of being three-valued but it has two values and like all values they can be unknown. Similarly a SMALLINT has 65,536 possible values not 65,537.

Re: SQL nulls are weird

#204
post #84

Earlier quoted context omitted.

I think I mean either. So yes NULL is a distinct value from true and false so I think it's also right to say it's neither true nor false. But the value NULL does represent is roughly "could be true or false, we don't know yet."

It could also be neither. It's whatever you define it to be. Null could mean you don't know if it's true or if it's false, or it could mean you know it's neither true nor false.

This is the map territory relation problem.

We bring in the 'Trinity of thought' a priori and forgot about the advantages and costs.

You have the principal of excluded middle, principal of identity, and the principal of non-contradiction.

If your problems territory fits things are convenient.

Once you introduce known, unknown, or unknowable unknowns, the classic model falls down.

Unfortunately you have to choose what to sacrifice based on the context of the problem at hand.

This is exactly where Rice's theorm, the halting problem etc.. arise.

AC from ZF(C) and IID from statistics bring PEM, which gives or forces the classical behavior.

The 'non-trivial' properties of Rice's theorm are exactly those properties where PEM doesn't hold.

The frame problem in machine learning is also related. As is Gödels (in) completeness theories.

Sometimes you can use failure as negation, other times you can use methods like accepting that NULL is an unknown.

But you have to decide what you can sacrifice and still solve real problems.

I think thinking of a problem and it's complement is better.

E.G P is the truthy T and co-P is the truthy F. Obviously P=co-P means that the traditional method works, but jump to NP vs co-NP and ot doesn't.

NP chained ORs, co-NP is chained ANDs is another way of thinking about it.

Re: SQL nulls are weird

#205
post #190

Earlier quoted context omitted.

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…

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 distinct they have just been lumped into the same 25C group due to the resolution of measurement yet equality works just fine on that value in sql.

I have used SQL for a long time null handling is weird and inconsistent and a waste of time. For all the language bugs due to the existence of null at least I can count on null=null and not write garbage like value=param or (param is null and value is null)

Re: SQL nulls are weird

#206
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…

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

Re: SQL nulls are weird

#207
All of this would be avoided if NULL in sql was just called "UNKNOWN". Which is what it is. Terrible name :/

Imo, SQL should add "NOTHING", add "UNKNOWN" as a synonym for "NULL", and deprecate "NULL".

Re: SQL nulls are weird

#208
post #170

Earlier quoted context omitted.

> 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

yeah I mean, isn't all of this true for ANY abstraction? Once you're off the beaten path, they're all hard to debug and optimize, they introduce extra complexity, etc. BECAUSE they are attempting to abstract away certain details This is true for an HTTP library as much as it is an ORM.

>yeah I mean, isn't all of this true for ANY abstraction?

No, it is only true for bad abstractions.

Re: SQL nulls are weird

#209
post #180

Earlier quoted context omitted.

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

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.

Re: SQL nulls are weird

#210
post #170

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

Are you sure about this?
Post reply on HN