According to the documentation, when creating a table "AS SELECT ...", the "affinity of comparison operands" rules are applied; what I am suggesting is that these rules be extended when used with "CREATE TABLE ... AS SELECT ..."
http://www.sqlite.org/datatype3.html#expraff 3.2 Affinity Of Comparison Operands SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the affinity of the operands. Operand affinity is determined by the following rules: - The affinity of the right-hand operand of an IN or NOT IN operator is NONE if the operand is a list and is the same as the affinity of the result set expression if the operand is a SELECT. - An expression that is a simple reference to a column value has the same affinity as the column. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions for the purpose of determining affinity. - An expression of the form "CAST(*expr* AS *type*)" has an affinity that is the same as a column with a declared type of "*type*". - Otherwise, an expression has NONE affinity. On Tue, Jul 8, 2014 at 7:31 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 8 Jul 2014, at 11:11pm, Hinrichsen, John <jhinrich...@c10p.com> wrote: > > > 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.) > > > > [snip] > > > > 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. > > Okay. So the problem is with sub-SELECT which produces values without > affinities. What you want is for MIN() and MAX() to have the same affinity > as the value they choose, and for SUM() to have an affinity of REAL. > Thanks for the explanation. > > So now I'm interested to know whether functions created with > sqlite3_create_function() return a value with an affinity or just a value. > > 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
