Re: [sqlite] Bug in SQLite FLOAT values

[Keith Medcalf]

A double precision floating point value contains about 14.5 to 16 digits of 
precision WHEN CONVERTED FROM BINARY TO DECIMAL TEXT.  However, the double 
precision number is merely the closest binary approximation of a value as can 
be encoded in BINARY (base 2) format.  Exact DEMAL (base 10) values cannot be 
represented in binary (base 2) floating point.  When you "round off" a floating 
point value to contain, as you put it, merely 6 digits of precision, what you 
are doing is throwing away some approximation data and making the value a less 
precise approximation of your diddled value -- you make it impossible to access 
the original value or to repeat the rerounding and achieve the same result.  
You are converting a value which is the best approximation of the value to a 
worse approximation of a value.  That you only want to see 6 digits of 
displayed resolution is a display problem, not a value storage and retrieval 
problem.

You are doing the equivalent of taking a beautiful 4096x2048 32-bit CMYK image 
and compressing it to a 16 KByte JPEG of postage stamp size (which it does by 
throwing away information such that it becomes permanently inaccessible and 
permanently irretrievable).  When you then "blow up" the JPEG back to the 
original size you are greeted by pixelated eye-bleed causing crap that bears no 
resemblance whatsoever to the original data.  The same applies to non-lossless 
audio compression formats as well (nay, all digital audio formats, truth be 
told).  Compressing an analogue signal into a 128kbit mpeg stream (or any other 
non-lossless format) permanently destroys data content, making it impossible to 
retrieve the original data (and in the case of audio, leaving only crap that a 
tone deaf moron using "cheap tinkle" audio equipment could stand -- it makes 
the rest of us bleed out our ears).

So too is "diddling" with double precision floating point numbers causing 
irretrievable and irreperable damage to the data values contained in them.

You are confusing the "data value" (in this case the floating point value) with 
the display representation for biots (text strings in base-10 decimal 
representation).  Once converted, you are throwing away information which you 
will never be able to recover ever again.  Store and work with ONLY the double 
value that is provided to you without any tinkering.  If you BIOTs require 
displaying only six digits of precision, then display it to them that way.  Do 
not confuse BIOT display and BIOT input requirements with the internal 
representation of the data inside a digital computer system.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Abdul Aziz
>Sent: Saturday, 7 February, 2015 07:22
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Bug in SQLite FLOAT values
>
>OK, understood, thanks but can anyone explain me I was creating db of
>different sensors, I used same methods, but in fields which were FLOAT
>were
>filling with junk values (after 6 decimal places, see in SENSOR_1) why?
>even I was cutting it to 6 decimal places (as shown previously, was then
>again converting into FLOAT before insertion), when I used VARCHAR(TEXT),
>in different table with same methods, then was getting correct
>(formatted,
>upto 6 decimal places, see in latitude, longitude) values in SENSOR_99,
>please have a look...
>
>Thank you very much :)
>
>On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik <i...@tandetnik.org>
>wrote:
>
>> On 2/7/2015 8:47 AM, Abdul Aziz wrote:
>>
>>> Thanks for replybut I am now using VARCHARS, then how this is
>working?
>>> not generating any errors?
>>>
>>
>> When Tim said "Read this", he meant it. http://www.sqlite.org/
>> datatype3.html answers your questions (but only if you read it).
>> --
>> Igor Tandetnik
>>
>>
>> ___
>> 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

Re: [sqlite] Bug in SQLite FLOAT values

[Simon Slavin]

On 7 Feb 2015, at 2:22pm, Abdul Aziz  wrote:

> but in fields which were FLOAT were
> filling with junk values (after 6 decimal places, see in SENSOR_1) why?

Take a look at what happens when you try to write 1/13th in decimal:

0.076923076923076923076923076923076923076923076923076923.

What about 7/9ths:

0.77.

And PI:

3.1415926535897932384626443323279502. (I forget the rest)

There's no way to write those numbers precisely in decimal.  The first repeats 
after the sixth place.  The second repeats after the first place.  The third 
never repeats, but also never stops.  Similarly there's no way to write some 
numbers precisely in binary.  So if I tell you you had to write the number 
using just digits and a decimal point, you can't do it.  Similarly, if you 
convert some numbers into binary format and back (just 0 and 1 and a decimal 
point) you don't get back precisely the number you put in.

Fields defined as FLOAT (in your Android API) have their values stored in 
binary format.  So storing a number in that field involves converting it to 
binary.  If, instead, you define your fields as text no conversion takes place 
because there's no need to turn the number into binary format.

(Above explanation simplified with respect to affinities and deep maths for 
simplicity.)

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite FLOAT values

[Abdul Aziz]

OK, understood, thanks but can anyone explain me I was creating db of
different sensors, I used same methods, but in fields which were FLOAT were
filling with junk values (after 6 decimal places, see in SENSOR_1) why?
even I was cutting it to 6 decimal places (as shown previously, was then
again converting into FLOAT before insertion), when I used VARCHAR(TEXT),
in different table with same methods, then was getting correct (formatted,
upto 6 decimal places, see in latitude, longitude) values in SENSOR_99,
please have a look...

Thank you very much :)

On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik  wrote:

> On 2/7/2015 8:47 AM, Abdul Aziz wrote:
>
>> Thanks for replybut I am now using VARCHARS, then how this is working?
>> not generating any errors?
>>
>
> When Tim said "Read this", he meant it. http://www.sqlite.org/
> datatype3.html answers your questions (but only if you read it).
> --
> Igor Tandetnik
>
>
> ___
> 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

Re: [sqlite] Bug in SQLite FLOAT values

[Keith Medcalf]

Why would an application need to use the SQLite printf function to convert 
doubles to formatted text?  The application ought to store and retrieve the raw 
doubles completely unadulterated (with no diddling, using the value_double and 
bind_double interfaces), and "format the value for display" when it is 
displayed.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Kees Nuyt
>Sent: Saturday, 7 February, 2015 18:14
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Bug in SQLite FLOAT values
>
>On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz <abduldblog...@gmail.com>
>wrote:
>
>> Hi there!
>> I hope you are well!
>>
>> Recently I was working on project based on Android Sensors and
>encountered
>> a bug in sqlite db, situation was this:
>> I was setting there three values x,y,z as FLOAT, android inbuilt
>sensors
>> were receiving values as float upto 8 decimal places, but I wanted to
>store
>> value only upto 6 decimal place, so in android this is the way that
>first
>> you will have to convert that value into String , as* String sLongitude
>=
>> String.format("%.6f", x);*
>
>As others have said, you shouldn't confuse the storage
>format (how a value is stored in the database) with
>the presentation (how data is displayed on output).
>
>Luckily, recently sqlite got a printf() function.
>
>Demo:
>
>$ sqlite3 test.db
>SQLite version 3.8.8 2015-01-30 20:59:27
>Enter ".help" for usage hints.
>sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
>sqlite> insert into t3 (id,lat,lon) VALUES
>(1,1.234567890123,5.6789012345678);
>sqlite> select printf('id:%3d, latitude: %9.6f, longitude:
>%9.6f',id,lat,lon) from t3;
>id:  1, latitude:  1.234568, longitude:  5.678901
>sqlite>
>
>Hope this helps.
>
>--
>Regards,
>
>Kees Nuyt
>
>___
>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

Re: [sqlite] Bug in SQLite FLOAT values

[Kees Nuyt]

On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz  wrote:

> Hi there!
> I hope you are well!
>
> Recently I was working on project based on Android Sensors and encountered
> a bug in sqlite db, situation was this:
> I was setting there three values x,y,z as FLOAT, android inbuilt sensors
> were receiving values as float upto 8 decimal places, but I wanted to store
> value only upto 6 decimal place, so in android this is the way that first
> you will have to convert that value into String , as* String sLongitude =
> String.format("%.6f", x);*

As others have said, you shouldn't confuse the storage 
format (how a value is stored in the database) with
the presentation (how data is displayed on output).

Luckily, recently sqlite got a printf() function.

Demo:

$ sqlite3 test.db
SQLite version 3.8.8 2015-01-30 20:59:27
Enter ".help" for usage hints.
sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
sqlite> insert into t3 (id,lat,lon) VALUES (1,1.234567890123,5.6789012345678);
sqlite> select printf('id:%3d, latitude: %9.6f, longitude: %9.6f',id,lat,lon) 
from t3;
id:  1, latitude:  1.234568, longitude:  5.678901
sqlite>

Hope this helps.

-- 
Regards, 

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite FLOAT values

[Simon Slavin]

On 7 Feb 2015, at 5:57am, Abdul Aziz  wrote:

> *again I converted back to float this string formatted value, and printed
> into Log, I was clearly seeing values upto 6 decimal places , but after
> insertion into sqlite db, when after generation of sqlite db file, was
> getting values upto 11-12 decimal places!*

SQLite itself would have converted your text (to six digits) and stored the 
resulting number. When you asked to retrieve your value, SQLite would have 
retrieved that number -- still at six digits.

However if you ask for the retrieved figure as a number (as opposed to a 
string) the programming language you use has to put the resulting number into a 
'float' variable.  And in doing this it would have to turn the number back into 
float format, which would introduce the extra 'garbage' digits.  So yes, you 
can argue that there is a bug somewhere, but if there is one it's in the 
Android interface to SQLite, not in SQLite itself.

You can avoid this by asking for the retrieved figure as a string, not a 
number.  Or by storing the value as a TEXT column instead of a REAL column, 
which is the solution you came up with.  So I'm glad you found a solution.

It might be worth asking yourself why you are trimming your values to six 
digits and then saving the result as a number.  It would make more sense to 
trim your values and then handle the number as a string from then onwards.  Or 
to handle all the digits you have and to convert to text as six digits just 
before you put the number on the display.  Both of these would be more 
mathematically 'correct' than what you are doing.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite FLOAT values

[RSmith]

On 2015/02/07 15:47, Abdul Aziz wrote:

Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?

Please elaborate, my query to create DB is:
mSQLiteDatabase.execSQL("CREATE TABLE " + tableName
+ " ( "
+ EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, "
+ ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z
+ " VARCHAR );");


It translates Varchar to Text, that's why no errors are generated, it understands what you mean by Varchar, which is really just 
some text, so it translates it to the internal type TEXT.


As for your question about decimal points and floats, no float in any language stores values up to a certain length... lengths are 
the domain of Strings and text, not Floating point numbers. any floating point number is an approximate number with a representation 
as close as is possible to the actual number. That representation includes many significant digits in the significand and an 
exponent. You may need to read up on floats some more to see how it works - my point is just that it doesn't  store numbers up to a 
certain length, for that you need a formatter.


Many DB engines offer formatted types, such as Decimal (in PG, Oracle, MSSQL, etc) where you can say you need the number with so 
many decimals after the point.  In SQLite you can format the output (much like your C solution) by doing  SELECT 
printf('%.6f',somevalue); etc.


Read the pages offered by the other posters and maye check out the Wikipedia pages on floating point storage and representation to 
understand WHY all the above happens, but to solve your immediate problem, use the output formatting or store as strings - there is 
no way to tell a true floating number to keep itself short.


Other interesting things you can see about this floating point problem (it's a mathematical problem too), look on youtube for "Why 
is 0.9... equal to 1?" or "How do we know two numbers are distinct?" - The Numberphile videos in general do a good job of 
explaining it.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite FLOAT values

[Igor Tandetnik]

On 2/7/2015 8:47 AM, Abdul Aziz wrote:

Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?


When Tim said "Read this", he meant it. 
http://www.sqlite.org/datatype3.html answers your questions (but only if 
you read it).

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite FLOAT values

[Abdul Aziz]

Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?

Please elaborate, my query to create DB is:
mSQLiteDatabase.execSQL("CREATE TABLE " + tableName
+ " ( "
+ EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, "
+ ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z
+ " VARCHAR );");

On Sat, Feb 7, 2015 at 7:08 PM, Tim Streater  wrote:

> On 07 Feb 2015 at 05:57, Abdul Aziz  wrote:
>
> > *I think this is a bug, this means float values in sqlite will always be
> > filled upto 11-12 decimal places, in any case, you will have to fill it,
> or
> > sqlite will fill it itself with junk values, **this may create lot of
> > consumption of memory while working on larger projects...*
>
> Read this:
>
>   http://www.sqlite.org/datatype3.html
>
> Note that:
>
> 1) There are no varchars in SQLite
>
> 2) Floats always occupy 8 bytes
>
> --
> Cheers  --  Tim
>
> ___
> 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

Re: [sqlite] Bug in SQLite FLOAT values

[Tim Streater]

On 07 Feb 2015 at 05:57, Abdul Aziz  wrote: 

> *I think this is a bug, this means float values in sqlite will always be
> filled upto 11-12 decimal places, in any case, you will have to fill it, or
> sqlite will fill it itself with junk values, **this may create lot of
> consumption of memory while working on larger projects...*

Read this:

  http://www.sqlite.org/datatype3.html

Note that:

1) There are no varchars in SQLite

2) Floats always occupy 8 bytes

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users