On Sun, Dec 5, 2010 at 7:25 AM, Gavrie Philipson <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
