Dan wrote: > Wow (applauds). That was incredible! > Thanks (bows). :-) > So the upshot is that if a set used with "NOT IN" contains a NULL, > the "NOT IN" operation will never evaluate to true. It may evaluate to > false, it may evaluate to NULL. But never true. > > If we have the expression: > > "x NOT IN (NULL, y, z)" > > this should be equivalent to what SQLite does for: > > "CASE WHEN x NOT IN (y, z) THEN NULL ELSE 0 END" > > (assuming x is itself not NULL). > > Is that correct? > Yes, I believe that is correct. > Do we also have a similar problem with the regular 'IN' operator? In > SQLite at the moment: > > SQLite version 3.6.0 > sqlite> select 1 IN (null, 2, 3), 2 IN (null, 2, 3); > 0, 1 > > Should the leftmost column of the result row should be NULL, not "0"? > Since rule (d) above is not true for "1 IN (null, 2, 3)", do we fall > through to rule (e) and return NULL? > > Yes, that looks to be the case.
Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
