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

Reply via email to