Yeah it seems confusing but once you get your head around it you're ok. The documentation is all there, and I'm sure there are a million threads on the archive about the same thing. If I had the time I'd write an 'affinity for dummies' section for the documentation. In the end, I think a lot of us have to go through the same bewilderment before the light goes on, no matter how milked down the documentation gets.
RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com !!! CHANGE OF EMAIL ADDRESS EFFECTIVE JUNE 2009 !!! Please update your contact list to reflect my new Harris email address (ronald.wil...@harris.com) as soon as possible. HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of D. Richard Hipp > Sent: Thursday, September 03, 2009 4:04 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] confused about column affinity > > > > On Sep 3, 2009, at 3:30 PM, Wilson, Ronald wrote: > > > > SQLite version 3.6.10 > > Enter ".help" for instructions > > Enter SQL statements terminated with a ";" > > sqlite> .read sql.txt > > create table "d" (i); > > create table "n" (i numeric); > > create table "i" (i integer); > > insert into "d" values (1); > > insert into "n" values (1); > > insert into "i" values (1); > > select * from "d" where i=1; > > 1 > > select * from "n" where i=1; > > 1 > > select * from "i" where i=1; > > 1 > > select * from "d" where i='1'; > > select * from "n" where i='1'; > > 1 > > select * from "i" where i='1'; > > 1 > > > > The default affinity is NONE if left unspecified. INTEGER affinity > > behaves like NUMERIC affinity except that it will store a REAL value > > as an INTEGER if there is no fractional component and it fits into > > an INTEGER container. > > > > Therefore table "d" should have the default affinity NONE and not > > coerce data from one storage class to another, so the first insert > > should put an INTEGER in table "d". The second insert should put an > > INTEGER in table "n", and the third insert should also put an > > INTEGER in table "i", both based on coercion rules. > > > > So I don't understand why *any* of the last 3 selects should return > > a value at all. If there is some type conversion going on in the > > comparisons, why did the fourth select return no results? > > In the statement SELECT * FROM n WHERE i='1', in the expression i='1', > the "i" has integer affinity. Hence the '1' on the other side of the > = is coerced into a 1. See section 3 "Comparison Expressions" of the > same document: http://www.sqlite.org/datatype3.html > > This all seems really complicated. But we did it that way because it > causes SQLite to mimic the behavior of other statically typed database > engines (ex: MySQL, PostgreSQL, Oracle, etc.) and hence maximizes > compatibility. > > > D. Richard Hipp > d...@hwaci.com > > > > _______________________________________________ > 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
