I thought I understood that column types were effectively a hint to sqlite and didn't really have an effect on the semantics of queries.
But I ran into this case wherein the column types of columns in tables being joined seems to determine whether an index is used or not. Here's my sample code. Note that in the case when the columns are both integer an index is used and when one is integer and one is not specified no index is used. Any thoughts on this? Thanks. -- Mark sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE seq (value integer); CREATE TABLE bar (value integer, unique(value)); CREATE TABLE bar1 (value, unique(value)); COMMIT; sqlite> sqlite> explain query plan SELECT * FROM seq LEFT OUTER JOIN bar1 ON seq.value = bar1.value; 0|0|0|SCAN TABLE seq 0|1|1|SCAN TABLE bar1 sqlite> sqlite> explain query plan SELECT * FROM seq LEFT OUTER JOIN bar ON seq.value = bar.value; 0|0|0|SCAN TABLE seq 0|1|1|SEARCH TABLE bar USING COVERING INDEX sqlite_autoindex_bar_1 (value=?) _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
