I haven't tried this myself.. but there is another possible solution. The
sqlite documentation describes that there are 3 affinity modes. One of these
modes is "Strict affinity mode". It appears to me that an "int" pushed into
a column of type "real" would be coerced into a real and would behave as the
author of the original post intended.
ofcourse as the title of this post suggests select 5/2 would still return 2.
But, a table with a 5 and a 2 stored in them, that was declared as real,
would yield the intended result of 2.5.
here is the reference i referred to: http://www.sqlite.org/datatype3.html
(bullet point #6)
I don't know if this is a database setting or a compile time setting or
what...(i would hope database setting) it might be worth trying out.
--Preston
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thu 10/6/2005 10:14 AM
> Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2
> <[EMAIL PROTECTED]> wrote:
> > Richard, I like to ask you, just to make sure; has the `order by`
> > "bug"/issue, as result from automatic float->int conversion, been
> > considered by you and if yes, do you plan any changes?
> >
>
>
> You have a simple workaround: Just add 0.0 to anything
> that you definitely want to be floating point.
>
> I will continue to trying to figure out a reasonable method
> of addressing your concerns.
In other databases the behavior in sqlite is the expected behavior. E.g. IBM
db2udb, Ingres, Microsoft SQL Server. Oracle is different because of the
wierd NUMBER datatype which is used to store both ints and decimal - I don't
know about any others.
In most databases the datatypes of the operands dictate the result type;
which is the sqlite behavior.
Chris
