Thanks Richard that worked
On 1 February 2013 11:23, Richard Hipp <d...@sqlite.org> wrote: > 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 > -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
