sqlite> select roundhe(3.255, 2);
3.26
sqlite> select printf('%!.18f', 3.255);
3.254999999999999893
sqlite> select printf('%!.18f', roundhe(3.255, 2));
3.259999999999999787
sqlite> select printf('%!.18f', pow(10.0, 2));
100.0
sqlite> select printf('%!.18f', pow(10.0, 2) * 3.255);
325.5
The good: proper rounding (half-even) within the limits of the floating-point
math processor/library of your computer.
The bad: drags in your platform math library and requires that it work
according to specifications.
Requires: IEEE-754 representation is "round nearest" (which it usually is).
and never round intermediates. Using intermediates to which rounding has been
applied invalidates the precision guarantees (such as they are) of IEEE-754.
SQLITE_PRIVATE void _heroundingFunc(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
int p = 0;
double x, scale, xval, ipart, fpart, sgn;
if ((argc == 0) || (argc > 2))
return;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
return;
x = sqlite3_value_double(argv[0]);
if (argc == 2)
{
if (sqlite3_value_type(argv[1]) == SQLITE_NULL)
return;
p = sqlite3_value_int(argv[1]);
p = p > 15 ? 15 : (p < 0 ? 0 : p);
}
scale = pow(10.0, p);
sgn = 1.0;
if (x < 0)
sgn = -1.0;
xval = sgn * x * scale;
if (log10(xval) > 16.0)
{
sqlite3_result_double(context, x);
return;
}
fpart = modf(xval, &ipart);
if ((fpart > 0.5) || ((fpart == 0.5) && (fmod(ipart, 2.0) == 1.0)))
ipart += 1.0;
xval = sgn * ipart / scale;
sqlite3_result_double(context, xval);
}
Note that you can implement "grade school" rounding (round half away from zero)
by changing:
if ((fpart > 0.5) || ((fpart == 0.5) && (fmod(ipart, 2.0) == 1.0)))
ipart += 1.0;
to
if (fpart => 0.5)
ipart += 1.0;
This is the part that implements the rounding since you know the sign (sgn),
the integer part (ipart), and the remainder (fpart) and unless you change the
ipart then the result is merely the truncation of the value (towards zero).
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
