What would you expect 9.95 to round to? Here are the simple rounding
rules -
Rule 1- if the remainder beyond the last digit to be reported is less
than 5, drop the last digit. Rounding to one decimal place, the number
5.3467 becomes 5.3.
Rule 2-if the remainder is greater than 5, increase the final digit by
1. The number 5.798 becomes 5.8 if rounding to 1 digit.
Rule 3- To prevent rounding bias, if the remainder is exactly 5, then
round the last digit to the closest even number.Thus the number 3.55
(rounded to 1 digit) would be 3.6 (rounding up) and the number 6.450
would round to 6.4 (rounding down)if rounding to 1 decimal.
Your floating point 9.95 could therefore round to 9.9 or 10.0. You
would expect 9.950 to round to 9.9 and 9.956 to round to 10.0.
When using floating point numbers you must always expect them to never
have a precise value and use inequalities not equalities in processing
them and to be cognisant of orders of accuracy.
mike cariotoglou wrote:
hello to all. I wish to report some quirks I discovered with floating
point and
ROUND() function, while looking into a problem reported by my
development team wrt
to sqlite handling of above function.
first of all, let me state that I understand the issues with inexact
floating point
representations, so let us not go into discussions about which is the
"correct"
interpretation of 9.95. however, a given implementation should at least
be consistent
wrt to this.
my tests have shown that, for the following statement:
select ROUND(9.95,1)
the command-line sqlite3.exe (v 3.3.8) returns 10.0
OTOH, the compiled DLL that can be downloaded from the sqlite site returns
9.9 !
(as a reference, both MS SQL and ORACLE return 10.0)
What gives ? is the result dependent on compilation options, and if so,
which ?
trying to investigate this issue, I compiled the dll locally (3.3.8),
using Microsoft
Visual Studio 2005, and came across a beauty :
the dll compiled with MSVC, using default options more or less, gives
0.0 (yes, zero)
I pulled my hair out over this for some hours, and discovered that :
a. the floating point optimizer in MSVC has a bug, which is triggered
when you use the
optimization setting /fp:precice (which is the default), and gives the
above erroneous
result.
b. you can get the correct behavior by speifying optimization as /fp:strict
Clearly, this is a problem with the microsoft compiler. however, trying
to avoid future
issues, I suggest that somebody which is conversant in C (which is not
me), try to find
the sqlite3 source construct that triggers this bug, and re-writes the
code so that it
is not optimization-sensitive. I tracked the problem down somewhere in
the vxprintf function
in the print.c source file. It is quite difficult to pin the problem
down, because:
the problem goes away when you build in debug mode, because
optimizations are turned off.
Even if you force some optimizations by hand, in debug mode, the
compiler will start
using floating-point stack and registers for variables. the debugger,
however,does not seem
to understand about these optimizations, so, inspecting the variables
gives you the wrong
results. (how people manage to work in this environment is beyond me, my
good ole delphi
never does thigs like this!)
Could someone please verify my findings, especially the one about the
correct setting for
MSVC to compile the dll ? and, assuming the above is verified, I think
we should add
a warning in the WIKI for poor souls who will try to compile using MS
Visual Studio 2005
in the future !
PS
when testing, pls note that the value 9.95 is a "magic" value, due to
the way the
code is written in print.c. AFAIK it is the only value manifesting the
compiler bug,
probably due to the way that the imput to ROUND() is compared to the
value "10.0" in
various points in the code.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
