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? 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
