Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
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?
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?
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. Detecting the state of Schrodinger's Cat actually changes its state, collapsing the wave into one of the definitive states. -- Richard Damon ___ 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?
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. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ 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?
On 3/6/20 9:00 PM, Simon Slavin wrote: On 7 Mar 2020, at 1:52am, Xinyue Chen wrote: If I change IS NOT FALSE to IS TRUE, the results will be different. I assume they should perform in the same way? No. Because NULL is not TRUE and is not FALSE. SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT NULL IS TRUE; 0 sqlite> SELECT NULL IS FALSE; 0 sqlite> SELECT NULL IS NOT TRUE; 1 sqlite> SELECT NULL IS NOT FALSE; 1 Once you can have NULL values, you have to know the rules very well when you apply logic. Other values make sense, but NULL is not logical. It is sort of like NaN, where a Nan is neither less than, greater than or equal to any value, including itself. -- Richard Damon ___ 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?
On 3/6/2020 8:52 PM, Xinyue Chen wrote: If I change IS NOT FALSE to IS TRUE, the results will be different. NULL IS TRUE is always false. NULL IS NOT FALSE is always true. So it's not surprising that the results are different. SQL uses trivalent logic. NULL is neither FALSE nor TRUE. I assume they should perform in the same way? You assume incorrectly. if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should also be always true. False. -- Igor Tandetnik ___ 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?
On 7 Mar 2020, at 1:52am, Xinyue Chen wrote: > If I change IS NOT FALSE to IS TRUE, the results will be different. I > assume they should perform in the same way? No. Because NULL is not TRUE and is not FALSE. SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT NULL IS TRUE; 0 sqlite> SELECT NULL IS FALSE; 0 sqlite> SELECT NULL IS NOT TRUE; 1 sqlite> SELECT NULL IS NOT FALSE; 1 Once you can have NULL values, you have to know the rules very well when you apply logic. Other values make sense, but NULL is not logical. ___ 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?
> 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. "NULL IS NOT FALSE" is true because NULL is not a value therefor it is not FALSE, because FALSE is a value. "NULL IS TRUE" is false because NULL is not a value so it's not TRUE. You can't compare NULL with anything. All you can do is tell if it "IS NULL" or "IS NOT NULL". ___ 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?
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? Thanks! On Fri, Mar 6, 2020 at 5:45 PM Peter da Silva wrote: > Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no > value, you have to check for it explicitly. > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ 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?
On Friday, 6 March, 2020 17:48 Xinyue Chen wrote: ... >select t1.textid a, i.intid b > from t t1, > i i > where (t1.textid = i.intid and t1.textid in (12) and t1.textid = i.intid) >or (t1.textid = null IS NOT FALSE) >group by i.intid, t1.textid; I got rid of all the extra brackets to make this easier to read. The where clause wants either (a bunch of and joined conditions) to be true OR (another condition to be true). We will ignore the first set of AND joined conditions since they appear to do what you want and instead deal with the handling of NULL values and tri-state logic from the second OR joined condition, which appears to be the one causing problems. The expression (t1.textid = null) is always null (it is neither True nor False), no matter what the value of t1.textid because any value compared to NULL is NULL. If you want to know whether t1.textid is null then you write "t1.textid is null" or (conversely) "t1.textid is not null" NULL is FALSE -> False (NULL is not False) NULL is TRUE -> False (NULL is not True either) NULL is not FALSE -> True (it is True that NULL is not FALSE) NULL is not TRUE -> True (it is True that NULL is not TRUE) Since the condition that you have specified (t1.textid = NULL) IS NOT FALSE will always be true, then the logic value of condition on the "other side" of the OR is irrelevant -- the WHERE clause will always be TRUE. This condition holds for any not null value you use in place of TRUE or FALSE. That is: NULL is 1 -> False (NULL is not 1) NULL is 0 -> False (NULL is not 0 either) NULL is not 1 -> True (NULL is indeed not 1) NULL is not 2 -> True (NULL is indeed not 2) This result will be the same if you change the IS NOT FALSE to IS NOT TRUE. However, if you specify IS TRUE or IS FALSE then this expression will always be FALSE and the value of the WHERE clause will depend on the result of the first set of AND joined conditions. So your original query must and always devolves to: select t1.textid a, i.intid b from t t1, i i group by i.intid, t1.textid; for which the correct results are produced. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ 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?
Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no value, you have to check for it explicitly. ___ 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?
On 3/6/20, Xinyue Chen wrote: > -- Buggy query > select t1.textid a, i.intid b > from t t1, > i i > where (((t1.textid = i.intid) and (t1.textid in (12)) and (t1.textid = > i.intid)) or ((t1.textid = null) IS NOT FALSE)) > group by i.intid, t1.textid; (1) The expression "t1.textid=null" is always NULL. (2) The expression "NULL IS NOT FALSE" is always true. (3) The WHERE clause expression "... OR true" is always true. Hence, the query above simplifies to just "SELECT * FROM t, i;". That query should return 4 rows, just as you show. I think it is working correctly. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite version 3.31.1 and 3.32?
Hi,
I am not sure if I found a bug in SQLite version 3.31.1 and 3.32. Could you
please check?
CREATE TABLE t (
textid TEXT
);
INSERT INTO t
VALUES ('12');
INSERT INTO t
VALUES ('34');
CREATE TABLE i (
intid INTEGER PRIMARY KEY
);
INSERT INTO i
VALUES (12);
INSERT INTO i
VALUES (34);
CREATE TABLE e (
x INTEGER PRIMARY KEY NOT NULL,
y TEXTNOT NULL
);
-- Original query
select t1.textid a, i.intid b
from t t1,
i i
where ((t1.textid = i.intid) and (t1.textid = 12));
-- Buggy query
select t1.textid a, i.intid b
from t t1,
i i
where (((t1.textid = i.intid) and (t1.textid in (12)) and (t1.textid =
i.intid)) or ((t1.textid = null) IS NOT FALSE))
group by i.intid, t1.textid;
The result for the original query is 12|12 but the result for the buggy one
is 12|12, 34|12, 12|34, 34|34. If I change the IS NOT FALSE to IS TRUE, the
result will be 12|12, same to the original query.
Thanks!
Best,
Xinyue Chen
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
