During a migration from ASA 220.127.116.1174 to SA 18.104.22.16898, I have finally compared the unloaded data from
Fortunately, the exported data is identical - with a few small differences. That does puzzle me.
The differences appear in some columns with the double datatype and differ in the part beyond the significant 15 digits. So, it doesn't violate the "guaranteed" preciseness. (And I'm aware that a precise numerical datatype would not lead to such problems, Breck.)
However, I'm still wondering why the same value can be unloaded and reloaded and take a different representation.
The value "1.84" cannot be represented as double. The best-fitting representation seems to be "1.8400000000000003". However, the v8 database has several columns that display that value 1.84, and it is unloaded as 1.84. The v12 database displays and unloads the value as 1.8400000000000003. FWIW, the following does display an identical value in v8 and v12 (with "1.66" being a similar case):
select cast(1.84 as double), cast(1.66 as double) -- displays 1.8400000000000003, 1.6599999999999997
Even manipulating the value in v8 ("update ... set val = val + 0.0" or "... set val = val * 1.0" does not lead to the display of the correct frational part.
In many cases, the data is old and has been converted from an older MS SQL database years ago. So I had suspected it could be due to that conversion or a different treatment of doubles on an older machine/OS. But it happens with quite new data, too.
As stated, the minimal differences are not problematic at all from an application's point of view, and of course they are not used as keys - they're just data.
Is there any explanation for this effect - something that has been fixed in newer versions? Or do values that cannot be represented exactly as double (like 1.84) have several approximate - and therefore non-deterministic - representations?
As you said, you are using the same V12 dbunload tool to perform the unload, however, the underlying server is not the same. When you use dbunload to unload a V10, 11 or 12 database, the server that is used to read the original V10, 11, 12 database is the SA 12 server that matches the dbunload tool. But, that same SA 12 server cannot be used to read the original V9, 8, 7, ... database since SA 10 and up do not support databases prior to SA 10. For unloading legacy databases, the dbunload utility uses a legacy program to read the older database and that program for reading legacy databases is built with a different compiler and compile options (out of necessity). Hence, you will see minor precision differences when unloading an SA 10 and up database versus unloading an ASA 9 and below database.
answered 06 Apr '11, 09:59
With 11.0.1 dbisql and also a tool using ODBC display always the rounded values for your example: 1.84,1.66 The .dat file of the unload bears as you said the fullprecision values. So it seems to be just a flaw of your client if the values are not displayed rounded.
Anyway IEEE defines 5 rounding algorithms so different implementations can show different results.
You can try for instance 1.8400000000000003 and 1.8400000000000004 they will display the same (rounded) 1.84
answered 06 Apr '11, 04:59