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

Reply via email to