On Sun, Dec 5, 2010 at 7:25 AM, Gavrie Philipson <gav...@gmail.com> wrote:
> Hi, > > .... > 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')) > > I also got no rows with the above query, but got: 300 object_change 2048 after changing the query to: SELECT entry_type AS entry_type_int, entry_types.name as entry_type_name, entry_id FROM timeline JOIN entry_types ON entry_type = entry_types.id WHERE (entry_types.name = 'cli_command' AND entry_type_name 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 changes were to use the real column names and not the aliases from the SELECT clause. I believe the problem arises (and the book "Using SQLite" explains it a lot better than I can) because the FROM and WHERE clauses are executed before the SELECT clause. I can not explain why the absence or presence if an INDEX changes the result. It is a known feature of SQLite that it does not contain huge amounts of error checking, and the results when you do things you shouldn't can be surprising. Hope this helps, Gerry _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users