NULLS and User Input WAS Re: multimaster

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

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&amp;q=data+standards+no+middle+name+NMN&amp;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

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

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/

"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