Numerical precision in SQLite

2

About numerical precision in SQLite v3.20.0. See the command sequence below and the .dump result. The value associated with the INSERT statement is not the same value as that obtained. I enter the value 123456.789 and I get 123456.7890000000043. How can this happen?

SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE Teste (Campo Number(9,3));
sqlite> INSERT INTO Teste Values (123456.789);
sqlite> SELECT * From Teste;
123456.789
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Teste (Campo Number(9,3));
INSERT INTO Teste VALUES(123456.7890000000043);
COMMIT;
sqlite>
    
asked by anonymous 06.09.2017 / 21:41

1 answer

1

The binary floating-point math is like this. In most programming languages (not only in SQLLite), it is based on the IEEE 754 standard. For example, JavaScript uses the 64-bit floating-point representation, which is the same as double Java. The crux of the problem is that the numbers are represented in this format, since an integer has a power of two; rational numbers (such as 0,1, which is 1/10) whose denominator is not a power of two, can not be exactly represented. Here are the references: Wiki - Float Binary Representation

    
06.09.2017 / 22:18