Re: [sqlite] confused about column affinity
> > The documentation refers to the column affinities TEXT, NUMERIC, > > INTEGER, REAL, and NONE, but NONE is not identified as a column > > affinity by the SQL parser, which treats it as NUMERIC. > > Column affinity is not part of the syntax - declared type is. The > affinity is inferred from the type. Column affinity is a semantic, > rather than a syntactic, concept, so it is meaningless to state whether > or not a parser identifies one. Thanks for the reply. I really appreciate your fast and frequent feedback, though sometimes your obsession for correctness gets in the way of a simple answer. I think it's fine to gently nudge people to ask well-formed questions, but my question was quite clear despite the semantic/syntactic mismatch. Nevertheless thank you for making me a better person. Seriously. RW 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
Re: [sqlite] confused about column affinity
Wilson, Ronald wrote: >>> Though, now I'm curious what your opinion on this is: >> >> I don't have an opinion on this. Should I? Is there a question in >> there somewhere? >> > The documentation refers to the column affinities TEXT, NUMERIC, > INTEGER, REAL, and NONE, but NONE is not identified as a column > affinity by the SQL parser, which treats it as NUMERIC. Column affinity is not part of the syntax - declared type is. The affinity is inferred from the type. Column affinity is a semantic, rather than a syntactic, concept, so it is meaningless to state whether or not a parser identifies one. It is true that a declared type of NONE is not treated in any way specially by SQLite, and results in a column with NUMERIC affinity - just like a declared type of, say, BLAH would. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
> > Though, now I'm curious what your opinion on this is: > > I don't have an opinion on this. Should I? Is there a question in there > somewhere? > > Igor Tandetnik The documentation refers to the column affinities TEXT, NUMERIC, INTEGER, REAL, and NONE, but NONE is not identified as a column affinity by the SQL parser, which treats it as NUMERIC. I thought that might lead to some user surprise. That is my opinion. Roughly, my question was, "Do you have an opinion on that?" You answered my question adequately. Thank you. RW 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
Re: [sqlite] confused about column affinity
Wilson, Ronald wrote: > Sigh, I thought I had read all the applicable sections. Thanks Igor. > > Though, now I'm curious what your opinion on this is: > >> 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> I don't have an opinion on this. Should I? Is there a question in there somewhere? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
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
Re: [sqlite] confused about column affinity
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 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
Re: [sqlite] confused about column affinity
Sigh, I thought I had read all the applicable sections. Thanks Igor. Though, now I'm curious what your opinion on this is: > 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> RW 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
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
Re: [sqlite] confused about column affinity
Wilson, Ronald 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": 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( TO )" is assigned an affinity as if it were a reference to a column declared with 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 B" and "B 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. 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
