On Friday, 6 March, 2020 17:48 Xinyue Chen <chenx...@uw.edu> 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
