> AH, GOT IT. values do NOT have affinity. so I would > either need to cast the expression OR the value. > > is this correct ?
Yes, that's correct, you have to cast either left or right part to assign affinity to it and to force conversion of another part. Pavel On Tue, Dec 15, 2009 at 10:53 AM, Cariotoglou Mike <m...@singular.gr> wrote: > Quoting from the documentation on Type Affinity: > > " > 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. > " > > since, in the examples posted, (select count(*) from ...) should be an > expression, and since it is compared to a value, it should take on the > affinity > of the value, no ? AH, GOT IT. values do NOT have affinity. so I would > either need to cast the expression OR the value. > > is this correct ? > > >> -----Original Message----- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov >> Sent: Tuesday, December 15, 2009 5:38 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Bug when uUsing Parameters with views >> >> Apparently result of count() and probably all other aggregate >> functions (as well as result of any function at all) has no affinity. >> So when you compare it to anything having no affinity too you >> have no type transformation during comparison. And thus >> values have to be exactly the same including their types... >> >> Everything is explainable and probably is impossible to fix >> unless core aggregate functions become specially recognizable >> part of the core or SQLite team figures out how to make >> functions return their results with some affinity assigned... >> >> Pavel >> >> On Tue, Dec 15, 2009 at 10:25 AM, Simon Davies >> <simon.james.dav...@googlemail.com> wrote: >> > 2009/12/15 D. Richard Hipp <d...@hwaci.com>: >> >> >> >>> >> >> >> >> Because string '2' is not the same thing as integer 2. >> >> >> >> sqlite3> select 2='2'; >> >> 0 >> >> sqlite3> >> >> >> > >> > Why >> > sqlite> select cast( 2 as integer ) = '2'; >> > 1 >> > >> >> >> >> D. Richard Hipp >> >> d...@hwaci.com >> >> >> > >> > Simon >> > _______________________________________________ >> > 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 >> > _______________________________________________ > 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
