Re: NULLS and User Input WAS Re: [GENERAL] multimaster
Alexander Staubo írta: On 6/4/07, Andrew Sullivan <[EMAIL PROTECTED]> 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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
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- 00:00:00 upon us besides NULL dates. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
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 | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
On 6/4/07, Andrew Sullivan <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
On 6/3/07, PFC <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
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 | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
On 6/3/07, Ian Harding <[EMAIL PROTECTED]> wrote: On 6/3/07, Alexander Staubo <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
On 6/3/07, Alexander Staubo <[EMAIL PROTECTED]> wrote: On 6/3/07, Ian Harding <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: NULLS and User Input WAS Re: [GENERAL] multimaster
On 6/3/07, Ian Harding <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
NULLS and User Input WAS Re: [GENERAL] 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
