-----Original Message-----
From: mike cariotoglou [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 08, 2006 5:27 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] weird (and dangerous) bug in Microsoft Compiler
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]
--------------------------------------------------------------
---------------
