Incorrect Float Handling????

Try this: - Create a table "test" with only one column (quantity float) - Add a record with the following value (0.94) to table "test" - Run any of the following queries on this table : Query 1: "Select quantity, round(quantity * 10000000000000000) from test" Returns: quantity (expression) 0.94 9399999999999999 Query 2: "Unload to /tmp/test.ld select * from test" test.ld contains the following: 0.9399999999999999 The same also happens for the following decimal values: 8.8 0.07 8.28 9.80 68.6 8.47 9.46 0.69 7.04 It is usually easier to see what happens by doing an unload (query 2). Query 1 works only when quantity is multiplied with the maximum value to convert quantity to an integer value of 16 digits. Is there any logical explanation or even a work-around for this. Unfortunately I can not change my columns to decimal (asuming that it won't cause the same problem) because that is the way that my third-party application creates the tables and it does not like me messing around with data types. By the way - I have tested this on various versions : Informix SE 5.01 on SCO OpenServer 5.04 on Pentium 166MMX Informix SE 5.01 on SCO OpenServer 5.05 on Pentium II 450 Informix SE 7.23UC13 on SCO OpenServer 5.04 on Pentium 166MMX Informix SE 7.23UC13 on SCO OpenServer 5.05 on Pentium II 450 Informix SE 7.23UC13 on SCO OpenServer 5.05 on Pentium II 266

I think this is simply a characteristic of floatingpoint in general. Take the following program and run it.... ------------------------------------------------- #include <stdio.h> void main() { float quantity = 0.94; float col2; printf("%f\\n", quantity * 1000000000000000); col2 = 1000000000000000; printf ("%f %f\\n", quantity, col2); } -------------------------------------------------------- On my system the output is: 939999964954624.000000 0.940000 999999986991104.000000 The results are caused because 10000000000000000 is promoted to a float according to 'c' promotion rules (int_field * float_field ) -> ( (float) int_field * float_field). Well 10000000000000000 won't fit exactly in a float field. This results in an "approximation error". JPS wrote: > Try this: > - Create a table "test" with only one column (quantity float) > - Add a record with the following value (0.94) to table "test" > - Run any of the following queries on this table : > > Query 1: > "Select quantity, round(quantity * 10000000000000000) from test" > Returns: > quantity (expression) > > 0.94 9399999999999999 > > Query 2: > "Unload to /tmp/test.ld select * from test" > test.ld contains the following: 0.9399999999999999 > > The same also happens for the following decimal values: > 8.8 0.07 8.28 9.80 68.6 8.47 9.46 0.69 7.04 > > It is usually easier to see what happens by doing an unload (query 2). > Query 1 works only when quantity is multiplied with the maximum value to > convert quantity to an integer value of 16 digits. > > Is there any logical explanation or even a work-around for this. > Unfortunately I can not change my columns to decimal (asuming that it won't > cause the same problem) because that is the way that my third-party > application creates the tables and it does not like me messing around with > data types. > > By the way - I have tested this on various versions : > Informix SE 5.01 on SCO OpenServer 5.04 on Pentium 166MMX > Informix SE 5.01 on SCO OpenServer 5.05 on Pentium II 450 > Informix SE 7.23UC13 on SCO OpenServer 5.04 on Pentium 166MMX > Informix SE 7.23UC13 on SCO OpenServer 5.05 on Pentium II 450 > Informix SE 7.23UC13 on SCO OpenServer 5.05 on Pentium II 266

My guess is that 0.94 is not exactly representable in IEEE 64bit floating point format which is what a type float column is. If you need absolute accuracy you should be using Informix DECIMAL type. This uses BASE100 arithmatic and so everything that is representable in decimal notation is also representable exactly in BASE100 notation. Informix DECIMAL is fairly efficient as well. The data format is optimized for both calculation and conversion to text. Art S. Kagel JPS wrote: > > Try this: > - Create a table "test" with only one column (quantity float) > - Add a record with the following value (0.94) to table "test" > - Run any of the following queries on this table : > > Query 1: > "Select quantity, round(quantity * 10000000000000000) from test" > Returns: > quantity (expression) > > 0.94 9399999999999999 > > Query 2: > "Unload to /tmp/test.ld select * from test" > test.ld contains the following: 0.9399999999999999 > > The same also happens for the following decimal values: > 8.8 0.07 8.28 9.80 68.6 8.47 9.46 0.69 7.04 > > It is usually easier to see what happens by doing an unload (query 2). > Query 1 works only when quantity is multiplied with the maximum value to > convert quantity to an integer value of 16 digits. > > Is there any logical explanation or even a work-around for this. > Unfortunately I can not change my columns to decimal (asuming that it won't > cause the same problem) because that is the way that my third-party > application creates the tables and it does not like me messing around with > data types. > > By the way - I have tested this on various versions : > Informix SE 5.01 on SCO OpenServer 5.04 on Pentium 166MMX > Informix SE 5.01 on SCO OpenServer 5.05 on Pentium II 450 > Informix SE 7.23UC13 on SCO OpenServer 5.04 on Pentium 166MMX > Informix SE 7.23UC13 on SCO OpenServer 5.05 on Pentium II 450 > Informix SE 7.23UC13 on SCO OpenServer 5.05 on Pentium II 266