|
During a migration from ASA 8.0.3.5574 to SA 12.0.1.3298, 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. For example: 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. Question: 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? |
|
Volker, 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. Thanks for that sound explanation, Karim. I just had not thought of the particular dbunlspt.exe (build 9.0.2.3961, compiled with VS 2002, methinks) coming into play. I remember having seen the same behaviour during former tests with migrations from v8 to v10 and v11, and that would be due to the same background, as the v10 and v11 executables seem to be compiled with newer VS tools, too (VS 2005/2008, I'd say).
(06 Apr '11, 10:51)
Volker Barth
Karim, but that explanation still don't seem to tell why v8 can contain a double column with the value "1.84" when the same dbengine tells select cast(1.84 as double) = "1.8400000000000003", right? Might there be some kind of "impreciseness" in the handling of doubles in v8?
(07 Apr '11, 03:36)
Volker Barth
|
|
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 "The .dat file of the unload bears as you said the fullprecision values." - No, that's not true. As stated, the data unloaded from the v8 database has the value 1.84, the data unloaded from v12 has the value 1.8400000000000003. Both are unloaded with the same v12 dbunload tool. dbunload itself is just a client tool, that's understood, but as I use the same tool in both cases that would not explain any differences in the implementation... BTW, I have used the v8 and v12 dbisqlc to display the values (AFAIK, still ESQL-based). They do not round values as DBISQL seems to do. But the different dbisqlc versions don't behave differently: Displaying the v8 data with v12 dbisqlc still displays "1.84", and dispalying the v12 data with v8 dbisqlc still displays "1.8400000000000003". So there must be another explanantion for this odd behaviour.
(06 Apr '11, 05:18)
Volker Barth
|
