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
