On Jun 25, 2008, at 11:33 AM, [EMAIL PROTECTED] wrote: > Hello, > > with the following schema and contents: > > BEGIN TRANSACTION; > CREATE TABLE a(id INTEGER); > INSERT INTO a VALUES(1); > INSERT INTO a VALUES(2); > INSERT INTO a VALUES(3); > CREATE TABLE b(id INTEGER); > INSERT INTO b VALUES(NULL); > INSERT INTO b VALUES(3); > INSERT INTO b VALUES(4); > INSERT INTO b VALUES(5); > COMMIT; > > mysql, postgres, sqlite and mssql agree on: > > SELECT * FROM a WHERE id IN (SELECT id FROM b); > > yielding one row with id=3. > > However, on the query: > > SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); > > mysql, postgres and mssql correctly return zero rows. SQLite > however returns two rows, for id=1 and id=2. > > http://www.sqlite.org/nulls.html doesn't list it, so perhaps > this has never come up before.
No, this has never come up before. The behavior of SQLite is as I intended it to be. NULLs are deliberately and willfully filtered out of the subquery to the right of NOT IN. Are you saying that this is incorrect? Other than the fact that three other database engines do it differently, do you have any evidence that this really is incorrect? NULL behavior in SQL is highly unintuitive. In fact, as far as I can tell it is arbitrary. Can you or anybody else point to text in any SQL spec that would suggest that SQLites behavior in this case is wrong? D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
