At table creation time, when column types are not declared explicitly, or are produced by an expression, column affinity defaults to NONE, with the result that indexes added afterwards often go unused in joins because of a column affinity mismatch.
Adding casts around the expressions is an effective way to enforce column affinities, at the expense of redundant code. Column types that could be declared in just one place, or simply inferred, have to be repeatedly re-declared via casts. I assume that this is a result of sqlite's manifest typing. Would you consider changing the column affinity determination rules such that expressions could, at least in easy-to-deduce cases, automatically assign the appropriate column affinity? My colleague Ivan, who diagnosed the issue, put the following repro together to illustrate the problem. He uses the syntax "a JOIN b ON b.x IN (a.x)" and compares its query plan and performance to that of using the standard join syntax. He also requests the query planner to specifically use the index within the context of the normal join syntax, which the query planner rejects. $ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> CREATE TEMP TABLE z AS ...> WITH RECURSIVE cnt(x) AS ( ...> VALUES(1) UNION ALL SELECT x+1 FROM cnt ...> WHERE x < 1e6 ...> ) SELECT x FROM cnt; sqlite> sqlite> CREATE TABLE a(x INTEGER NOT NULL PRIMARY KEY); sqlite> INSERT INTO a SELECT x FROM temp.z; sqlite> sqlite> CREATE TABLE b AS SELECT x FROM temp.z; sqlite> CREATE UNIQUE INDEX b_idx_1 ON b(x); sqlite> sqlite> ANALYZE; sqlite> sqlite> PRAGMA table_info(a); cid name type notnull dflt_value pk ---------- ---------- ---------- ---------- ---------- ---------- 0 x INTEGER 1 1 sqlite> PRAGMA table_info(b); cid name type notnull dflt_value pk ---------- ---------- ---------- ---------- ---------- ---------- 0 x 0 0 sqlite> PRAGMA index_info(b_idx_1); seqno cid name ---------- ---------- ---------- 0 0 x sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123; selectid order from detail ---------- ---------- ---------- -------------------------------------------------- 0 0 0 SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) 0 1 1 SCAN TABLE b sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM a JOIN b INDEXED BY b_idx_1 ON a.x = b.x AND a.x = 123; Error: no query solution sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123; selectid order from detail ---------- ---------- ---------- -------------------------------------------------- 0 0 0 SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) 0 1 1 SEARCH TABLE b USING COVERING INDEX b_idx_1 (x=?) 0 0 0 EXECUTE LIST SUBQUERY 1 sqlite> .timer on sqlite> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123; x x ---------- ---------- 123 123 Run Time: real 0.157 user 0.155976 sys 0.000000 sqlite> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123; x x ---------- ---------- 123 123 Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
