The Meaning of Null in Databases and Programming Languages (2016)(arxiv.org)
arxiv.org
The Meaning of Null in Databases and Programming Languages (2016)
https://arxiv.org/html/1606.00740
24 comments
> The relational null represents the absence of a value in a field of a record; whereas the programming language null represents one of the possible values of a variable.
This sounds like splitting hairs, especially as they never seem to explicitly define what "value" means. How is it not a value? It sure looks like a "value" to me.
It's not just the occasional textbook that uses this nomenclature. In SQL itself, you type "INSERT INTO mytable VALUES (NULL);" to insert a NULL. Did the designers of SQL not understand what "value" means?
They also claim that databases are special in that they use "three-valued logic" -- which would seem to imply that NULL is a third value. Does "value" have different meanings in different contexts? For the word whose precise definition is central to their thesis, it has some remarkable linguistic flexibility.
> Here is a partial list of the reasons why NULL can occur. 1. The field has a value but it isn't known... 2. The field has no value because it isn't applicable...
These are all reasons I've seen people use null in programming languages, too.
> The field is not allowed to have a value. For example, the root node of a tree has no parent node. The fact that the field is NULL in this case is not due to missing information.
Are they really trying to claim this is somehow unique to relational databases? Trees work exactly the same way in programming languages.
This sounds like splitting hairs, especially as they never seem to explicitly define what "value" means. How is it not a value? It sure looks like a "value" to me.
It's not just the occasional textbook that uses this nomenclature. In SQL itself, you type "INSERT INTO mytable VALUES (NULL);" to insert a NULL. Did the designers of SQL not understand what "value" means?
They also claim that databases are special in that they use "three-valued logic" -- which would seem to imply that NULL is a third value. Does "value" have different meanings in different contexts? For the word whose precise definition is central to their thesis, it has some remarkable linguistic flexibility.
> Here is a partial list of the reasons why NULL can occur. 1. The field has a value but it isn't known... 2. The field has no value because it isn't applicable...
These are all reasons I've seen people use null in programming languages, too.
> The field is not allowed to have a value. For example, the root node of a tree has no parent node. The fact that the field is NULL in this case is not due to missing information.
Are they really trying to claim this is somehow unique to relational databases? Trees work exactly the same way in programming languages.
I think I understand what they're trying to say.
The value of a variable with type "non-nullable 32-bit integer" is an element of the space {-231, ..., 231 - 1}. The value of a variable with type "nullable 32-bit integer" is an element of the space {-231, ..., 2*31 - 1} U {NULL}.
There's a "main space", and an extra singleton space tacked on, containing the "NULL" element.
So NULL isn't a value in the "main space", and therefore in some sense "isn't a value". I think it's wrong for the same reasons you stated, but I think this is what they were trying to describe.
The value of a variable with type "non-nullable 32-bit integer" is an element of the space {-231, ..., 231 - 1}. The value of a variable with type "nullable 32-bit integer" is an element of the space {-231, ..., 2*31 - 1} U {NULL}.
There's a "main space", and an extra singleton space tacked on, containing the "NULL" element.
So NULL isn't a value in the "main space", and therefore in some sense "isn't a value". I think it's wrong for the same reasons you stated, but I think this is what they were trying to describe.
This is a very interesting article, especially since I can now appreciate the meaning, having spent the last year doing both SQL schema design and backend development that works off that schema.
The main point here is that Language null is a possible value, whereas Database NULL is a composite with several different meanings, which can often trip up people who are not familiar with how some SQL systems treat it (NULL <> NULL, etc)
The important takeaway from this article is that DB NULL can arise for a variety of reasons (taken from the article):
1. The field has a value but it isn't known.
2. The field has no value because it isn't applicable.
3. A field value could have been inferred but was not inferred, because of an overriding requirement.
4. The field has a value but it is not within the domain.
5. The field value cannot be determined due to an exception.
As the author suggested, "NOT SET" does a good job carrying the meaning behind what NULL is used to accomplish (I also think "NOT GIVEN" or "NOT AVAILABLE" could work and keep the idea.
The main point here is that Language null is a possible value, whereas Database NULL is a composite with several different meanings, which can often trip up people who are not familiar with how some SQL systems treat it (NULL <> NULL, etc)
The important takeaway from this article is that DB NULL can arise for a variety of reasons (taken from the article):
1. The field has a value but it isn't known.
2. The field has no value because it isn't applicable.
3. A field value could have been inferred but was not inferred, because of an overriding requirement.
4. The field has a value but it is not within the domain.
5. The field value cannot be determined due to an exception.
As the author suggested, "NOT SET" does a good job carrying the meaning behind what NULL is used to accomplish (I also think "NOT GIVEN" or "NOT AVAILABLE" could work and keep the idea.
I don't understand how those aren't also descriptions of Language null. Haven't you seen programs that use null in those ways (and several others)? I certainly have.
Fun factoid, VBA/VB6 dichotomizes between the "NULL" of value (keyword Null) versus the "null" of reference (keyword Nothing).
Dim x
x = Null
Debug.Print "x", x
Debug.Print "x = x", x = x
Debug.Print "x <> x", x <> x
Debug.Print "x = 0", x = 0
Debug.Print "0 = x", 0 = x
Debug.Print "0 + x", 0 + x
Dim y
y = Nothing
Debug.Print "y", y
VBA seems to have null, empty, "" and nothing, all of which have different semantics. But only variants can be null or empty, I believe; if you have a typed variable like a Date or a String, you're out of luck.
And then, I was using either Power Automate or SharePoint/odata and it turned out "null" does not have the "normal" null semantics, and to test for null, you simply check if something is equal to it.
I think I remember from using SQL Server that there is a option that you can treat null either way.
Microsoft has a weird relationship with null.
On the other hand, I'm sure someone has complained about Oracle's idea that empty strings are null values. And once you think about it, SQL's handling of nulls is weirdly inconsistent, because the special handling of them doesn't always apply in aggregates.
Somewhere I had a book by C.J. Date/Hugh Darwen in which I remember a rant about how nulls are a terrible offense against the true relational model.
And then, I was using either Power Automate or SharePoint/odata and it turned out "null" does not have the "normal" null semantics, and to test for null, you simply check if something is equal to it.
I think I remember from using SQL Server that there is a option that you can treat null either way.
Microsoft has a weird relationship with null.
On the other hand, I'm sure someone has complained about Oracle's idea that empty strings are null values. And once you think about it, SQL's handling of nulls is weirdly inconsistent, because the special handling of them doesn't always apply in aggregates.
Somewhere I had a book by C.J. Date/Hugh Darwen in which I remember a rant about how nulls are a terrible offense against the true relational model.
>But only variants can be null or empty...
That's true, only Variants can be Null (Null is a state of Variant); however, Empty is translated to whatever default value makes sense for some of the (non-Object, non-user-defined) data types:
>I'm sure someone has complained about Oracle's idea that empty strings are null values.
I have certainly complained about that: I mean, what's not to love about conflating "this field intentionally left blank" with "no certain value could be obtained for this field"? /s
>...a rant about how nulls are a terrible offense against the true relational model...
By way of Wikipedia, found this: https://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-TheAskewWall-pri.... A fine read. I am left wondering about how OUTER JOINs would be handled without NULLs. I am now also wondering if it was OUTER JOINs that made NULLs seem necessary.
That's true, only Variants can be Null (Null is a state of Variant); however, Empty is translated to whatever default value makes sense for some of the (non-Object, non-user-defined) data types:
Dim s As String, d As Date, x As Single, y As Double, i As Integer
s = Empty: d = Empty: x = Empty: y = Empty: i = Empty
Debug.Print s, d, x, y, i
>I'm sure someone has complained about Oracle's idea that empty strings are null values.
I have certainly complained about that: I mean, what's not to love about conflating "this field intentionally left blank" with "no certain value could be obtained for this field"? /s
>...a rant about how nulls are a terrible offense against the true relational model...
By way of Wikipedia, found this: https://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-TheAskewWall-pri.... A fine read. I am left wondering about how OUTER JOINs would be handled without NULLs. I am now also wondering if it was OUTER JOINs that made NULLs seem necessary.
"I mean, what's not to love about conflating "this field intentionally left blank" with "no certain value could be obtained for this field"
Well, probably because Oracle was the first database I used a lot, it just seems natural to me and Microsoft's distinction between the empty string and null is annoying.
On the other hand, if I were to try to rationally defend Oracle's way of doing it, it would be something along these lines:
You can subdivide the concept of "this field doesn't have a normal value" into an infinite number of reasons. So if you're not going to have zero "null-like" options, and you're not going to have one, then where does it stop? When you have two, or three, or four, etc. that seems like you've gone down the wrong path no matter how good your intentions are. It vaguely reminds me of the "zero, one, infinity" rule.
Well, probably because Oracle was the first database I used a lot, it just seems natural to me and Microsoft's distinction between the empty string and null is annoying.
On the other hand, if I were to try to rationally defend Oracle's way of doing it, it would be something along these lines:
You can subdivide the concept of "this field doesn't have a normal value" into an infinite number of reasons. So if you're not going to have zero "null-like" options, and you're not going to have one, then where does it stop? When you have two, or three, or four, etc. that seems like you've gone down the wrong path no matter how good your intentions are. It vaguely reminds me of the "zero, one, infinity" rule.
"Empty is translated to whatever default value makes sense"
You can assign it, but then you've lost the distinction. Conversely, you can't tell that you haven't assigned anything by testing for Empty. With a Date, it's probably clear that if it equals zero it's uninitialized, but with an integer, not so much.
You can assign it, but then you've lost the distinction. Conversely, you can't tell that you haven't assigned anything by testing for Empty. With a Date, it's probably clear that if it equals zero it's uninitialized, but with an integer, not so much.
Although this is relevant for languages like C where NULL == 0, other (typically more modern languages) treat Null similar to how databases treat it. Ex: Swift, Kotlin, Rust
This can’t be correct.
At least in Rust, null (or None) is not treated at all like how databases treat it. If you have T: PartialEq<T>, then there’s an implementation for Option<T>: PartialEq<Option<T>>.
With this implementation, None == None. The result is true. None != None is false.
In an SQL database, (null = null) is null. Neither true nor false. (null != null) is also null. Neither true nor false.
This is an enormous difference, and it is basically THE gotcha for working with null in SQL databases.
If you want to translate that into a "traditional" programming language, the closest I can give you is Haskell, where you can think of SQL equality as being normal equality lifted into the Maybe applicative functor. (If that doesn’t make sense, you’re not a Haskell programmer, don’t worry about it.)
At least in Rust, null (or None) is not treated at all like how databases treat it. If you have T: PartialEq<T>, then there’s an implementation for Option<T>: PartialEq<Option<T>>.
With this implementation, None == None. The result is true. None != None is false.
In an SQL database, (null = null) is null. Neither true nor false. (null != null) is also null. Neither true nor false.
This is an enormous difference, and it is basically THE gotcha for working with null in SQL databases.
If you want to translate that into a "traditional" programming language, the closest I can give you is Haskell, where you can think of SQL equality as being normal equality lifted into the Maybe applicative functor. (If that doesn’t make sense, you’re not a Haskell programmer, don’t worry about it.)
sqlEq :: (Applicative f, Eq a) => f a -> f a -> f Bool
sqlEq = liftA2 (==)
> Nothing `sqlEq` Nothing
Nothing
> Nothing `sqlEq` Just 4
Nothing
> Just 4 `sqlEq` Just 5
Just False
> Just 4 `sqlEq` Just 4
Just True
Null in SQL databases is more complicated than that.
Quote from Oracle documentation: "Unless otherwise stated, group functions ignore NULL values"
So, sometimes operations on nulls don't produce null, and in fact that's considered normal...except when it isn't.
Quote from Oracle documentation: "Unless otherwise stated, group functions ignore NULL values"
So, sometimes operations on nulls don't produce null, and in fact that's considered normal...except when it isn't.
Thanks for the explanation, I forgot that null != null in databases. I was thinking more about how null != 0 and is a separate concept in languages with optional values. You’re right that optionals in these languages don’t quite work the same as in databases
But it's not NULL != NULL. It's that NULL == NULL is NULL. That's different! NUll != NULL is also NULL, for instance.
Maybe a concise way to describe it is that in databases NULL represents the absence of a value. Since NULL is not a value, operators like == and != don't really make sense on NULL, and also produce NULL as an output.
I'm not a C programmer, rather an extensive database user. And NULL == 0 seems awkward and nonsensical to me.
It’s an awkward holdover from bygone days. If you go back far enough in time, before it was really “C”, there was no difference between pointers and integers. The shift happened gradually, with pointers turning into a different type. I suspect NULL was kept as 0 to avoid breaking existing code. Then you can just:
#define NULL 0
struct mystruct {
int length;
int *data;
};
struct mystruct x;
memset(&x, 0, sizeof(x));
Fun reading on what systems didn’t use 0 for NULL: https://stackoverflow.com/questions/2597142/when-was-the-nul...
Looking at popular SQL databases (you know who you are!), NULL == '' seems pretty awkward to me, too!
Those languages use three value logic?
I think they mean that NULL is a part of the type system, rather than an overloaded value of the same type. You can't assign NULL to a Boolean, but you can assign NULL to a nullable Boolean. From the article:
> The programming language null is a value but database null is not a value.
Those languages can use three value logic, but only if the type is marked as nullable. Newer languages have syntactic sugar to make it easier to handle null, which means that most functions can be written assuming their inputs are non-null. SQL by contrast defines most functions to handle nullable types. Here are some examples in kotlin (all ? relate to nullability, see a tutorial for more info):
> The programming language null is a value but database null is not a value.
Those languages can use three value logic, but only if the type is marked as nullable. Newer languages have syntactic sugar to make it easier to handle null, which means that most functions can be written assuming their inputs are non-null. SQL by contrast defines most functions to handle nullable types. Here are some examples in kotlin (all ? relate to nullability, see a tutorial for more info):
val b : Boolean? = null
b.and(true)
// type error
b?.and(true)
// null
(b ?: true).and(true)
// true
b?.let { bb -> true.and(bb) }
// null
https://en.wikipedia.org/wiki/Open-world_assumption
vs.
https://en.wikipedia.org/wiki/Closed-world_assumption
Which means we're dealing with philosophy, no?