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. > > So I don't understand why *any* of the last 3 selects should return a > value at all.
The answer is in the document you cite, section 3 "Comparison Expressions": <quote> SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the nominal affinity assigned to the expressions on either side of the binary operator. Affinities are assigned to expressions in the following cases: - An expression that is a simple reference to a column value has the same affinity as the column it refers to. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions. - An expression of the form "CAST(<expr> TO <type>)" is assigned an affinity as if it were a reference to a column declared with type <type> Conversions are applied before the comparison as described below. In the following bullet points, the two operands are refered to as expression A and expression B. Expressions A and B may appear as either the left or right operands - the following statements are true when considering both "A <op>B" and "B <op>A". - 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. - When two expressions are compared, if expression A has been assigned an affinity and expression B has not, then the affinity of expression A is applied to the value of expression B before the comparison takes place. - Otherwise, if neither of the above applies, no conversions occur. The results are compared as is. If a string is compared to a number, the number will always be less than the string. </quote> Expression '1' has no affinity. d.i has no affinity either, so in d.i='1' no conversion takes place; since d.i has a value of type integer, it is never equal to a string. On the other hand, n.i and i.i have NUMERIC and INTEGER affinity, correspondingly. So '1' is coerced to NUMERIC value of 1, and the equality test is satisfied. > If there is some type conversion going on in the > comparisons, why did the fourth select return no results? No affinity, no conversion. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
