Sqlite filtering on number with more that 5 decimal fails

[bscott]

I in a numeric field we have some values with more that 5 decimals the filter won't work

example update table set column = 1.00001 select * from table where column = 1.00001 This works fine

but update table set column = 1.000001 select * from table where column = 1.000001 does not return any records

Bruno

[traianstanev]

What do you mean by numeric -- REAL?

[bscott]

I have created the table using FDOdatatype_double In SqlLiteStudio it tell me it's REAL

[traianstanev]

In general, you are not supposed to compare floating point numbers directly. 1.000001 is an infinite fraction in binary, which means that it could get rounded or truncated, depending on how the code was compiled. My guess is that the binary representation stored in the database differs by one bit from the number you use to compare due to different string to double conversion code being used resulting in rounding/truncation discrepancy.

[bscott]

The same query works find when executed out of FDO. If i run "select * from table where column = 1.000001" in a native SQLIte engine it works fine.

By the way > , < , >= , <= have the same bug.

Really likely that the String/Double truncation is in the provider

[traianstanev]

Was the native SQLite engine compiled with /fp:fast and SSE2 settings turned on?

[bscott]

i can't tell i've tested against

Sqllite Studio SqlLite Database Browser 2 SqlLiteMan

All 3 works fine

[traianstanev]

Are you using 64 bit or 32 bit builds?

[bscott]

Actually we are ruuning our unittest in 32 bit We will run then in 64 bit as soon as we will validate the 32 bit platform

[traianstanev]

I'm asking because floating point is different on the two, on 64 bit, all operations on double are always done to 64 bit, while on 32 bit, the floating point unit has 80 bit precision which doesn't always get truncated to 64 bits before comparisons are performed, depending on the compiler optimization settings used. So those operations can give different results on 32 vs 64.

[bscott]

I found the problem, I've taken the same double precision approach as the GenericRdbms and the KingOracle provider Patch is attached

[traianstanev]

Indeed, you're right, snprintf would screw it up before it even got to the floating point comparison instructions.