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

[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users