Hey friend don't get upset, I didn't create the sql and can't change
it either!
Greetings brother!
I hardly implied any such thing ;-)
What's your point, do you want to change the sql ?
SQL is an international standard and I am not part of that board so
obviously that is out of my league.
However I do want to discuss the standard as all people using any
standard should.
I suppose that you never ever has needed to create a table with a
nullable column, right? Why?
Wrong. I use them constantly. And as I mentioned earlier they are a
marker of open-endedness of a system.
Very useful in real world situations.
Maybe nulls aren't so out of the world as you think.
I wonder what you mean by that.
I said it is justifiable to view nulls as not the same kind of values as
other values are in a database system.
The reason is that they say that a certain variable (value
holder/container) is in an undefined/unknown state.
Thus they talk about the state of a variable, they are not the state of
a variable.
"meaningless value" : what part of the sentence "absence of a know
value" don't understand ?
I meant I do not understand what means
"null value is an irregularity in any other data type"
Maybe you could tell me what is an irregularity in a data type?
I mean NULL is not equal to any value of any other type (even to itself).
Try in any database :
select null=null;
select cast(null as boolean) = cast(null as boolean) ;
select cast(null as boolean) = 1;
return NULL always.
Well one of the reasons you get a null from jdbc is that java's boolean
type does not have a third truth value UNKNOWN.
So even though conceptually these evaluate to UNKNOWN the implementation
has decided to return null instead of UNKNOWN.
> null value is an irregularity in any other data type
I can not figure out what this sentence could possibly mean.
SQL is a typed language, so if you select a column of INTEGER type
it's irregular (or wrong) that can obtain a value of type NULL.
Sorry, can't understand especially this part
> "it's irregular (or wrong) that can obtain a value of type NULL"
> that only express or mean the absence of a known value
So if null means that a value is missing then it clearly is a metavalue.
Value talking about the values.
And thus I feel understanding toward the position that NULL IS NOT A
VALUE like the other values in the database.
> UNKNOWN is a descriptive term used for a NULL value in the
evaluation of an expression of boolean type.
That sentence can not possibly mean anything.
Here the first paragraph from Wikipedia article Null (SQL)
Heading: History
"Null was introduced by E. F. Codd as a method of representing
missing data in the relational model
<http://en.wikipedia.org/wiki/Relational_model>. Codd later
reinforced his requirement that all RDBMS' support Null to indicate
missing data in a two-part series published in /ComputerWorld/
magazine.^[1]
<http://en.wikipedia.org/wiki/Null_%28SQL%29#cite_note-0> ^[2]
<http://en.wikipedia.org/wiki/Null_%28SQL%29#cite_note-1> Codd also
introduced a ternary (three-valued)
<http://en.wikipedia.org/wiki/Ternary_logic> logic, consisting of the
truth values <http://en.wikipedia.org/wiki/Truth_value> True, False,
and Unknown, which is closely tied to the concept of Null. The
Unknown truth value is generated whenever Null is compared with any
data value, or with another Null."
My English is bad but not so bad to not understand that this citation
match the above concepts.
Previously you say: "I mean NULL is not equal to any value of any other
type (even to itself)"
Wikipedia says: "The Unknown truth value is generated whenever Null is
compared with any data value, or with another Null."
So by deduction you say:
NULL = NULL == FALSE
and wikipedia says
NULL = NULL == UNKNOWN
there must be a difference somewhere.
> In any context where a expression must to be evaluated for a
boolean result ( for example in a where clause)
> if any operand is null then the expresion value is UNKNOWN having
no effect in their context.
What does "having no effect in their context"?
Does it mean that the sql statement is silently ignored?
That's what having no effect could mean.
ok, that was an imprecise and unfortunate phrase.
For context I refer to, for example, a boolean expression in a select
list or the same boolean expression in a where clause.
The difference it's that a null boolean exp. selected as value can be
externalized as a null value, but the same exp. in a where clause or
check constraint it's evaluated as UNKNOWN with the three-state
truth's table.
To see my point just try this on any dbm:
select cast(null as boolean) as x ;
select cast(null as boolean) as x where cast(null as boolean);
Agreed.
But to me this seems that to the outside world the database pretends
that boolean type has only two values (and can be null).
Whereas internally it has three values in boolean type and does not
allow the boolean expression evaluate to null
(and thus throw a nullPointerException whenever a where clause can not
be evaluated properly).
This is neither consistent nor elegant.
So you say that a boolean expression evaluates to UNKNOWN, yet
UNKNOWN is just a descriptive term for NULL.
Why not just say that boolean expression can evaluate to value NULL.
But since NULL is not of boolean type but of type NULL (according to
your sources) then the boolean expression
like COL1 = COL2 can evaluate to null so the expression does not have
ONE but TWO possible types: Boolean and Null.
(So we need to evaluate the type of the expression at runtime, like
in javascript).
Again, don't believe, try it yourself : select cast(null as boolean) =
cast(null as boolean) ;
Maybe this needs repetition.
Database represents it's unknown truth value to external world as null
because eg. in java the third truth value is not supported.
I think this inconsistency is the hidden reason why all the databases
have been so reluctant to implement BOOLEAN datatype.
There are two concepts of the same type, fighting for supremacy.
Three-valued logic is bad news but one must honor the rules of logic.
And the sql board has gotten into trouble with this issue.
Also keep in mind that a particular database implementation is
completely separate from sql standard.
One proof being that Thomas just found out there is such a thing as a
third truth value in sql in the first place.
I don't mind. I think sql is a juggernaut and not to be taken too literally.
It is just one piece in the puzzle of database compatibility that seems
important to Thomas.
(NULL>1 ) = null = UNKNOWN
NULL is equal to UNKNOWN means that 2 separate values from two separate
types are considered equal
NULL:NULL == BOOLEAN:UNKNOWN
Hmm....
So what does the where-clause do?
Constraint the result set with unknown is equal to the empty set (
because you can assure that condition it's true).
Does it include the row?
No.
So where-clause evaluating to UNKNOWN causes it to behave the same as
evaluating to FALSE.
As a proof to the non-intuitiveness of null's position between true and
false remember
that Thomas decided that a constraint passes when the expression
evaluates to true or null.
And in your model the where clause works like null is equal to false.
Rami, I think you should drink less coffee and eat more fiber ... ;-)
Take it easy and regards,
Dario.
I also should quit smoking cigarettes and and cut down on my alcohol
consumption
(not to mention the spliffs...)
I am just taking it day by day.
Would that be ok?
- 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.
