In most cases it should be possible for SQLite to perform type inference, i.e. deduce the type of an expression, rather than assign the column affinity to None for all columns that are produced by expressions. This applies when creating a table using a SELECT where a column is the result of an expression (such as min, max, or sum) or within a CTE (in the example provided, where the expression can obviously only produce integers.)
Because expressions can be anywhere and everywhere, I have to perform a cast whenever a column is produced by an expression. The cast is not a one-off, do it once and forget about it kind of thing. In SQLite, a cast must wrap every expression if indices are to be used consistently. In my original post I note that the cast does work--but: -- it's redundant (the cast is a possible source of bugs if the column type is later changed) -- it's unintuitive (why can't the database figure out the right column affinity?) -- it's specific to SQLite (other DBs do assign the correct column type automatically) -- the code works anyway without the casts--which can be very misleading--but performs significantly worse, because it may stubbornly refuse to use an index that has been provided If SQLite (optionally?) permitted us to avoid writing these casts, by automatically deducing the correct column affinity, it would correctly make use of indices created, which would benefit everyone. It would also simplify the writing of queries, remove redundant code, and behave like SQL as understood by other DBs. An alternative might be to make SQLite consistently use indices regardless of column affinity. On Tue, Jul 8, 2014 at 1:47 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 8 Jul 2014, at 6:16pm, Hinrichsen, John <jhinrich...@c10p.com> wrote: > > >>> It would be more intuitive: why should aggregate functions like min(), > >>> max(), and sum() return column data stripped of the original column > >>> affinity? > > Can you talk us through the original problem again ? > > Are you talking about the affinity of the column x, or the affinity of the > result of these functions. And how is this a problem ? > > Simon. > _______________________________________________ > 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
