Re: [sqlite] bug with NULL in NOT IN
On Wed, Jun 25, 2008 at 05:33:04PM +0200, [EMAIL PROTECTED] wrote: > 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. http://www.sqlite.org/draft/doc/35to36.html notes that 3.6.0 will fix this issue. Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug with NULL in NOT IN
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
Re: [sqlite] bug with NULL in NOT IN
On Thu, Jun 26, 2008 at 04:35:20PM +0700, Dan wrote: > 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? I'd say so, yes - and MySQL and postgres seem to agree too. Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug with NULL in NOT IN
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 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 General Rules 1 > through > 2e copied below. > > 1) Let R be the result of the and let T be > the result of the . > 2) The result of ‘‘R T’’ is derived by the > application of the implied ‘‘R > RT’’ to > every row RT in T: > Case: > a) If T is empty or if the implied is > true for every row RT in T, then ‘‘R T’’ is true. > b) If the implied is false for at > least > one row RT in T, then ‘‘R T’’ is false. > c) If the implied is true for at least > one row RT in T, then ‘‘R T’’ is true. > d) If T is empty or if the implied is > false for every row RT in T, then ‘‘R T’’ is false. > e) If ‘‘R 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 General Rules 1a we have > > a) If either XV or YV is the null value, then > X 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 General Rule 1. > > 1) The is applied to each row of T. The result > of the is a table of those rows of T for which the > result > of the 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
Re: [sqlite] bug with NULL in NOT IN
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 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 General Rules 1 through 2e copied below. 1) Let R be the result of the and let T be the result of the . 2) The result of ‘‘R T’’ is derived by the application of the implied ‘‘R RT’’ to every row RT in T: Case: a) If T is empty or if the implied is true for every row RT in T, then ‘‘R T’’ is true. b) If the implied is false for at least one row RT in T, then ‘‘R T’’ is false. c) If the implied is true for at least one row RT in T, then ‘‘R T’’ is true. d) If T is empty or if the implied is false for every row RT in T, then ‘‘R T’’ is false. e) If ‘‘R 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 General Rules 1a we have a) If either XV or YV is the null value, then X 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 General Rule 1. 1) The is applied to each row of T. The result of the is a table of those rows of T for which the result of the is true. It looks like this should be changed to match the other database engines for improved standard compliance. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug with NULL in NOT IN
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
Re: [sqlite] bug with NULL in NOT IN
The lesson is very clear from the evidence. With SQL NULL is ambiguous and subject to intepretation so good design requires that you completely avoid it. Then you sidestep intractable implementation interptrations. A project management technique dating back further than I can remember was to define elments of languages and tools which were problem ridden and forbid their usage. Quality problems were slashed. D. Richard Hipp wrote: > On Jun 25, 2008, at 12:48 PM, Wilson, Ron P wrote: > > >>It seems to me that using NULL ... could >>create a lot of confusion in queries. > > > Yes, yes. SQL-NULL excels at creating confusion! > > 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
Re: [sqlite] bug with NULL in NOT IN
On Jun 25, 2008, at 12:48 PM, Wilson, Ron P wrote: > It seems to me that using NULL ... could > create a lot of confusion in queries. Yes, yes. SQL-NULL excels at creating confusion! D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug with NULL in NOT IN
Ah. Thanks for the clarification. It seems to me that using NULL as 'anything' or 'unknown' - it becomes a wildcard of sorts and could create a lot of confusion in queries. note to self : use NULL with extreme caution. 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 12:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug with NULL in NOT IN On Jun 25, 2008, at 12:12 PM, Wilson, Ron P wrote: > Why should the second query return zero rows? Clearly ids 1 and 2 > don't > exist in b. The meaning of "NULL" in SQL is overloaded. In some contexts NULL means "anything" or "unknown". In other contexts it means "nothing". If we assume NULL means "nothing" then your statement above is correct. But if we assume NULL means "anything" or "unknown" then we don't know if the right-hand side (RHS) of the NOT IN contains a 1 or 2 because it contains a NULL which is a placeholder for an unknown value which might be a 1 or a 2 - we just don't know. 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". 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
Re: [sqlite] bug with NULL in NOT IN
On Jun 25, 2008, at 12:12 PM, Wilson, Ron P wrote: > Why should the second query return zero rows? Clearly ids 1 and 2 > don't > exist in b. The meaning of "NULL" in SQL is overloaded. In some contexts NULL means "anything" or "unknown". In other contexts it means "nothing". If we assume NULL means "nothing" then your statement above is correct. But if we assume NULL means "anything" or "unknown" then we don't know if the right-hand side (RHS) of the NOT IN contains a 1 or 2 because it contains a NULL which is a placeholder for an unknown value which might be a 1 or a 2 - we just don't know. 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". D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
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] bug with NULL in NOT IN
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. Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
