Daniel Ring wrote:
> The following behavior when using LEFT OUTER JOIN with an indexed column
> and literals in the ON clause is, I'm pretty sure, wrong. SQLite seems
> to convert the join to an INNER JOIN in some cases.
>
> The capture is from SQLite 3.4.2, but I get the same results with 3.5.7.
> I also copied the raw SQL at the end for your copy-and-pasting pleasure.
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> CREATE TABLE "aaa" (
> ...> 'id' integer NOT NULL PRIMARY KEY,
> ...> 'o_id' integer UNSIGNED NOT NULL,
> ...> 'o_type' integer NOT NULL
> ...> );
> sqlite>
> sqlite> CREATE TABLE 'bbb' (
> ...> 'id' integer NOT NULL PRIMARY KEY
> ...> );
> sqlite>
> sqlite> CREATE TABLE 'ccc' (
> ...> 'id' integer NOT NULL PRIMARY KEY
> ...> );
> sqlite>
> sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (1, 3, 1);
> sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (2, 4, 2);
> sqlite>
> sqlite> INSERT INTO bbb ('id') VALUES (3);
> sqlite> INSERT INTO ccc ('id') VALUES (4);
> sqlite>
> sqlite> SELECT aaa.id, bbb.id, ccc.id
> ...> FROM aaa
> ...> LEFT OUTER JOIN bbb
> ...> ON (aaa.o_id=bbb.id AND aaa.o_type=1)
> ...> LEFT OUTER JOIN ccc
> ...> ON (aaa.o_id=ccc.id AND aaa.o_type=2);
> 1|3|
> 2||4
> sqlite>
>
> This is correct, but add an index and...
>
> sqlite> CREATE INDEX 'aaa_o_type' ON 'aaa' ('o_type');
> sqlite>
> sqlite> SELECT aaa.id, bbb.id, ccc.id
> ...> FROM aaa
> ...> LEFT OUTER JOIN bbb
> ...> ON (aaa.o_id=bbb.id AND aaa.o_type=1)
> ...> LEFT OUTER JOIN ccc
> ...> ON (aaa.o_id=ccc.id AND aaa.o_type=2);
> 1|3|
> sqlite>
>
> I expect the second SELECT to produce the same results as the first
> (presumably faster).
>
This is a bug and you should create a ticket at
http://www.sqlite.org/cvstrac/tktnew so that it gets addressed. Adding
an index should never change the result of a query.
As for a workaround; What about simply dropping the index? In other
words are you sure the correct version without the index is too slow?
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
