The expression "x NOT IN (something-that-contains-NULL)" is always false. I suggest you add an additional term to the WHERE clause of the subquery: "... AND md5 NOT NULL".
On Fri, Feb 1, 2013 at 6:20 AM, Paul Sanderson <sandersonforens...@gmail.com > wrote: > I have a query > > SELECT * FROM rtable WHERE md5 NOT IN (SELECT md5 FROM rtable WHERE vsc = > 0) > > I have a test data set > > sqlite> select * from rtable; > $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 > $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 > $RmMetadata|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 > $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5 > $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62 > $RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62 > $RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62 > $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 > $RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 > > When I execute the above query on my test dataset all works OK. These rows > are a subset (some of the columns and some of the rows) of a real dataset. > > However I also have a real dataset where the query returns no rows (and it > should). The real dataset has indexes on all of the relevant columns. The > only differences I can think of with the real dataset are that there are > rows where MD5 is null, and that I have added a custom collation > (SYSTEMNOCASE) for some text columns (although MD5 is a text column the > data in it will only ever by capitals so the collation is not used on this > column). > > Any ideas what the problem could be? > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
