> Yes, I know how it works. But that seems to contradict the > documentation. The first field of the record inserted should have a type > of numeric, as types are associated with the data not with the column > declaration. So the phrase Where n = '1' should fall into the first > bullet case
Rod, type != affinity. While in my example value in the field n in the inserted record has type numeric it has no affinity (because column has no type declared) thus it doesn't fall into first bullet. Affinity is associated with columns, not with the data inserted. Thus in my earlier example (when column n had type integer) even though column n had integer affinity expression +n has no affinity (though integer type) and thus comparison +n = '1' is always false because no conversions applied. > (Reading "conversion is applied" for "affinity is applied".) These are not equivalent actions. When we are talking about conversion of text into number "affinity is applied" means that conversion is made if possible otherwise the value keeps its text type, but "conversion is applied" means that conversion is made in any case i.e. if text doesn't contain number it will be converted to 0. Pavel On Thu, Sep 3, 2009 at 9:33 AM, Rod Dav4is<dav...@yahoo.com> wrote: > > Pavel Ivanov wrote: >>> Perhaps the fact that my column definitions declared no >>> typing has an effect here? >>> >> >> Yes, that means that your columns have no affinity, all data stored in >> it as you give and no conversions done during insertions and >> comparisons: >> >> sqlite> create table t (n, t); >> sqlite> insert into t values (1, '3'); >> sqlite> select * from t where n = 1; >> 1|3 >> sqlite> select * from t where n = '1'; >> sqlite> >> >> Everything is well described at http://www.sqlite.org/datatype3.html >> and link there goes even from http://www.sqlite.org/version3.html >> which I presume you've already seen. >> >> >> Pavel >> >> > Yes, I know how it works. But that seems to contradict the > documentation. The first field of the record inserted should have a type > of numeric, as types are associated with the data not with the column > declaration. So the phrase Where n = '1' should fall into the first > bullet case, i.e. > > * When two expressions are compared, if expression A has INTEGER or > REAL or NUMERIC affinity and expression B does not, then NUMERIC > affinity is applied to the value of expression B before the > comparison takes place. > > (Reading "conversion is applied" for "affinity is applied".) > -R. > > -- > Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA > Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/ > 538 ancestral & collateral families, mostly 17°-19° century > New England & European roots. Total population: 136,000+ > Annex: http://www.gencircles.com/users/dav4is/ > email: dav...@yahoo.com > A Democrat, a Republican and a giraffe walk into a bar. The > bartender looks up from his want ads and says, "What is this, a joke?" > -unknown > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
