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).
So how can I rewrite the query to compensate?
sqlite> INSERT INTO ccc ('id') VALUES (3);
sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (3, 3, 2);
sqlite>
sqlite> SELECT aaa.id, bbb.id, ccc.id
...> FROM aaa
...> LEFT OUTER JOIN bbb
...> ON (aaa.o_id=bbb.id)
...> LEFT OUTER JOIN ccc
...> ON (aaa.o_id=ccc.id)
...> WHERE
...> (aaa.o_type=1 AND bbb.id IS NOT NULL) OR
...> (aaa.o_type=2 AND ccc.id IS NOT NULL);
1|3|3
2||4
3|3|3
sqlite>
This is the solution I saw elsewhere on this list, and it works, but I
want exactly one of the joined tables to be non-null.
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 OR 0))
...> LEFT OUTER JOIN ccc
...> ON (aaa.o_id=ccc.id AND (aaa.o_type=2 OR 0));
1|3|
2||4
3||3
sqlite>
This is what I want, but the extra 0 makes me sad.
So what am I doing wrong?
Daniel Ring
------
CREATE TABLE "aaa" (
'id' integer NOT NULL PRIMARY KEY,
'o_id' integer UNSIGNED NOT NULL,
'o_type' integer NOT NULL
);
CREATE TABLE 'bbb' (
'id' integer NOT NULL PRIMARY KEY
);
CREATE TABLE 'ccc' (
'id' integer NOT NULL PRIMARY KEY
);
INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (1, 3, 1);
INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (2, 4, 2);
INSERT INTO bbb ('id') VALUES (3);
INSERT INTO ccc ('id') VALUES (4);
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);
CREATE INDEX 'aaa_o_type' ON 'aaa' ('o_type');
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);
INSERT INTO ccc ('id') VALUES (3);
INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (3, 3, 2);
SELECT aaa.id, bbb.id, ccc.id
FROM aaa
LEFT OUTER JOIN bbb
ON (aaa.o_id=bbb.id AND aaa.o_type=1*(aaa.id/aaa.id))
LEFT OUTER JOIN ccc
ON (aaa.o_id=ccc.id AND aaa.o_type=2*(aaa.id/aaa.id));
SELECT aaa.id, bbb.id, ccc.id
FROM aaa
LEFT OUTER JOIN bbb
ON (aaa.o_id=bbb.id AND (aaa.o_type=1 OR 0))
LEFT OUTER JOIN ccc
ON (aaa.o_id=ccc.id AND (aaa.o_type=2 OR 0));
------
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
