On Apr 17, 2008, at 12:04 PM, Eric Minbiole wrote:
> I have been using SQLite for about a year now, and have been extremely
> pleased. While testing 3.5.8, I noticed that a SELECT with LEFT OUTER
> JOIN seemed to stop using an index, resorting to a (slow) full table
> scan. A simple (contrived) example follows:
>
> CREATE TABLE pets (
> pet_id INTEGER PRIMARY KEY,
> owner_id INTEGER,
> name TEXT );
>
> CREATE TABLE owners (
> owner_id INTEGER PRIMARY KEY,
> name TEXT );
>
> INSERT INTO owners (owner_id, name) VALUES (1, 'Steve');
> INSERT INTO pets (owner_id, name) VALUES (1, 'Fluffy');
>
> EXPLAIN QUERY PLAN
> SELECT pets.name, owners.name
> FROM pets
> LEFT OUTER JOIN owners
> ON (pets.owner_id = owners.owner_id);
Your work-around until I fix this is to say
owners.owner_id = pets.owner_id
instead if what you have. In other words, put the
table on the left side of the join before the equals
sign instead of after it.
It shouldn't make any difference. SQLite should
generate exactly the same code regardless of
whether you say A=B or B=A. Clearly something
is busted. It will be fixed soon.
D. Richard Hipp
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
