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
