[dead]
SQL nulls are weird
91–100 of 293 posts
Re: SQL nulls are weird
#92In 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.
Re: SQL nulls are weird
#93Earlier quoted context omitted.
Furthermore if null only means unknown then we need a value for “known absent”, there’s a reason why null is so often used as that.
Do you actually need that in a Boolean context? It would only be useful for evaluating self-referent claims like "this sentence is false".
Your questions might be relevant if null were limited to boolean contexts.
It’s not.
Re: SQL nulls are weird
#94NULLs are weird because they are basically two different types under the same name. The 3-value logic type is useful for representing "missing" foreign keys, but 2-value logic type is arguably more useful when searching/sorting/aggregating.
I think we would have been better-off by treating FKs (and maybe outer JOINs) as a special case, and using 2-value logic everywhere else.
Re: SQL nulls are weird
#95Earlier quoted context omitted.
There's another comment in here that talks about thinking of NULL as UNKNOWN, and I quite like that. It makes a lot more sense, and I think it would've been a better choice to standardize on. UNDEFINED would also be an improvement.
UNDEFINED would not be accurate. If your signup form has an optional field for a full name which I don’t fill in, I still have a name. Just because a value is not known by your database doesn’t mean it isn’t defined. E. F. Codd thought about this issue.[0] > Codd indicated in his 1990 book The Relational Model for Database Management, Version 2 that the single Null mandated by the SQL standard was inadequate, and sho…
> UNDEFINED would not be accurate. If your signup form has an optional field for a full name which I don’t fill in, I still have a name.
If your signup form has an optional field for middle name which I don’t fill, it can absolutely be because I don’t have a middle name. It’s undefined and known to be so.
> E. F. Codd thought about this issue.[0]
And because four value logic was left out, nulls have to fulfil multiple incompatible roles, and ends up being weird.
Re: SQL nulls are weird
#96In 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…
[deleted]
Re: SQL nulls are weird
#97This has always made queries unpredictable in many scenarios and it should be a feature to turn nulls off entirely and swap them out with Option instead.
How would you handle unmatched outer joins?
a left outer join b yields tuples of (A, Option), a full outer join b yields tuples of (Option, Option)
Re: SQL nulls are weird
#98In 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.
What is the type is something other than a string?
age: null? married: null?
Re: SQL nulls are weird
#99Earlier quoted context omitted.
The distinction is that not all formal logic systems are Boolean. Meaning that it is nonsensical and confusing to use "Boolean" as a generic synonym for "truth value" in the same way that it's nonsensical to use "Pantone" as a generic synonym for "color value", including when the specific kind of color value you're talking about is CMYK or HSV and definitely not Pantone.
But it is a boolean value, there's only two possible values TRUE and FALSE. But because it's SQL you can define any column as TYPE | NULL. You could say that a boolean column with a NULL value is FALSE like how a lot of programming languages coerce it but if you wanted that you would just make a default of FALSE. The meaning of NULL in general being "value not specified" lends itself pretty nicely to "either true or…
What I want is for e.g. "x OR y" where y is NULL (and/or of nullable type) to be an error rather than silently giving surprising results. Just like in a decent programming language I can't do x || y where x and y are of type boolean?, I have to explicitly handle the case where one or other of them is null (or e.g. write x!! || y!! - and that will at still error if either is null rather than silently evaluating to a funny result).
Re: SQL nulls are weird
#100In 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.