On Tue, Jul 8, 2014 at 11:01 AM, Hinrichsen, John <jhinrich...@c10p.com> wrote:
> Hi, > > Would you consider changing the column affinity determination rules Probably not. There are over a half million apps (literally) in circulation that use the existing rules. Changing the rules would break some fraction of those half-million apps, which would likely be very annoying to the developers. > so that > expressions would, at least in easy-to-deduce cases, automatically assign > the appropriate column affinity? > > Making this change would improve the performance of some queries (see my > original email.) > > It would be more intuitive: why should aggregate functions like min(), > max(), and sum() return column data stripped of the original column > affinity? > > > On Fri, May 23, 2014 at 2:21 PM, Hinrichsen, John <jhinrich...@c10p.com> > wrote: > > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
