On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote:
> According to that, it rounds to the nearest even number.  Shouldn't 9.95
> go to 10 then, and 9.85 go to 9.8?
> 
> After additional testing with SQLite 3.2.2, I have the following
> results:
> 
> Round(9.95,1)  -> 9.9    *Rounded Down*
> Round(9.85,1)  -> 9.8    *Rounded Down*
> Round(9.5,0)   -> 10     *Rounded Up*
> Round(9.995,2) -> 9.99   *Rounded Down*
> 
> I really see no pattern or sense to the results.
> 

The reason you see no pattern is because you are thinking in
decimal whereas SQLite thinks in binary.

The number 9.95 does not a have finite representation in
binary.  The closest you can get with a 64-bit IEEE float
is:

   9.949999999999999289457264239899814128875732421875

So when you type "9.95" into an SQL statement, SQLite really
inserts the number shown above, not 9.95.  And the number 
shown above rounds down.

9.5 does have an exact representation in binary so it rounds
as you would expect.  But neither 9.85 nor 9.995 do - the
binary values chosen to represent them are both just a little
less than their decimal values.  Hence they both round
down.

So I'm not overly worried when I see round(9.95,1) come out
with 9.9.  But I am concerned about the people who are seeing
results like ":.0".  I wish I could reproduce that problem.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Reply via email to