Hi,
We have found that some Double values is truncated when they are retrieved from the database.
The following code is an example when the error occurs.
c <- connectSqlite3 "testHDBC.db"
create <- prepare c "create table abc (A real)"
execute create []
insert <- prepare c "insert into abc (A) values (?)"
execute insert [(toSql (1.662937779684191 :: Double))]
select <- prepare c "select * from abc"
execute select []
res <- fetchAllRowsAL' select
print (res)
let x = fromSql (snd $ head $ head res) :: Double
print (x == 1.662937779684191)
commit c
disconnect c
The output is:
[[("A",SqlDouble 1.66293777968419)]]
False
We compared this behavior with the sqlite shell and c code and found out that the printed value seems to be equal to your values. However, the printed value uses a print function that only prints the 15 most significant digits even if the stored value has higher precision (more digits) in the sqlite shell/C code. It is possible to compare the exact value which is stored in the database (in the sqlite shell/C code) with for example a cross join to see if the printed value is the same as the stored value. We have inserted the values from the example (1.662937779684191) and (1.66293777968419) and compared them with a cross join which showed us that they were different.
We also looked at your code and from what we can understand, you are using the sqlite3_column_text function to retrieve all values which indeed only retrieves 15 digits. To get the exact value, functions like sqlite3_column_double or sqlite3_column_value and then sqlite3_value_double should be used in order to retrieve the exact double and to behave like sqlite does.
Hi, We have found that some Double values is truncated when they are retrieved from the database.
The following code is an example when the error occurs.
c <- connectSqlite3 "testHDBC.db"
create <- prepare c "create table abc (A real)" execute create []
insert <- prepare c "insert into abc (A) values (?)" execute insert [(toSql (1.662937779684191 :: Double))]
select <- prepare c "select * from abc" execute select [] res <- fetchAllRowsAL' select
print (res) let x = fromSql (snd $ head $ head res) :: Double print (x == 1.662937779684191)
commit c disconnect c
The output is: [[("A",SqlDouble 1.66293777968419)]] False
We compared this behavior with the sqlite shell and c code and found out that the printed value seems to be equal to your values. However, the printed value uses a print function that only prints the 15 most significant digits even if the stored value has higher precision (more digits) in the sqlite shell/C code. It is possible to compare the exact value which is stored in the database (in the sqlite shell/C code) with for example a cross join to see if the printed value is the same as the stored value. We have inserted the values from the example (1.662937779684191) and (1.66293777968419) and compared them with a cross join which showed us that they were different.
We also looked at your code and from what we can understand, you are using the sqlite3_column_text function to retrieve all values which indeed only retrieves 15 digits. To get the exact value, functions like sqlite3_column_double or sqlite3_column_value and then sqlite3_value_double should be used in order to retrieve the exact double and to behave like sqlite does.
Best regards @vico7 @winzor92