FreeTDS Development Group

["Craig A. Berry" <craigberry AT mac.com>]

Patch inline for easy review and also attached for easy application.

From 3bbd7d18bef4469f52c14053c8370529a6e7ec15 Mon Sep 17 00:00:00 2001
From: "Craig A. Berry" <craigberry AT mac.com>
Date: Thu, 7 Aug 2014 12:12:22 -0500
Subject: [PATCH] Preserve enough digits when converting float to char.

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

Attachment: 0001-Preserve-enough-digits-when-converting-float-to-char.patch
Description: Binary data