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
