On Jun 26, 2008, at 4:49 AM, Dennis Cote wrote: > D. Richard Hipp wrote: >> >> If I understand Peter correctly, he is saying that NULL should mean >> "unknown" in the context of the RHS of a NOT IN operator. SQLite >> does >> not currently operate this way. SQLite currently interprets a >> NULL in >> the RHS of a NOT IN operator to mean "nothing". >> >> Can you or anybody else point to text in any >> SQL spec that would suggest that SQLites behavior in this case is >> wrong? > > > I believe that your interpretation is correct, and that SQLite's > current > behavior is incorrect according to the standard, at least the SQL:1999 > standard. > > The IN predicate is a synonym for the quantified predicate = ANY, or = > SOME which is equivalent. The inversion of this predicate, NOT IN, is > therefore the inversion of the quantified predicate. The rules defined > in section 8.4 <in predicate> Syntax Rules 3 and 4 show how this is > transformed. > > col NOT IN subquery > > becomes > > NOT col IN subquery > > which becomes > > NOT (col = SOME subquery) > > And the inversion of the quantified predicate is > > col <> ALL subquery > > In the standard the rules for evaluating this quantified subquery > are in > section 8.8 <quantified comparison predicate> General Rules 1 > through > 2e copied below. > > 1) Let R be the result of the <row value expression> and let T be > the result of the <table subquery>. > 2) The result of ‘‘R <comp op> <quantifier> T’’ is derived by the > application of the implied <comparison predicate> ‘‘R <comp op> > RT’’ to > every row RT in T: > Case: > a) If T is empty or if the implied <comparison predicate> is > true for every row RT in T, then ‘‘R <comp op> <all> T’’ is true. > b) If the implied <comparison predicate> is false for at > least > one row RT in T, then ‘‘R <comp op> <all> T’’ is false. > c) If the implied <comparison predicate> is true for at least > one row RT in T, then ‘‘R <comp op> <some> T’’ is true. > d) If T is empty or if the implied <comparison predicate> is > false for every row RT in T, then ‘‘R <comp op> <some> T’’ is false. > e) If ‘‘R <comp op> <quantifier> T’’ is neither true nor > false, > then it is unknown . > > For the example given, the engine evaluates the following predicates. > > 1 <> ALL (NULL, 3, 4, 5) > 2 <> ALL (NULL, 3, 4, 5) > 3 <> ALL (NULL, 3, 4, 5) > > In each case the first comparison is > > X <> NULL > > And from 8.2 <comparison predicate> General Rules 1a we have > > a) If either XV or YV is the null value, then > X <comp op> Y > is unknown. > > The results of the comparisons are therefore: > > (unknown, true, true, true) > (unknown, true, true, true) > (unknown, false, true, true) > > So all three rows result in an unknown result for the first, NULL, > element. The last row is handled buy case b in section 8.8 General > Rule > 2 above since one subquery result is false. The other rows all fall > through to case e in section 8.8 General Rule 2 above. The result of > each quantified comparison is therefore unknown. > > The where clause only returns rows where the condition is true > according > to section 7.8 <where clause> General Rule 1. > > 1) The <search condition> is applied to each row of T. The result > of the <where clause> is a table of those rows of T for which the > result > of the <search condition> is true. > > It looks like this should be changed to match the other database > engines > for improved standard compliance.
Wow (applauds). That was incredible! 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? 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? Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
