Thread: NULLS and User Input WAS Re: multimaster
> > An empty string is not null! Null means the value is missing, which is > clearly not the case here. I would say Rails is exactly in the right > here. When an HTML form is posted, empty input boxes are declared as > empty strings, which what the user entered. The problem is not with > Rails/ActiveRecord but with your form handling. If you want empty > boxes to become null, add some client-side JavaScript logic that sets > the "disabled" attribute on empty input elements before form is > submitted; this will prevent the client from sending the value. The user was presented an _opportunity_ to enter data and did not. The data is unknown. I don't know how you can say "...The user entered" an empty string. There is no empty string key on the keyboard. I have no idea why I got such hard pushback on this. This is the EXACT same behaviour other types use. If a number field is presented to the user and submitted with no value, NULL Is inserted. Not zero, which is the numeric equivalent of the empty string, but NULL. Same with date types. Why not say they entered '1/1/1970' by default if they entered nothing? http://dev.rubyonrails.org/ticket/3301 - Ian > > Alexander. >
On 6/3/07, Ian Harding <harding.ian@gmail.com> wrote: > > An empty string is not null! Null means the value is missing, which is > > clearly not the case here. I would say Rails is exactly in the right > > here. When an HTML form is posted, empty input boxes are declared as > > empty strings, which what the user entered. The problem is not with > > Rails/ActiveRecord but with your form handling. If you want empty > > boxes to become null, add some client-side JavaScript logic that sets > > the "disabled" attribute on empty input elements before form is > > submitted; this will prevent the client from sending the value. > > The user was presented an _opportunity_ to enter data and did not. > The data is unknown. I don't know how you can say "...The user > entered" an empty string. There is no empty string key on the > keyboard. Not at all. If the input box already contained a string, and the user erased the contents of the input box, then the user has, in effect, entered an empty string. Not a "null". This is a UI layer issue, not a database issue. > I have no idea why I got such hard pushback on this. This is the > EXACT same behaviour other types use. If a number field is presented > to the user and submitted with no value, NULL Is inserted. Not zero, > which is the numeric equivalent of the empty string, but NULL. Same > with date types. Why not say they entered '1/1/1970' by default if > they entered nothing? Ah, no. An empty string is not a valid number -- in fact, it is the absence of a number; the same goes for dates. An empty string, however, is a valid string, since a string is (in this context) defined as a sequence of 0 to n characters.) Your patch is awful because it would mean there was no way to enter an empty string in the database. A one-character string containing a single space is not an empty string. Alexander.
On 6/3/07, Alexander Staubo <alex@purefiction.net> wrote: > On 6/3/07, Ian Harding <harding.ian@gmail.com> wrote: > > > An empty string is not null! Null means the value is missing, which is > > > clearly not the case here. I would say Rails is exactly in the right > > > here. When an HTML form is posted, empty input boxes are declared as > > > empty strings, which what the user entered. The problem is not with > > > Rails/ActiveRecord but with your form handling. If you want empty > > > boxes to become null, add some client-side JavaScript logic that sets > > > the "disabled" attribute on empty input elements before form is > > > submitted; this will prevent the client from sending the value. > > > > The user was presented an _opportunity_ to enter data and did not. > > The data is unknown. I don't know how you can say "...The user > > entered" an empty string. There is no empty string key on the > > keyboard. > > Not at all. If the input box already contained a string, and the user > erased the contents of the input box, then the user has, in effect, > entered an empty string. Not a "null". This is a UI layer issue, not a > database issue. > > > I have no idea why I got such hard pushback on this. This is the > > EXACT same behaviour other types use. If a number field is presented > > to the user and submitted with no value, NULL Is inserted. Not zero, > > which is the numeric equivalent of the empty string, but NULL. Same > > with date types. Why not say they entered '1/1/1970' by default if > > they entered nothing? > > Ah, no. An empty string is not a valid number -- in fact, it is the > absence of a number; the same goes for dates. An empty string, > however, is a valid string, since a string is (in this context) > defined as a sequence of 0 to n characters.) > > Your patch is awful because it would mean there was no way to enter an > empty string in the database. A one-character string containing a > single space is not an empty string. Yeah, it is awful ;^) However the existing system is equally awful because there is no way to enter NULL! Properly implemented, the rails model would allow you to indicate nullability and use null if no data is provided. - Ian > > Alexander. >
On 6/3/07, Ian Harding <harding.ian@gmail.com> wrote:
> On 6/3/07, Alexander Staubo <alex@purefiction.net> wrote:
> > Your patch is awful because it would mean there was no way to enter an
> > empty string in the database. A one-character string containing a
> > single space is not an empty string.
>
> Yeah, it is awful ;^) However the existing system is equally awful
> because there is no way to enter NULL!
But there is. One could, quite convincingly, I think, argue that the
parsing of '' (empty string) into nil/null is data model-specific. One
solution, then, is to add this rule to the model:
class User < ActiveRecord::Base
...
def description=(value)
value = nil if value.blank?
self.write_attribute(:description, value)
end
end
You can easily refactor this into a plugin, which you could then invoke thus:
class User < ActiveRecord::Base
null_when_empty :description
...
end
This is getting very Rails-specific, so I'll stop here. I would be
happy to send you the code (it's probably around 15 lines) for such a
plugin privately if you like.
> Properly implemented, the rails model would allow you to indicate
> nullability and use null if no data is provided.
The preferred approach nowadays is not to clutter the Rails (or in
this case, ActiveRecord) core unduly with all sorts of app-specific
solutions, and instead move code out into plugins. Plugins that, over
time, prove to be universally useful, would be considered for
inclusion into the core. So a plugin is a start.
Alexander.
> Yeah, it is awful ;^) However the existing system is equally awful > because there is no way to enter NULL! Consider this form : First name : Edgar Middle name : J. Last name : Hoover Now, if someone has no middle name, like "John Smith", should we use NULL or "" for the middle name ? NULL usually means "unknown" or "not applicable", so I believe we have to use the empty string here. It makes sense to be able to concatenate the three parts of the name, without having to put COALESCE() everywhere. Now consider this form : City : State : Country : If the user doesn't live in the US, "State" makes no sense, so it should be NULL, not the empty string. There is no unnamed state. Also, if the user does not enter his city name, this does not mean he lives in a city whose name is "". So NULL should be used, too. It is very context-dependent.
On Mon, Jun 04, 2007 at 12:37:42AM +0200, PFC wrote:
> NULL usually means "unknown" or "not applicable"
Aaaargh! No, it doesn't. It means NULL. Nothing else.
If it meant unknown or not applicable or anything else, then
SELECT * FROM nulltbl a, othernulltbl b
WHERE a.nullcol = b.nullcol
would return rows where a.nullcol contained NULL and b.nullcol
contained NULL. But it doesn't, because !(NULL = NULL).
It's too bad indeed that the originators of SQL used three-value
rather than five-value logic, but this is what we have. If you
happen to want to use NULL to mean something specific in some
context, go ahead, but you shouldn't generalise that to "usually
means" anything.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Everything that happens in the world happens at some place.
--Jane Jacobs
On 6/3/07, PFC <lists@peufeu.com> wrote: > > > Yeah, it is awful ;^) However the existing system is equally awful > > because there is no way to enter NULL! > > Consider this form : > > First name : Edgar > Middle name : J. > Last name : Hoover > > Now, if someone has no middle name, like "John Smith", should we use NULL > or "" for the middle name ? "NMN" for No Middle Name. http://www.google.com/search?hl=en&q=data+standards+no+middle+name+NMN&btnG=Search The hazard with doing stuff like that is some joker could name their kid Billy NMN Simpson. Or this http://www.snopes.com/autos/law/noplate.asp If the the "None" identifier can't be guaranteed to not conflict with data, the best thing is a boolean for "None". > NULL usually means "unknown" or "not applicable", so I believe we have to > use the empty string here. It makes sense to be able to concatenate the > three parts of the name, without having to put COALESCE() everywhere. > Null always means unknown. N/A usually means Not Applicable. I use COALESCE once in a view and never again. > Now consider this form : > > City : > State : > Country : > > If the user doesn't live in the US, "State" makes no sense, so it should > be NULL, not the empty string. There is no unnamed state. Also, if the > user does not enter his city name, this does not mean he lives in a city > whose name is "". So NULL should be used, too. > There are states in other countries, but I get your meaning. But if someone doesn't enter their middle name, that doesn't mean their parents named them Billy "" Simpson either, right? I think there is an argument for filling fields with empty strings where they are _known_ not to exist but they are _applicable_ but I don't do it. I prefer the consistency of NULL for absent data versus WHERE (mname = '' OR mname IS NULL). Again, the user failing to enter it when presented an opportunity does not meet the "known not to exist" test for me. > It is very context-dependent. > Yeah, unless you are a stubborn old null zealot like me! - Ian
At 12:37 AM +0200 6/4/07, PFC wrote: >>Yeah, it is awful ;^) However the existing system is equally awful >>because there is no way to enter NULL! > >Consider this form : > >First name : Edgar >Middle name : J. >Last name : Hoover > >Now, if someone has no middle name, like "John Smith", should we use >NULL or "" for the middle name ? >NULL usually means "unknown" or "not applicable", so I believe we >have to use the empty string here. It makes sense to be able to >concatenate the three parts of the name, without having to put >COALESCE() everywhere. > >Now consider this form : > >City : >State : >Country : > >If the user doesn't live in the US, "State" makes no sense, so it >should be NULL, not the empty string. There is no unnamed state. >Also, if the user does not enter his city name, this does not mean >he lives in a city whose name is "". So NULL should be used, too. > >It is very context-dependent. My take on the NULL philosophy is that NULL should indicate that no data has been entered. If the data for the record is not applicable, then it should have a zero length string, indicating that the field has been considered by the user, and that a blank value is appropriate. A NULL field on an entered record should indicate an error condition, rather than that the field is not appropriate to the context. Thus, NULL fields on a completed record would mean either that they were never presented to the user (thus, did not appear in the UI), or an error condition. The advantages to this is that, if enforced, a count of the non-null records will show those operated on by a user, vs. those untouched by a user. -Owen
Andrew Sullivan wrote: > On Mon, Jun 04, 2007 at 12:37:42AM +0200, PFC wrote: >> NULL usually means "unknown" or "not applicable" > > Aaaargh! No, it doesn't. It means NULL. Nothing else. > > If it meant unknown or not applicable or anything else, then > > SELECT * FROM nulltbl a, othernulltbl b > WHERE a.nullcol = b.nullcol > > would return rows where a.nullcol contained NULL and b.nullcol > contained NULL. But it doesn't, because !(NULL = NULL). Well, a strict "unknown" is fine - so long as it means just that. How tall is Andrew? Unknown How tall is Richard? Unknown Are Andrew and Richard the same height? Unknown The problem is the slippery-slope from "unknown" to "not applicable" to "user refused to answer" to ...whatever Part of it is the poor support for out-of-band values. In many cases what people want is the ability to have a value of type 'number in range 1-20 or text "n/a"' and there's not a simple way to provide that, so they use null. -- Richard Huxton Archonet Ltd
On Mon, 4 Jun 2007, Ian Harding wrote: > The hazard with doing stuff like that is some joker could name their > kid Billy NMN Simpson. Or this > http://www.snopes.com/autos/law/noplate.asp That settles it; I'm getting custom plates with NULL on them just to see if it makes it impossible for me to be sent a ticket. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On 6/4/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote: > On Mon, Jun 04, 2007 at 12:37:42AM +0200, PFC wrote: > > NULL usually means "unknown" or "not applicable" > > Aaaargh! No, it doesn't. It means NULL. Nothing else. > > If it meant unknown or not applicable or anything else, then > > SELECT * FROM nulltbl a, othernulltbl b > WHERE a.nullcol = b.nullcol > > would return rows where a.nullcol contained NULL and b.nullcol > contained NULL. But it doesn't, because !(NULL = NULL). I don't disagree with the principle, but that's a specious argument. Who says (unknown = unknown) should equal true? Alexander.
On Mon, Jun 04, 2007 at 03:38:01PM +0100, Richard Huxton wrote:
> Well, a strict "unknown" is fine - so long as it means just that.
> How tall is Andrew? Unknown
> How tall is Richard? Unknown
> Are Andrew and Richard the same height? Unknown
>
> The problem is the slippery-slope from "unknown" to "not applicable" to
> "user refused to answer" to ...whatever
While you do well to point out that I have equivocated on "unknown"
(in my usual twitchy way whenever NULLs come up, I am told), your
example actually illustrates part of the problem. There are NULLs
that are actually just local absences of data (you don't know how
tall I am), NULLs that are in fact cases of 'no such data' (the full
name that 'S' stands for in Harry S Truman -- Truman's middle name
was in fact just S), NULLs that are data nobody knows (unlike the
mere locally-unknown data: "When the tree fell in the woods with
nobody around to hear it, did it make a sound?"), and NULLs that are
the data in response to questions that can't be answered, ("What
exists after the end of the universe?")
See, this is what happens when you study the wrong things in school.
You start to think that logic and metaphysics are somehow related to
one another. :-/
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
> Aaaargh! No, it doesn't. It means NULL. Nothing else.
Well, x = UNKNOWN doesn't make any sense... the answer is UNKNOWN.
x IS UNKNOWN does make sense, the answer is true or false. Replace
UNKNOWN with NULL...
Actually it means what the DBA wants it to mean (which opens the door to
many a misguided design...)
I hereby light a candle to the pgsql designers who didn't inflict
00-00-0000 00:00:00 upon us besides NULL dates.
Alexander Staubo írta: > On 6/4/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote: >> On Mon, Jun 04, 2007 at 12:37:42AM +0200, PFC wrote: >> > NULL usually means "unknown" or "not applicable" >> >> Aaaargh! No, it doesn't. It means NULL. Nothing else. >> >> If it meant unknown or not applicable or anything else, then >> >> SELECT * FROM nulltbl a, othernulltbl b >> WHERE a.nullcol = b.nullcol >> >> would return rows where a.nullcol contained NULL and b.nullcol >> contained NULL. But it doesn't, because !(NULL = NULL). > > I don't disagree with the principle, but that's a specious argument. > Who says (unknown = unknown) should equal true? NULL means "value doesn't exist" and for your amusement, here's an analogy why !(NULL = NULL). Prove the following statement: every fairy has black hair. For proving it, let's suppose that there exists a fairy that's hair isn't black. But fairies don't exist. QED. Now replace the above statement with another one, possibly with one that contradicts with the statement above. Along the same lines, every statements can be proven about non-existing things, even contradicting ones. Best regards -- ---------------------------------- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/
Richard Huxton wrote: PFC wrote: >>> NULL usually means "unknown" or "not applicable" Andrew Sullivan wrote: >> Aaaargh! No, it doesn't. It means NULL. Nothing else. >> If it meant unknown or not applicable or anything else, then >> SELECT * FROM nulltbl a, othernulltbl b >> WHERE a.nullcol = b.nullcol >> >> would return rows where a.nullcol contained NULL and b.nullcol >> contained NULL. But it doesn't, because !(NULL = NULL). (a == b) <=> ( (a -> b) AND (b -> a)) | a | b | a->b | b->a | a==b | |----|-----|------|------|------| | F | F | T | T | T | | F | T | T | F | F | | F | U | U | U | U | | T | F | F | T | F | | T | T | T | T | T | | T | U | U | T | U | | U | F | U | U | U | | U | T | T | U | U | | U | U | U | U | U | Ergo, (UNKNOWN = UNKNOWN) is UNKNOWN. Similarly for (UNKNOWN != UNKNOWN). Where NULL differs is that (NULL = NULL) is FALSE, and (NULL != NULL) is FALSE. The similarity is that with NULL, SQL is not exactly saying (NULL = NULL) is FALSE so much as that it's not TRUE. NULL follows Zen-valued logic, not 3-valued, and that seems somehow appropriate to me. -- Lew
"Lew" <lew@nospam.lewscanon.com> writes: > Where NULL differs is that (NULL = NULL) is FALSE, and (NULL != NULL) is FALSE. No, that's not true. NULL=NULL is NULL. And NULL!=NULL is NULL as well. Ie, it's exactly as your table describes. The confusion comes because WHERE clauses treat NULL the same as they treat FALSE, ie, they exclude the row. But unless you can come up with a way for a SELECT clause to not tell you whether it's including a row or not (ie, whether it includes the row is "unknown") then it's got to pick one or the other. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com