Hi, I'd like to report a possible bug in SQLite that causes a query to give wrong results when an index exists on one of the columns. Without the index the results are correct. I have honed down the data to the smallest possible set that still shows the bad behavior.
The behavior was observed with SQLite version 3.7.3, on Mac OS X 10.6 and on Linux (2.6.16). It also happens with version 3.7.4rc2 (40756fe1f1). With SQLite 3.6.14.1 it does work correctly. The query is as follows: SELECT entry_type AS entry_type_int, entry_types.name as entry_type_name, entry_id FROM timeline JOIN entry_types ON entry_type_int = entry_types.id WHERE (entry_type_name = 'cli_command' AND entry_id IN (SELECT command_id FROM object_changes WHERE obj_context = 'exported_pools')) OR (entry_type_name = 'object_change' AND entry_id IN (SELECT change_id FROM object_changes WHERE obj_context = 'exported_pools')) The database is: CREATE TABLE entry_types ( id integer primary key, name text ); INSERT INTO "entry_types" VALUES(100,'cli_command'); INSERT INTO "entry_types" VALUES(300,'object_change'); CREATE TABLE object_changes ( change_id integer primary key, system_id int, obj_id int, obj_context text, change_type int, command_id int ); INSERT INTO "object_changes" VALUES(1551,1,114608,'exported_pools',1,2114); INSERT INTO "object_changes" VALUES(2048,1,114608,'exported_pools',2,2319); CREATE TABLE timeline ( rowid integer primary key, timestamp text, system_id int, entry_type int, entry_id int ); INSERT INTO "timeline" VALUES(6735,'2010-11-21 17:08:27.000',1,300,2048); INSERT INTO "timeline" VALUES(6825,'2010-11-21 17:09:21.000',1,300,2114); CREATE INDEX timeline_entry_id_idx on timeline(entry_id); The query should return one row, which it does when deleting the index. While the index exists, no rows are returned. Regards, -- Gavrie _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
