Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
> 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. Good idea: Swapping the terms of the JOIN expression does seem to work. Thank you for this workaround, and for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
D. Richard Hipp wrote: > > Likely this has to do with ticket #3015. > http://www.sqlite.org/cvstrac/tktview?tn=3015 > Yes, I agree. It seems like the fix may have been too broad. The problem with the query in that report was the use of an index on the left table in the join condition. In the OP's query SQLite is failing to use an index on the right table when it should. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
On Apr 17, 2008, at 12:31 PM, Dennis Cote wrote: > Eric Minbiole wrote: >> >> However, I wanted to let others take a look, to see if the >> issue was with my query (quite possible), or with the new version. >> > > This is definitely an issue with the new version. It is doing a nested > table scan instead of using the index for the left join. > Likely this has to do with ticket #3015. http://www.sqlite.org/cvstrac/tktview?tn=3015 D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
Eric Minbiole wrote: > > However, I wanted to let others take a look, to see if the > issue was with my query (quite possible), or with the new version. > This is definitely an issue with the new version. It is doing a nested table scan instead of using the index for the left join. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
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); With 3.5.7, SQLite seems to use the primary key: 0|0|TABLE pets 1|1|TABLE owners USING PRIMARY KEY However, 3.5.8 appears to do a full table scan: 0|0|TABLE pets 1|1|TABLE owners On my actual database, the query time jumped from a couple milliseconds to a few seconds. In the meantime, I can continue using the earlier version(s). However, I wanted to let others take a look, to see if the issue was with my query (quite possible), or with the new version. Thank you, Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
