Re: [sqlite] strange rounding problem
Donald, Will Leshner <[EMAIL PROTECTED]> 14/10/2004 04:20 AM Please respond to sqlite-users To: [EMAIL PROTECTED] cc: Subject: Re: [sqlite] strange rounding problem >The point is that the number is in the database as '358', but by the time we get it back from a query it has become '357.999'. We aren't doing any floating point with the number. It is (apparently) happening for us in the SQLite engine itself. But we depend on the number being '358' when we query for it and because it isn't, on some machines, our app fails. Can you come up with a minimal test case that exhibits this behaviour? Ideally, it would be a series of SQL statements written into the sqlite shell program that results in something visibly wrong. The second best case would be a short (10-20 line) C program that uses the sqlite API only to demonstrate the problem. Perhaps other users of this list could test on their own machines and come up with more information. Benjamin.
Re: [sqlite] strange rounding problem
[EMAIL PROTECTED] wrote: Can you come up with a minimal test case that exhibits this behaviour? Ideally, it would be a series of SQL statements written into the sqlite shell program that results in something visibly wrong. The second best case would be a short (10-20 line) C program that uses the sqlite API only to demonstrate the problem. Perhaps other users of this list could test on their own machines and come up with more information. Thanks. I'll certainly try. But it is very elusive. The exact same query works fine on some machines and doesn't on others. And that's with the exact same binary executable. We even copied the executable from one machine that works to another machine that doesn't. For now we have moved back to an older version of SQLite and that seems to have fixed the problem.
Re: [sqlite] strange rounding problem
Griggs, Donald wrote: If I'm missing the (decimal) point, here, forgive me. The point is that the number is in the database as '358', but by the time we get it back from a query it has become '357.999'. We aren't doing any floating point with the number. It is (apparently) happening for us in the SQLite engine itself. But we depend on the number being '358' when we query for it and because it isn't, on some machines, our app fails.
RE: [sqlite] strange rounding problem
Isn't this a possibility with most any computer language when one uses
binary floating point numbers? Since a real number such as 35.8 can't be
EXACTLY represented as a binary fraction, there can always be a bit of
floating point fluff added or subtracted, right?
Its analogous to the fact that, for example, 1/3 can't be represented
exactly in base 10 -- 0.666 is too low, and 0.667 is too high.
If so, then suppose you want to display X digits to the right of the decimal
point. If you prepare a number for display by adding a 5 in the X+1 decimal
position (e.g. to display 3 decimal digits, you first add 0.0005) then
truncating to X digits (e.g. 35.800) you may have your problem solved. (Of
course, you want to do this only to values you don't intend to calculate
further upon.)
If I'm missing the (decimal) point, here, forgive me.
Donald Griggs
Desk: 803-735-7834
Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.
-Original Message-
From: Will Leshner [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 1:44 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] strange rounding problem
I've tracked my rounding problem down to this line in vxprintf():
while( realvalue>=10.0 && exp<=350 ){ realvalue *= .1; exp++; }
Apparently, on some hardware, sometimes, multiplying a number like '358' by
.1 results in a number like '35.79' or thereabouts. Now what's really
strange is that it doesn't happen every time. If I call vxprintf by hand
with '358', I get '3.580' the first time round that loop. But there is a
code path down which I go that causes that value to become strange the first
time round the loop. On other hardware the problem never happens, no matter
what code path gets to that line.
This is with SQLite 2.8.15 built with VC 6. If we go back to our old version
of 2.8.6, then we don't have this kind of problem.
I don't suppose anybody has ever run into something like this before?
Re: [sqlite] strange rounding problem
Will Leshner wrote: I've tracked my rounding problem down to this line in vxprintf(): I'm sorry to say that is a false alarm. It turns out that we were being fooled by the debugger in Dev Studio. What is the case, however, is that a value is coming out of the database and at some point duing the processing of a query, a very small amount of floating-point error is being introduced. But only on some hardware configurations. The reason I keep talking about this on this list is because it still seems like this just might be a very subtle bug in SQLite. If we ever figure it out I'll report back.
Re: [sqlite] strange rounding problem
I've tracked my rounding problem down to this line in vxprintf():
while( realvalue>=10.0 && exp<=350 ){ realvalue *= .1; exp++; }
Apparently, on some hardware, sometimes, multiplying a number like '358' by .1 results
in a number like '35.79' or thereabouts. Now what's really strange is that it
doesn't happen every time. If I call vxprintf by hand with '358', I get '3.580'
the first time round that loop. But there is a code path down which I go that causes
that value to become strange the first time round the loop. On other hardware the
problem never happens, no matter what code path gets to that line.
This is with SQLite 2.8.15 built with VC 6. If we go back to our old version of 2.8.6,
then we don't have this kind of problem.
I don't suppose anybody has ever run into something like this before?
Re: [sqlite] strange rounding problem
On Oct 13, 2004, at 3:00 AM, Bogusław Brandys wrote: Not a solution, but ... get any sqlite manager like sqliteadmin or sqliteexplorer and check what is really stored in database table. Thanks. I did that and what's stored there is exactly what I expect to be store there. In other words, the column contains integers like "234". For some reason, though, they are getting turned into real numbers, like 233. before I get them back in my query.
Re: [sqlite] strange rounding problem
Hi, Will Leshner wrote: In version SQLite 2.8.15 I'm having a situation with a query that I don't quite understand. This only happens on some machines. Basically I have a table with an Integer field that contains integers. And I have a query that is returning those integers as real numbers. So, for example, if I have 234 in the field, I'm getting back '233.999'. As I step through the code, I'm finding myself in Stringify in the OP_Callback case of sqliteVdbeExec. Inside of Stringify, the column's flags are set to MEM_Real, which causes the value to be converted from a real number to a string using sqlite_snprintf(). On many machines the number comes in as, say, '234.00' and it gets turned into '234'. But on some machines it comes in as '233.99' and goes out as '233.99', which messes things up down the line. Has anybody run into anything like this before? Is there some setting I may be missing. We recently upgraded from 2.8.6 to 2.8.15 and it is entirely possible we changed a setting somewhere during the upgrade. Thanks. Not a solution, but ... get any sqlite manager like sqliteadmin or sqliteexplorer and check what is really stored in database table. Regards Boguslaw Brandys
