Hi, Ronald! You seem to mix terms "affinity", "data type" and "declared type of column" again.
> The default affinity is NONE if left unspecified. Mixed statement which is right and wrong in some sense. If you look closer to 2.1 of cited document you'll see that the default affinity is NUMERIC (item # 5). But if you do not specify type of the column in CREATE TABLE statement then affinity will be NONE (item # 3). > 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? Igor and DRH already answered this so I'm not printing the same. > PS: The documentation in section 2.1 (see url above) at first led me to > believe that the default affinity is NUMERIC (see the 5th bullet). Perhaps > it would be more clear to have the first bullet be "If no datatype string is > provided, then it is assigned affinity NONE." This is exactly what 3rd bullet says. > Also, one might expect that specifying an affinity of NONE would result in an > affinity of NONE, but such a table behaves like table "n" above, i.e. NUMERIC > affinity. So section 2.1 correctly describes what strings are parsed for > affinity determination, though not without some user surprise. You cannot specify affinity NONE - it's not specified anywhere. In your example you're specified "column datatype" NONE. And according to the same rules it will fall into 5th bullet and assign affinity NUMERIC. Pavel On Thu, Sep 3, 2009 at 3:30 PM, Wilson, Ronald<rwils...@harris.com> wrote: > Creating a new thread for this topic... I'm confused by affinity. Here is my > munging of Pavel's examples from another thread: > > --- sql.txt --- > .echo on > 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; > select * from "n" where i=1; > select * from "i" where i=1; > select * from "d" where i='1'; > select * from "n" where i='1'; > select * from "i" where i='1'; > .echo off > > 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 > .echo off > sqlite> > > Based on the documentation (http://www.sqlite.org/datatype3.html) column > affinity does not limit the types of data that can be stored, it merely > prefers types based on documented coercion rules. > > 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? > > RW > > PS: The documentation in section 2.1 (see url above) at first led me to > believe that the default affinity is NUMERIC (see the 5th bullet). Perhaps > it would be more clear to have the first bullet be "If no datatype string is > provided, then it is assigned affinity NONE." Also, one might expect that > specifying an affinity of NONE would result in an affinity of NONE, but such > a table behaves like table "n" above, i.e. NUMERIC affinity. So section 2.1 > correctly describes what strings are parsed for affinity determination, > though not without some user surprise. > > SQLite version 3.6.10 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table none (i NONE); > sqlite> insert into "none" values(1); > sqlite> select * from "none" where i='1'; > 1 > sqlite> > > Ron Wilson, Engineering Project Lead > (o) 434.455.6453, (m) 434.851.1612, www.harris.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
