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
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