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...
SQL nulls are weird
201–210 of 293 posts
Re: SQL nulls are weird
#202Re: SQL nulls are weird
#203Earlier 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
Re: SQL nulls are weird
#204Earlier 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.
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
#205Earlier 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…
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
#206Earlier 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…
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
#207Imo, SQL should add "NOTHING", add "UNKNOWN" as a synonym for "NULL", and deprecate "NULL".
Re: SQL nulls are weird
#208Earlier 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.
No, it is only true for bad abstractions.
Re: SQL nulls are weird
#209Earlier 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…
Re: SQL nulls are weird
#210Earlier 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