>> Note: In sql standard NULL <> 1 evaluates to UNKNOWN (a third truth
value) whereas in H2 it evaluates to null (something more obscure)."
> Where did you read that?
I have read in books about sql talking lengthily about the issue of sql
having a third truth value.
That is UNKNOWN. A boolean expression in sql MUST NEVER produce NULL as
it's value.
So clearly NULL in H2 has the same meaning as unknown has (and most
certainly some discrepancies
between the sql spec UNKNOWN and H2 NULL exist.)
> UNKNOWN would be a forth option (TRUE, FALSE,
> NULL, UNKNOWN) - which would be even more complicated. I guess it
> wouldn't be a big problem for H2 to support UNKNOWN, but it would be a
> big problem for the users of H2. Also, it would be a compatibility
> problem (most other databases don't support UNKNOWN).
Together with Chris Date I do not think that 3 truth values is a good
thing for system's complexity.
So instead of introducing fourth truth value UNKNOWN I would like to go
back to 2 truth values.
> The check condition must evaluate to TRUE, FALSE or NULL. TRUE and
NULL mean the
> operation is to be permitted, and FALSE means the operation is to be
> rejected. To prevent NULL in a column, use NOT NULL instead of a check
> constraint.
It was surprising that NULL is interpreted to be closer to true than
false (eg. javascript interprets
null always as false but then again either way is just as justifiable).
> By the way,
> somebody told me NULL is not a value. I don't care that much, but some
> people are really picky about that.
Well that is clearly a matter of definition.
If it is considered not to be a value but something of a meta-value
(standing in place of a real value,
changing it's meaning depending on the context Eg. value missing, value
not known, value inapplicable ....)
then it is not a value but some sort of generic marker that "Hey, here
we don't have a value."
This is the reason why some people oppose nulls in databases (the whole
thing becomes a mess when you can't rely
that you will always encounter a value of a given type in a column or a
row of a table (or more theoretically in an attribute
of a tuple of a relation)).
Examples: What are the truth values of the following expressions that
should evaluate to truth value
1 > NULL
LENGTH("") > LENGTH(NULL) (Would you really like your database to throw
a nullPointerException at your face?)
NULL = TRUE
All these expressions are the genesis of the 3rd truth value (and it
doesn't matter whether it is called UNKNOWN or NULL).
Or maybe it does matter a little because sql defined UNKNOWN instead of
using NULL because as previously mentioned NULL
is not a value whereas UNKNOWN is a value of type BOOLEAN.
There are 2 strategies to avoid three-valued logic:
Chris Date and Hugh Darwen have long been advocating strategies how to
design databases where the use of nulls can be avoided
(essentially it means decomposing every column to it's own table) and
then combining them together and putting some value from within the
type that explains the reason why there is no value there).
Another way would be to give a true or false value to all those
expressions that do not clearly have one.
1 > NULL
We could simply decide that NULL is a clear value within the integer
type and it is smaller (or larger) than any other number.
(Minus or plus infinity, oh yeah people also say that infinity is not a
number but rather a concept concerning numbers).
1 + NULL is obviously NULL
How about
LENGTH("") > LENGTH(NULL)
When defining an operator (LENGTH) the implementor should always decide
what it produces with null value
if he decides that LENGTH(NULL) = NULL then this expression becomes
0 > NULL (Now if Null is defined to be -infinity then this evaluates to
true, if +infinity or 0 then this would be false)
if he decides that LENGTH(NULL) = 0 then this expression becomes
0 > 0 which is clearly false
None of this is very intuitive.
But NULL as such is an intuitive concept.
It represents the open-endedness of every real-world system.
But open-endedness is poison for a logic machine (computer program).
A clearly defined system is so much easier to make consistent
and programmatically elegant (read: bug free).
- rami
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
