On Fri, Dec 24, 2010 at 5:19 PM, Andrew Z <ahz...@gmail.com> wrote: > My application accesses an SQLite database created by another > application, and I noticed on my system (SQLite 3.7.2, Fedora 14) if > the subquery returns no values, the delete clause does nothing. Is > this a bug in SQLite?
No. SQLite is giving the correct answer. There are NULLs in the right-hand side of your NOT IN clause. Try this instead: delete from moz_favicons where id not in (select favicon_id from moz_places where favicon_id is not null); > The expected result below is that all rows from > moz_favicons are deleted. > > > CREATE TABLE moz_favicons ( id INTEGER PRIMARY KEY, url LONGVARCHAR > UNIQUE, data BLOB, mime_type VARCHAR(32), expiration LONG); > INSERT INTO "moz_favicons" > VALUES(1,'http://fedoraproject.org/static/images/favicon.ico > ','2','image/png',1293223842352252); > INSERT INTO "moz_favicons" > VALUES(2,'http://slashdot.org/favicon.ico > ','82','image/png',1293221318471851); > CREATE TABLE moz_places ( id INTEGER PRIMARY KEY, url LONGVARCHAR, > title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT > 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT > NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, > last_visit_date INTEGER ); > INSERT INTO "moz_places" > > VALUES(1,'place:redirectsMode=2&sort=8&maxResults=10','redirectsMode=2&sort=8&maxResults=10',NULL,0,1,0,NULL,0,NULL); > INSERT INTO "moz_places" > > VALUES(2,'place:folder=BOOKMARKS_MENU&folder=UNFILED_BOOKMARKS&folder=TOOLBAR&queryType=1&sort=12&excludeItemIfParentHasAnnotation=livemark%2FfeedURI&maxResults=10&excludeQueries=1','folder=BOOKMARKS_MENU&folder=UNFILED_BOOKMARKS&folder=TOOLBAR&queryType=1&sort=12&excludeItemIfParentHasAnnotation=livemark%2FfeedURI&maxResults=10&excludeQueries=1',NULL,0,1,0,NULL,0,NULL); > INSERT INTO "moz_places" > > VALUES(3,'place:type=6&sort=14&maxResults=10','type=6&sort=14&maxResults=10',NULL,0,1,0,NULL,0,NULL); > INSERT INTO "moz_places" > VALUES(4,'http://docs.fedoraproject.org/release-notes/ > ','/release-notes/','gro.tcejorparodef.scod.',0,0,0,NULL,140,NULL); > > > select "select distinct favicon_id from moz_places"; > select distinct favicon_id from moz_places; > > select "deleting: standard method..."; > delete from moz_favicons where id not in (select distinct favicon_id > from moz_places); -- here is the alleged bug? > > select "remaining URLs"; > select url from moz_favicons; > > select "deleting: dummy, non-null value..."; > delete from moz_favicons where id not in (0); > > select "remaining URLs"; > select url from moz_favicons; > _______________________________________________ > 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
