Well, NULL is not a value, technically, it is the lack of a value. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Wednesday, June 25, 2008 11:12 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug with NULL in NOT IN
Why should the second query return zero rows? Clearly ids 1 and 2 don't exist in b. I'm not defending sqlite per se, just asking, logically speaking, why would those other databases return zero rows for that query? On a related note, what if NULL exists in both tables? Sqlite doesn't return that row for the first query: SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table a(id integer); sqlite> insert into a values(1); sqlite> insert into a values(2); sqlite> insert into a values(3); sqlite> insert into a values(NULL); sqlite> create table b(id integer); sqlite> insert into b values(NULL); sqlite> insert into b values(3); sqlite> insert into b values(4); sqlite> insert into b values(5); sqlite> select * from a where id in (select id from b); 3 sqlite> Sqlite deliberately ignores all NULL values in a select. This explains the result, but logically doesn't follow because in fact NULL does exist in both tables. Interesting. RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Wednesday, June 25, 2008 11:50 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug with NULL in NOT IN 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
