On 2020/03/07 03:52, Xinyue Chen wrote:
Hi,
If I change IS NOT FALSE to IS TRUE, the results will be different. I
assume they should perform in the same way?
if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
also be always true. Then why doesn't that query also return 4 rows?
Some excellent answers were already given, but in case you still wonder...
In Boolean logic, a value can only be TRUE or FALSE. But in SQL (which
has to model the real World Algebraically) there is also the possibility
that the state is simply NOT KNOWN (or indeed that a
variable/placeholder/identifier can represent no value at all).
The expression (Null = Null) is always NULL - it's like asking if an
unknown person is exactly the same person as another unknown person? The
answer is obviously "We don't know. It might be, so we cannot say
definitively it ISN'T the case, but it might also NOT be the same
person, so the only correct answer is: We don't know".
Further, "We don't know" in logic terms is undefined, which in SQL we
write as "NULL".
While (Null = Null) in mathematical terms is always unknown, we can
however test if two values are of the same kind with "is", and more
specifically, test if they are both unknown, so the expression (NULL is
NULL) correctly returns True.
This whole "Three possible states" thing is no longer simply Boolean
logic, but indeed Trivalent logic with the possible values being
NULL/TRUE/FALSE.
Writing the matrix of states of (x IS [NOT] y) down and numbering them
we get 9 symantically distinct evaluations (there are more, like "FALSE
is TRUE", but they can be rearranged as one of these):
1. NULL is NULL = Yes, True,
2. NULL is FALSE = Nope, False.
3. NULL is TRUE = Nope, False.
4. NULL is NOT NULL = Nope, False,
5. NULL is NOT FALSE = Yep, True.
6. NULL is NOT TRUE = Yep, True.
7. TRUE is FALSE = Nope, False.
8. TRUE is NOT FALSE = Yep, True.
9. FALSE is NOT TRUE = Yep, True.
Thus when you ask:
"I assume they should perform in the same way?
if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE'
should also be always true. Then why doesn't that query also return 4 rows?"
You assume that state 5 (NULL IS NOT FALSE) and state 3 (NULL IS TRUE)
would mean the same thing, but as you can see from the list, in
Trivalent logic it clealy doesn't - one is False and the other is True.
The stuff of nightmares to a purist, I know. In the real World though,
some stuff simply isn't known and therefore cannot fit into the simple
Boolean logic of TRUE and FALSE.
Best of luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
