Hi Richard, Your concern about breaking existing code makes a lot of sense. Would you consider a PRAGMA or compile-time directive to enable keeping column affinity where possible?
I tested postgres, and found that columns produced by aggregate functions retain the column affinity of the input columns. Most users of sqlite have experience with other sql databases, so it's fair to assume that a lot of sql for sqlite is losing column affinity unintentionally in cases like this. On Tue, Jul 8, 2014 at 11:09 AM, Richard Hipp <d...@sqlite.org> wrote: > 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 > -- 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
