SQLite Forum

change in round() function
Login

change in round() function

(1) By Tom B (tb13) on 2020-05-15 17:01:48 [link] [source]

Hi, sorry if this has been asked before, or if I missed the thread in this forum or the old mailing list.

The behavior of the round() function seems to have changed between v3.28 and v3.29, but I don't see where it is mentioned in the release notes.

On my FreeBSD 11.3 server, v3.29 is the default SQLite, and I see this:
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select round(1.15,1); select round(9.95,1);
1.2
10.0

On my iMac running macOS Catalina (10.15.3), the built-in sqlite3 is v3.28:
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select round(1.15,1); select round(9.95,1);
1.1
9.9

I found the example numbers in mailing list.

How is the newer round() implemented?

Here are the release notes for v3.29:
2019-07-10 (3.29.0)

Added the SQLITE_DBCONFIG_DQS_DML and SQLITE_DBCONFIG_DQS_DDL actions to sqlite3_db_config() for activating and deactivating the double-quoted string literal misfeature. Both default to "on" for legacy compatibility, but developers are encouraged to turn them "off", perhaps using the -DSQLITE_DQS=0 compile-time option.
-DSQLITE_DQS=0 is now a recommended compile-time option.
Improvements to the query planner:
Improved optimization of AND and OR operators when one or the other operand is a constant.
Enhancements to the LIKE optimization for cases when the left-hand side column has numeric affinity.
Added the "sqlite_dbdata" virtual table for extracting raw low-level content from an SQLite database, even a database that is corrupt.
Enhancements to the CLI:
Add the ".recover" command which tries to recover as much content as possible from a corrupt database file.
Add the ".filectrl" command useful for testing.
Add the long-standing ".testctrl" command to the ".help" menu.
Added the ".dbconfig" command

(2) By Keith Medcalf (kmedcalf) on 2020-05-15 17:52:04 in reply to 1 [link] [source]

The round() function (func.c) was not changed. It still works the same way it always did.

First the arguments are checked to see if rounding is needed. Secondly, they are checked to see if the result will fit in a 64-bit integer and to do that if so.

If rounding still needs to be performed, then use sqlite3_mprintf to "print" the value as a character string with the requested precision. The resulting character string is then converted back to a double using the sqlite3AtoF function.

This is done so that the grade-school (4/5) rounding result retains the same precision guarantees as are made by your platform (1 ulp for IEEE compliant platforms with half-even rounding).

The changes were made in SQLite3's "printf" code to more accurately convert floating point numbers to character strings by using IEEE compliant "half's" rather than computed half's.

In other words, where the code used to (more or less) do rounding by scaling then adding .5 then descaling, it now does not scale and adds 0.5, 0.05, 0.005, etc directly, thus maintaining 1 ulp precision. These changes were in printf.c and apply everywhere conversion from double to text is performed.

(3) By Tom B (tb13) on 2020-05-15 18:21:08 in reply to 2 [link] [source]

Ah I see.  The round() function did not change, but the printf functions it uses did and they do the heavy lifting.  I enclosed the function from the 3.31.1 src below.

Do you think that this change makes SQLite sufficient for most financial calculations, to the cent?

Thanks Keith!  I am so glad to see you are still on this forum after the mailling list was deprecated!
Tom

====

static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  int n = 0;
  double r;
  char *zBuf;
  assert( argc==1 || argc==2 );
  if( argc==2 ){
    if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
    n = sqlite3_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  r = sqlite3_value_double(argv[0]);
  /* If Y==0 and X will fit in a 64-bit int,
  ** handle the rounding directly,
  ** otherwise use printf.
  */
  if( r<-4503599627370496.0 || r>+4503599627370496.0 ){
    /* The value has no fractional part so there is nothing to round */
  }else if( n==0 ){
    r = (double)((sqlite_int64)(r+(r<0?-0.5:+0.5)));
  }else{
    zBuf = sqlite3_mprintf("%.*f",n,r);
    if( zBuf==0 ){
      sqlite3_result_error_nomem(context);
      return;
    }
    sqlite3AtoF(zBuf, &r, sqlite3Strlen30(zBuf), SQLITE_UTF8);
    sqlite3_free(zBuf);
  }
  sqlite3_result_double(context, r);
}

(4.1) By Keith Medcalf (kmedcalf) on 2020-05-16 00:07:22 edited from 4.0 in reply to 3 [link] [source]

Well, double precision floating point has always been sufficiently accurate for financial calculation to the 1/100th of a unit, assuming that no "value" or intermediate "value" exceeds the precision capabilities of a double -- that is, provided that no value or intermediate exceeds (+/-)351,843,720,888.00 units, which has a precision greater than 1.0e-04.

The "old wives tale" about not using base-2 floating point for money has been perpetuated by a bunch of people who do not understand computers nor binary or computational theory.

The biggest problem in using floating point (no matter the precision), decimal floating point, or scaled decimal arithmetic is rounding of intermediates. Once you have addressed the rounding issue, Bob's your Uncle. If you haven't yet figured out how to address rounding, then nothing will help you. There are also other pathological problems when dealing with floating point, none of which have material impact if you are constraining the magnitude of the numbers you are playing with.

That said, the round() and double-to-text functions (printf) in SQLite are not suitable for financial applications. They do "Nearest Half-Away-from-Zero" rounding, not "Nearest Half-to-Even" or "Nearest Half-to-Odd" rounding.

The simplest implementation of Nearest Half-to-Even rounding can be implemented as follows (assuming of course that your compiler and platform implements IEEE standards properly):

double roundhalfeven(double x, int digits)
{
   double scale = pow(10.0, digits);
   return 2.0 * round(x / 2.0 * scale) / scale;
}

Bounds checking is left as an exercise for the reader.

see also https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

As noted, the biggest influence on the use of IEEE floating point to deal with currency are (a) failure to understand precision and computation theory (b) failure to understand and perform correct rounding; and, (c) junk ass compilers.

(5) By Keith Medcalf (kmedcalf) on 2020-05-16 09:32:11 in reply to 4.1 [source]

Ooopsie ... missed something in that round code -- it should only use that if the rounding difference is exactly 0.5 -- Should be:

double roundhalfeven(double x, int digits)
{
   double scale = pow(10.0, digits);
   double y;
   x *= scale;
   y = round(x);
   if (fabs(x - y) == 0.5)
      y = 2.0 * round(x / 2.0);
   return y / scale;
}

or even simpler

double roundhalfeven(double x, int digits)
{
   double scale = pow(10.0, digits);
   x *= scale;
   return (x - remainder(x, 1.0)) / scale;
}

(6) By Tom B (tb13) on 2020-05-16 17:41:25 in reply to 4.1 [link] [source]

Excellent explanation, Keith.  

Thank you very much!
Tom