Rami, I agree with many of your opinions but I can't see any choice there.
>> >> 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. I only say that Unknown it's a logic term or synonymous of a null value in boolean expression. In a tri-state truth's tables you will found TRUE,FALSE and UNKNOWN ; but in all dbms I know is INTERNALLY implemented as a null value. >> 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" > Irregular (abnormal, exceptional, strange, wrong) is the legal possibility to get a NULL value of NULL type from a column or expression of another type. Irregularity in the sql types definition. > 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. > I say: (NULL = NULL) = NULL= UNKNOWN != TRUE != FALSE ; and indeed: NULL and NULL = NULL TRUE and NULL = NULL FALSE and NULL = FALSE NULL or NULL = NULL TRUE or NULL = TRUE FALSE or NULL = NULL >> 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); > This is neither consistent nor elegant. I agree. > Also keep in mind that a particular database implementation is completely > separate from sql standard. > 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. Out there are too many databases to living in their own island. Standards are a continuous work in progress, but the only way achieve code/effort reusability and interoperability. I believe too that standards are an important thing. >> (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.... > It's just ugly syntax ; but at the end you must to evaluate a logical condition and don't a value to evaluate. >>> 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. This was the reason of my original post. In this point, I agree and like the way Postgresql documentation explain why for a CHECK CONSTRAINT, null values have the same effect a true condition. In the same line of all this thread, again NULL value in a CHECK CONSTRAINT mean NO VALUE TO BE CHECKED , and then doesn't constraint that row. And like Thomas has said, if your intention is exclude nulls you must explicitly use IS NOT NULL in the check condition or at column definition. > I am just taking it day by day. > Would that be ok? Great and good for you. Thanks for the lively exchange of ideas and regards, Dario. -- 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.
