The floating point standard (IEEE 754) says that in order to make
a round trip conversion from binary floating point values to
character and back again, you need to preserve 9 digits for 32-bit
floats and 17 digits for 64-bit floats in order avoid loss of
precision.
We were only preserving 7 digits for singles and 16 for doubles,
so we could experience loss of precision when making such round-trip
conversions, such as bulking out and bulking back in again.
---
src/tds/convert.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/tds/convert.c b/src/tds/convert.c
index 5b49118..2d27856 100644
--- a/src/tds/convert.c
+++ b/src/tds/convert.c
@@ -1525,7 +1525,7 @@ tds_convert_real(const TDS_REAL* src, int desttype, CONV_RESULT * cr)
switch (desttype) {
case TDS_CONVERT_CHAR:
case CASE_ALL_CHAR:
- sprintf(tmp_str, "%.7g", the_value);
+ sprintf(tmp_str, "%.9g", the_value);
return string_to_result(tmp_str, cr);
break;
@@ -1638,7 +1638,7 @@ tds_convert_flt8(const TDS_FLOAT* src, int desttype, CONV_RESULT * cr)
switch (desttype) {
case TDS_CONVERT_CHAR:
case CASE_ALL_CHAR:
- sprintf(tmp_str, "%.16g", the_value);
+ sprintf(tmp_str, "%.17g", the_value);
return string_to_result(tmp_str, cr);
break;
--
1.8.4.2
The round-trip conversion problem can be illustrated by creating and populating a table like so:
create table tempdb.dbo.test_float (
[my_single] [float](24) NULL,
[my_double] [float](53) NULL
)
insert into tempdb.dbo.test_float values ('4.149999', '4.1499999999999995');
If you bulk out that table in character format before my change you get:
$ cat tf.bcp
4.149999 4.149999999999999
but after my change you get:
$ cat tf2.bcp
4.14999914 4.1499999999999995
If you bulk both of those files back in and examine them you get:
1> select cast(my_single as varbinary), cast(my_double as varbinary)
2> from tempdb.dbo.test_float
3> go
4084cccb 4010999999999999
4084cccb 4010999999999998
4084cccb 4010999999999999
(3 rows affected)
The change appears not to be significant for single precision with my chosen example. But for double precision, the middle record (ending in '8' in binary) shows that there was a loss of precision before my change. I also tested with the Microsoft bcp client and it does not lose precision as ours does before this change.
I thought about a proper place to add a test for this. It appears src/tds/unittests/convert.c only tests the return values from conversion routines and not the contents of the converted data. I wasn't up to changing that at the moment.
________________________________________
Craig A. Berry
mailto:craigberry at mac.com
"... getting out of a sonnet is much more
difficult than getting in."
Brad Leithauser
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0001-Preserve-enough-digits-when-converting-float-to-char.patch
Type: application/octet-stream
Size: 1501 bytes
Desc: not available
URL: <http://lists.ibiblio.org/pipermail/freetds/attachments/20140807/af2c37f3/attachment-0001.obj>