Use NULL for unknown data...
Hah, it goes back much further than I thought... Snopes.com pointed out this morning that the use of a 'bad default value' dates back to at least 1979...
See http://tkyte.blogspot.com/2009/10/back-from-oracle-openworld.html for the original reference.
See http://tkyte.blogspot.com/2009/10/back-from-oracle-openworld.html for the original reference.
posted by Thomas Kyte at 8:11 AM
42 Comments:
Hilarious!
This not only shows a breakdown in the usage / lack of usage of NULL, but more importantly in the process of issuing tickets or citations. What's the point of putting a ticket on a vehicle without a license plate? If the owner is driving without plates, does the officer actually believe the owner will pay a parking ticket?
Wouldn't it be great if there were a universally accepted (specific and unique) symbol that represented the 'absence of a value', be that for an non-applicable value or an unknown value?
The great advantage here would be that it would actually be a bona fide value, and not the void or absence of a value that DBMS NULLs currently represent.
And whilst it's certainly true that this multi-valued logic (TRUE, FALSE, NULL), if properly understood, can be queried with accuracy, it's also true that there are more querying mistakes executed every single day as a result of NULLs than any other rudimentary DBMS feature.
It's also true that anomalies exist, for example, the NULLIF function, where if the first argument is a NULL (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1274000564279), or where NULL can equal NULL with DECODE but not with CASE. And Oracle isn't the only DBMS to have problems with them, they all have.
So yes, NULLs may be the best we can currently do with what we currently have, but it could have been so much better.
@SeanMacGC
There already is such a symbol. It is 0 for numbers and "N/A" for strings. However, some use it for a value.
So, let's make a new symbol that means absence. Well, that would only work until someone decided to use it for something. OK, so what if we could get everyone to follow this new convention even though it is not a built-in constraint. What happens if i am quoting someone's value?
For example, i create a questionnaire with allowable comments. There are question with comments, and some comments were entered as absence of value. I now write a monthly report where comment needs to have an entry for absence of value, though it itself cannot be the absent value symbol because it would imply that i don;t know what the comment is. In this case i do know what it is, it is absent.
This is always an issue, regardless of the scheme. But adding a symbol really doesn't help.
I think the main issue people have with using NULL is NULL isn't a value. It's an attribute of the value. If the value is not NULL, it;s value is important and we can look at it. If the VALUE is NULL, it may or may not have (in the real world, "represent") a value. But it is unknown, or in a sense, unreliable. HENCE the keyword IS NULL. "IS" references the state of the data, where "=" references it's value.
To me, it's not that NULL is bad, it's just that it could be improved.
1) Add a property to value stating whether it is NULL or not, don't put NULL in the value itself.
This is currently implemented half way via the IS operator.
2) The property should have three states: Not Applicable, Unknown, Known. For example, Product.Quantity may be unknown, but it is not N/A. I think that difference confuses people.
3) Force DEFAULT to be explicit, and do not default DEFAULT to NULL NULL isn't always appropriate.
(In a NOT NULL field the DEFAULT would have to make sense, or be NULL so the statement fails.)
Just some thoughts.
@Brian
you are kidding right?
0 for numbers - since when is 0 not a valid number, and not a number that is FREQUENTLY used??
think about an inventory rollup. Just because there are 0 in inventory does not mean or imply the inventory amount DOES NOT EXIST, it does exist and it is currently ZERO. (that is just one example). ZERO is frequently the correct KNOWN value. Using 0 for a missing number would be like using XXXXXXX for a missing license plate.
And N/A. What about when you are in China? Does it still apply - or is it not applicable? What about a table of acronyms/abbreviations, might there be a need to store N/A in a field and not have it mean "N/A". N/A is as bad as XXXXXXXX or 'NO PLATE' etc.
I don't think Sean meant a symbol in the sense of a value - but rather a 'state of being', like NULL is not a symbol, it is a value that represents a certain state of being.
I totally agree that NULL could be improved (almost everything can) but - it is what it is. And if you know about it, you can use it. If you don't, you shouldn't be programming databases in the first place (it would be like saying "order by is so confusing, I don't get it, so I don't use it, I just sort in my client. Furthermore, where is so long and confusing, I don't use it either, I filter in my program" and so on...
@Brian Tkatch
There already is such a symbol. It is 0 for numbers and "N/A" for strings.
That's two values, and the problem with those is they can be legitmate values in their own right, where they don't represent a 'not-applicable' or 'unknown'.
However, some use it for a value.
Exactly, so they fail at the first hurdle.
So, let's make a new symbol that means absence. Well, that would only work until someone decided to use it for something.
If 'NULL', which after all is a four character symbol, can represent NULL without it being used for anything else (and without someone deciding to use it for something else), then why can't a specific value be introduced to represent NULL that can't be used or confused with anything else.
@Tom
I don't think Sean meant a symbol in the sense of a value - but rather a 'state of being', like NULL is not a symbol, it is a value that represents a certain state of being.
Yes Tom, that would be the gist of it, though we're at variance on the terminology somewhat ;o)
@Sean
NULL is not the symbol, NULL is what we call that thing - we do not store NULL, we store a flag that tells us if the data on the block is null, we return to the client application a flag that tells them whether the value is null.
Null is not the symbol :)
@Tom
I was making a point. People do have a symbol that they use. Very similar to the NOPLATE example. However, other people use them for other things.
BTW, 0 isn't a number. :) A number represents a quantity and 0 is not a quantity. That's why it took so long for 0 to be "added". It's a convenience number, best used as a placeholder. Obviously necessary as in the example you provided. But a point worth making. I despise it when programmer's use 0 witht eh claim "it's a number too". No it isn't!
But i do agree with your comments.
We must not forget why NULL exists:
Three valued logic.
Pulling from page 344 of Relational Database Writings 1991-1994 by CJ Date, a paragraph written by Dr. E.F. Codd, the father of the RDBMS, response to some articles written by CJ Date:
"A single relational request can touch many different columns in a relational database, and therefore it is intolerable that in conceivig such a request the user should have to understand and cope with as many different representations and treatments of missing values as the columns that are touched. In a relational database, both the representation amd treatment of missing values must be uniform across the entire database."
When I need to know whether a value is missing or not, I don't want to base it on the whims of the developer or data analyst, and how they decided to treat "missing values", which may be different from column to column and datatype to datatype.
@Brian
zero is absolutely a number, speaking as a math major from college, I can attest to that
http://www.straightdope.com/columns/read/1633/is-zero-a-number
The reason it took so long to be added is because humanity has been accumulating knowledge over the centuries.
Once we thought the sun went around the earth too - that it took so long to be accepted that the earth revolves around the sun doesn't make it less true or differently true.
zero is absolutely, definitely a number.
Using your logic "a number represents a quantity and 0 is not a quantity" - you would be saying "-2" is not a number.
It is 0 degrees F today. Is zero a number?
It is -32F today. Is -32 a number?
zero is, has been (even before people recognized it as such, just like the earth revolving around the sun) and will be a number.
@Tom
"Once we thought the sun went around the earth too"
Sort of. It was a religious belief not a secular one. And it is still held today by many. The solar system certainly is heliocentric. Religion may hold itself to a larger picture, or not be talking about the physical world at all.
"you would be saying "-2" is not a number."
It is a number. The number is 2. It's attribute is negative, however.
"It is 0 degrees F today. Is zero a number?"
No. It is a place holder. The numbers were put on a scale and chose 0 as the bottom. I would have put 1 at the bottom.
"It is -32F today. Is -32 a number?"
Yes. 32 with the negative attribute.
"zero is, has been (even before people recognized it as such, just like the earth revolving around the sun) and will be a number."
Well, i had to disagree with you on something. Might as well be this.
Though, this is a disagreement in nomenclature. Math has defined various entities in order to be used. That ends up using 0 as a number. I am defining a number based on what it is, regardless of its use, and a number represents a quantity.
Should name be based on (practical) use or what it actually is? That probably depends on personality type more than anything. (J/P on the MBTI.)
@Brian
We'll have to disagree- as you are thinking of counting numbers only - saying "negative is an attribute" - well, just give me a break (that is just silly, if you cannot have zero of something - you cannot have negative one of something either - your argument is "you cannot have zero of something, numbers are a quantity" - if that were true - you cannot have a negative one of something either, if you can - then I can give you one of something and -1 plus +1 equals zero).
zero is NOT at the bottom of any scale, it is the number between 1 and -1. It is NOT at the bottom of anything.
In mathematics - zero is, will be and actually always has been (just took them a while to figure out the concept). And mathematics might have a certain ability to lay claim to .... numbers.
zero is in fact a number, there is no disagreeing with that really, by definition - *by mathematical definition* - zero is a number.
calling is a placeholder is disingenuous at best.
... Though, this is a disagreement in nomenclature. Math has defined various entities in order to be used. That ends up using 0 as a number. I am defining a number based on what it is, regardless of its use, and a number represents a quantity....
You mean to say "I am defining it based on whatever I feel like", sort of Alice in Wonderland. You are defining a number based on your made up set of criteria.
And the sun revolving around the earth predates the religions in place today. It is true that certain religions still to this day hold it as one of their written down things - but it wasn't a religious thing - it is and was a lack of knowledge. It was written in religious documents because at the time those things were written - that is what people believed (flat earth, earth as the center of everything). Do not chicken and egg this - the idea (sun revolves around earth) came and then religions made that part of their writing.
Just as the early humans did not have the concept of zero - they didn't need that concept yet, they got around to realizing it was there when it was needed.
Negative numbers didn't exist for a long time either. Some government forms in the US still don't
http://www.mathpages.com/HOME/kmath298.htm
but it doesn't mean they don't exist or aren't numbers.
@Tom
"if you cannot have zero of something - you cannot have negative one of something either"
That is true. But you do have something, and that something is in a quantity.
"zero is NOT at the bottom of any scale, it is the number between 1 and -1."
Interestingly enough, there is no year 0. At least not on any calendar i know of.
"calling is a placeholder is disingenuous at best."
I believe it to be historically accurate. But i do understand the comment, for in practical use, it is treated as a number.
"You mean to say "I am defining it based on whatever I feel like""
No, that is not what i meant. I am not being subjective, i am trying to be objective. Defining it based on what it is.
"You are defining a number based on your made up set of criteria."
Defining number as a representation of quantity is "made up". That would indeed explain why so many kids have a hard time with math. How do you define number?
"And the sun revolving around the earth predates the religions in place today."
That depends on the religion. Of course, it also depends on the religions version of the religion and irreligious people's version of the religion.
"It was written in religious documents because at the time those things were written - that is what people believed"
To a religious person, that might not be the case.
"flat earth"
What religions believed in a flat earth? I mean that seriously. Judiasm, Xians, and Islam never did. Well, in the church, there was some argument, but St. Augustine clearly believed in the antipods. That's a good portion of "Western" religions. Did eastern religions believe in a flat earth?
"Just as the early humans did not have the concept of zero - they didn't need that concept yet, they got around to realizing it was there when it was needed."
In the writings i've seen before 0 was used, a term (translated as) "nothing" is usually used. I always thought it was used because there is no quantity, hence "nothing".
@Brian
I have pointed to various places that define numbers, would you please point to a source - a scientific sort of source - that defines numbers without zeros?
... To a religious person, that might not be the case. ....
ummm? so??? It doesn't make it any less true. The sun revolving around the earth is what people thought to be true. Then they wrote it in books. Then it became dogma. So what if to a person of religion X they do not believe that is what happened. It is in fact what happened.
The sun revolving around the earth is a good analogy for "zero is not a number"
"if you cannot have zero of something - you cannot have negative one of something either"
That is true. But you do have something, and that something is in a quantity.
that hurts my head, please make it stop.
If you can have negative -1 of something - you have nothing. You have less then nothing. There must be a 'nothing' state. When I give you two things - you go from -1 to +1.
What do you have quantity wise when I give you 1 of something then?
The result of adding two numbers is a number
What is the result of -1 plus +1?
"flat earth"
What religions believed in a flat earth? I mean that seriously. Judiasm, Xians, and Islam never did. Well, in the church, there was some argument, but St. Augustine clearly believed in the antipods. That's a good portion of "Western" religions. Did eastern religions believe in a flat earth?
I do not want to go down the 'religion black hole' - but, the bottom line is this entire religion thing you brought in is a total non-sequitur, it has no bearing on the discussion. So what if some religions believed the sun revolved around the earth? That doesn't mean a) they invented the concept b) other non secular individuals did not also believe it
it (sun, flatness, zero) has nothing to do with religion when discussing "was it right or wrong"
... In the writings i've seen before 0 was used, a term (translated as) "nothing" is usually used. I always thought it was used because there is no quantity, hence "nothing". ...
so? that was then, this is now, and that is the point.
back then lots of people thought lots of strange things - some still do. Does that make them right?
"I have pointed to various places that define numbers"
Those links are about the subject and use. I was wondering if you had a simple definition.
"would you please point to a source - a scientific sort of source - that defines numbers without zeros?"
I cannot, for there are none (that i know of). Scientific sources list systems; those systems are based on their usage; their usage uses 0s.
I don't think this changes the definition, however. But you obviously disagree with that definition.
@Brian
the definition was in the first link.
http://www.straightdope.com/columns/read/1633/is-zero-a-number
oh, almost forgot:
ops$tkyte%ORA9IR2> select add_months( to_date( '1-jan-0001', 'dd-mon-yyyy' ), -1 ) from dual;
ADD_MONTHS(TO_DATE('
--------------------
01-dec-0000 00:00:00
ops$tkyte%ORA9IR2> select to_date( '1-dec-0000', 'dd-mon-yyyy' ) from dual;
select to_date( '1-dec-0000', 'dd-mon-yyyy' ) from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
the convention is, there is no year zero directly accessing it - but the only way to do the math is to have a year zero.
ops$tkyte%ORA9IR2> alter session set nls_date_format = 'dd-mon-yyyy ad'
2 /
Session altered.
ops$tkyte%ORA9IR2> select add_months( to_date( '1-jan-0001', 'dd-mon-yyyy' ), -13 ) from dual;
ADD_MONTHS(TO_
--------------
01-dec-0001 bc
ops$tkyte%ORA9IR2> select add_months( add_months( to_date( '1-jan-0001', 'dd-mon-yyyy' ), -13 ), 1 ) from dual;
ADD_MONTHS(ADD
--------------
01-jan-0000 ad
so, there is a year zero.
http://en.wikipedia.org/wiki/Year_zero
depends on your calendar too.
@Tom
In that link, i see "what do you mean by number" and some sub-classing. I don't see a definition though.
In the year 0 article, it is a matter of convenience "it can be more convenient to regard". But i did not know the point about the eastern calendars. Thanx.
they tell you what the set of counting numbers are and go on to describe that these are a subset of the Positive integers which are a subset of ... and so on
...
Let's ignore history and get back to mathematical development. After you have developed the Counting Numbers, you get the Positive Integers, and that's when zero steps onto the stage. The Positive Integers (more technically correct, the Non-Negative Integers) are the set of Natural Numbers and zero, usually designated P = {0, 1, 2, 3...} At that stage in the development of your number system, zero becomes a number.
...
http://www.google.com/search?q=define%3A+number
# a concept of quantity involving zero and units; "every number has a unique position in the sequence"
http://en.wikipedia.org/wiki/Number
In mathematics, the definition of number has been extended over the years to include such numbers as zero, negative numbers, rational numbers, irrational numbers, and complex numbers.
You could argue using some of your arguments that 1.5 is not a number. You cannot have half of something, you just have something. one half is not a quantity by itself.
But - 1.5 is a number nonetheless.
As is zero.
"You cannot have half of something, you just have something. one half is not a quantity by itself."
Going Socratic on me? :)
Phaedo (http://www.gutenberg.org/files/1658/1658.txt)
"neither can I understand how the division of one is the way to make two; for then a different cause would produce the same effect,--as in the former instance the addition and juxtaposition of one to one was the cause of two, in this the separation and subtraction of one from the other would be the cause"
Funny, just before I read this I was working on a confusing issue where the app displays the default value of zero, whether the row has zero or null. But a processing program does different things if it is null.
There is a point to putting a ticket on a car with no plate - the VIN number is also available and uniquely id's modern vehicles.
I had a propagated null incorrectly handled affect me around 1983.
I grew up in the Westchester mentioned in the Steve Harvey article. I got my personalized plates in 1973, and I remember jokes about funny or interesting plates coming in the default non-personalized series of plates from the dmv at that time - you weren't allowed to get a plate that would conflict with an existing pattern, such as 3 numbers followed by 3 letters. For example, the plate on my previous car was 502YIP. In California, a 502 is driving under the influence, and you are presumed to give consent to a urine test. But at least I'm not in Florida with A55RGY.
There was an attorney general of California who had problems getting a home loan because his credit report showed lots and lots of lawsuits against him - basically any claim against the state was counted against his personal credit. (Sorry I can't give a reference, subsequent events overload a google.)
word: ilatte
word: kerocen
@Joel,
Without getting into a full blown business analysis, maybe a ticket needs two fields, one for plate number and one for VIN. If the vehicle does not have plates, then the officer should enter the VIN.
The use of nulls, is ultimately determined by modeling the real world and how the business exists within it.
Yes, some tickets don't have a place for it, some do. These days, most jurisdictions are going to electronic devices that print out the ticket then upload to the police computers to track. Many cars have barcoded VIN on the dashboard, and barcode readers have to be able to read through the windshield.
word: potin
I understand that 0 is not null and I also agree that 0 is a number.
If to_number() doesn't throw exception - it's a number :-)
But I don't understand why empty string is null?
select count(*) from dual
where '' is null
-----
1
Lev
Maybe the DB was implemented correctly and the licencse plate field was in fact nullable, but the client application programmer made sure that only valid licence plates can be entered there ;-) Wouldn't be the first time that human communication is not that perfect in the IT world. Anyone involved in that project, please enlighten us in the truth behind.
I use Schrödinger's "cat-in-a-crate" analogy to explain NULL.
http://it.toolbox.com/blogs/data-ruminations/sql-and-the-seven-lives-of-schroedingers-cat-33057
The analogy works, at least for me!
http://it.toolbox.com/blogs/data-ruminations/the-case-of-threevalue-logic-or-yes-it-is-obvious-34823
Schrödinger's "cat-in-a-crate" analogy can certainly be used to explain NULL, and does that well. But that's not really the issue...
If you were concerned with the colour of the cats, either to record, or to purchase, or whatever, then you're not going to be too concerned about the cat in the crate, because you don't know what colour it is. It is information that you will not retain.
In the same way, for RDBMS purists, the database will hold a set of propositions (in simple terms) that will evaluate to either True or False (Two-Valued Logic), i.e., the database will not hold the 'absence of knowledge' about anything, since it's of no inherent meaning in and of itself.
Of course, there are RDBMS adherents who claim that Three-Valued Logic is just as valid as a Relational property as 2VL, but's that's another discussion ;o)
I've recently come across a New Scientist story of a UK Rail route planner locating a railway station in the south Atlantic ocean:
http://www.newscientist.com/article/mg20327226.200-all-at-sea-with-national-rail.html
The unknown/missing location was entered as 0°N 0°W.
This comment has been removed by the author.
Is zero a number?
'The question is,' said Humpty Dumpty, 'which is to be master -- that's all.'
It comes down to context and choice (though these choices may have been accidents really!):
- In the context of temperature we choose zero to be a number as we go from -1 to 0 to +1 degrees.
- In the context of dates we can choose to have a year zero, or not - providing we do so in a consistent fashion.
- In the context of telling the time on a 12-hour clock, we choose zero NOT to be a number. The hours are labelled 1 to 12. These labels form a group under addition modulo 12, with 12 acting as the identity (i.e. it does what zero would have done if we had chosen to use 0 to 11 as the labels).
. . . continued
- In the context of an array index, the language designer can choose a[0] or a[1] to be the first element.
@DaPi
Web filter considered link "...notlong..." in new scientist article as adult, sexually explicit.
Brian:
{quote}
BTW, 0 isn't a number. :) A number represents a quantity and 0 is not a quantity.
Those links are about the subject and use. I was wondering if you had a simple definition.
{/quote}
So, what is your definition of quantity? You are already implying a system of measurement (an abstraction), one in which you can compare (greater, less, equal, increasing, decreasing).
A number is an abstraction about a collection of things (a set). The number zero is the abstraction of the empty set. That is the simple definition.
In case your use of the word quantity meant to suggest an entity or being, something with a material existence … abstraction is the process of distancing ideas from objects ... the idea, in the case of natural numbers, was to count and compare things ... which takes us back to a number is an abstraction about a collection of things.
It matters not that the idea about number zero (Indian mathematician) came later than the idea about number two (Greek philosophers) … it is still a number.
This perennial discussion about Nulls in relational databases, its shortcomings, uses and abuses - which took a turn by way of natural numbers, Greek philosophers, Indian scientists, not to mention us opinionated mortals - reminds me of my friend who quoted an anonymous Russian compatriot of his:
the philosopher learns less and less about more and more until knows nothing about everything; the scientist learns more and more about less and less until knows everything about nothing.
Talk about Nulls! :)
@Brian
Check Peano's axiom
http://en.wikipedia.org/wiki/Peano_axioms
Just for fun, the St. Louis Rams' backup quarterback's name is Keith Null.
Reminds me of this XKCD:
http://xkcd.com/327/
sPh
Hi,
zero is a number (read it as "it does have a meaning for a quantity, so it is a number").
zero is not a natural number.
both afirmations are true, anyone can pick one of them and argue based on that.
we can build a math theory based on the axiom "zero is a number", we can build a different one based on "zero is not a number".
we will get different results, both corrects, as long as they respect their own set of axioms.
However, we use a mathematical theory based on "zero is a number" because it actually explains better, to our very human eyes, what we see as "the real world".
sorry for posting offtime.
@Nigel
Using anything other than NULL to represent NULL may, in Oracle, take up more index space.
That isn't what anybody suggested at all.
Where does it say anywhere "use anything other than NULL to represent NULL"?? I don't see it - do you?
What was said was - that as long as an index has at least ONE not null attribute - all rows in the table will be present in the index, allowing the index to be used for many things beyond what it could have been used for before - including "IS NULL" predicates.
If you add a non-null attribute t the index, it'll make the index more useful.
Infinitely preferable to storing something to represent NULL in the attribute itself.
A 'warning' like this is sort of like 'waring' "If you create an index, it will take up more index space"
And no one said to use something else to repsesent null - the entire pair of articles is actually written stating "do NOT do that" clearly. What was said was "adding a non-nullable attribute to your index can do some nice things".
Shurely 'good default' values can prevent many errors.
@gdever
There is a good default - it is called NULL. It is the default default!
Actually null can't be assigned to those objects which are primitive except that it is good idea to assign null to unknown data, except they're candidate key or more over primary key.
That is correct use null if you do not know where or how to get the data. This is what I do when I make my projects.
little late but for others like me who come across this... Another reason we know 0 is a number: That all sets of numbers must have an additive identity. In all sets of numbers the additive identity is 0. The additive identity must also be in the set.... therefore, 0 must be a number for if it isn't then no number is a number since sets of numbers do not exist?
http://en.wikipedia.org/wiki/Additive_identity
POST A COMMENT
<< Home