subject:"\[sqlite\] BUG\?"

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

2020-03-07 Thread Joe Mucchiello

 I just want to point something out that might help the original poster.

On Saturday, March 7, 2020, 7:00:21 AM EST, 
sqlite-users-requ...@mailinglists.sqlite.org 
 wrote:  > 
> 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.
This explanation 100% correct and probably 80% confusing without the following, 
especially because "is" is not capitalized:IS and IS NOT are logical operators 
in SQL. NOT is not a unary operator when preceded by IS.

In most non-relational languages "NULL IS NOT TRUE" is parsed as:

value(NULL) operator(IS) (operator(NOT) value(TRUE)). 

That is NOT how SQL works. In SQL, it is:

value(NULL) operator(IS NOT) value(TRUE).


  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

2020-03-06 Thread R.Smith


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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

2020-03-06 Thread Richard Damon

On 3/6/20 9:54 PM, Keith Medcalf wrote:

On Friday, 6 March, 2020 19:25, Richard Damon wrote:

It is sort of like NaN, where a Nan is neither less than, greater than
or equal to any value, including itself.

NULL (as in SQL NULL) means "missing value" or "unknown". NULL represents any value
within the domain, we simply do not know what that value is. That is, the value "NULL" for colour
of a car means that we do not know the colour -- however, it still has one.

NaN, on the other hand, means that the value is outside the domain and that
there is no possible value of the domain which well render the proposition true.

For example, the state of Schroedingers Cat is NULL. It has a state, either
dead or alive. That state is merely unknown until one looks in the box.
However, if when you looked in the box there was no cat, then the cat would be
a NaN since its state was outside of the domain of states for a cat in a box
with a time release poison after the release of the poison, that is, the
non-existance of a cat in the box precludes the possibility of the state of the
cat in the box being either either dead or alive.

It may have a different meaning, but similar effects on logic.

As an aside, for the quantum effect Schrodinger's Cat is designed to
demonstrate, the cat ISN'T just one of dead or alive but not know which,
but exists as a probability wave between the two states. This is why the
photon which goes through one of two slits generates an interference
pattern unless you detect which slit it goes through, if you measure the
slit it went through, you get a different pattern of light, as the lack
of knowledge allows it to be less precise in its position and the
probability of going through the left slit interferes with the
probability of that same particle going through the right slit, so the
pattern implies it sort of went through both at once.

1 2 3 4 5 6 7 8 9 10 >

1 - 100 of 1902 matches

Mail list logo