There are 3 places in 3.7.14 sqlite3.c where %!.15g" format is used. Ergo 15 significant digits on output. 57186: sqlite3_snprintf(nByte, pMem->z, "%!.15g", pMem->r); 62788: sqlite3XPrintf(&out, "%!.15g", pVar->r); 85973: sqlite3_snprintf(sizeof(zBuf), zBuf, "%!.15g", r1);
For this example, changing 57186 to 17g almost gets the answer correct: sqlite> create table t(f real); sqlite> insert into t values(1.7976931348623157e+308); sqlite> select * from t; 1.7976931348623156e+308 The odds you hitting a random number that isn't represented correctly is pretty slim. You can show the loss in when using %!.17g sqlite> insert into t values(1.7976931348623157e+308); sqlite> select * from t; 1.7976931348623156e+308 sqlite> insert into t values(1.7976931348623156e+308); sqlite> select * from t; 1.7976931348623156e+308 1.7976931348623154e+308 sqlite> insert into t values(1.7976931348623154e+308); sqlite> select * from t; 1.7976931348623156e+308 1.7976931348623154e+308 1.7976931348623152e+308 sqlite> insert into t values(1.7976931348623154e+308); sqlite> insert into t values(1.7976931348623152e+308); sqlite> select * from t; 1.7976931348623156e+308 1.7976931348623154e+308 1.7976931348623152e+308 1.7976931348623152e+308 1.797693134862315e+308 1.7976931348623156083e+308 is the highest value for 7FEFFFFFFFFFFFFE 1.79769313486231580793e+308 is the highest value for 7FEFFFFFFFFFFFFF That's why the last digit of 7 is the "most correct" as it's the average error. I found this one added line seems to fix the problem. /* Significant digits after the decimal point */ while( (precision--)>0 ){ *(bufpt++) = et_getdigit(&realvalue,&nsd); } if ( realvalue >=5 ) (*(bufpt-1))++; // round up I'm unsure if any additional checks are required...since everything should be a power of 2 on the last digit you should only be increasing even numbers so I don't think roll over should occur to the next higher digit. With that change you can insert and select and not lose precision SQLite version 3.7.14 2012-09-03 15:42:36 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(f real); sqlite> insert into t values(1.7976931348623156e+308); sqlite> select * from t; 1.7976931348623155e+308 sqlite> insert into t values(1.7976931348623155e+308); sqlite> select * from t; 1.7976931348623155e+308 1.7976931348623155e+308 sqlite> insert into t values(1.7976931348623157e+308); sqlite> select * from t; 1.7976931348623155e+308 1.7976931348623155e+308 1.7976931348623157e+308 sqlite> insert into t values(1.7976931348623158e+308); sqlite> select * from t; 1.7976931348623155e+308 1.7976931348623155e+308 1.7976931348623157e+308 1.7976931348623157e+308 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Tuesday, September 11, 2012 8:01 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] selecting real values On 11 Sep 2012, at 1:56pm, "Black, Michael (IS)" <michael.bla...@ngc.com> wrote: > A quick experiment shows that 3.7.14 rounds off the last 2 digits of a > double-precision. I think we found that the rounding was happening during the translation from text input to having the number stored in the file. Writing a C app which took random 64-bit floats, stored them in a SQLite database, and retrieved them, yielded unchanged values. Dumping the bit pattern as actually stored in the files suggested that the numbers were being stored intact. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users