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

Reply via email to