FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

manoranjan_sahu AT agilent.com wrote:
> From SYBASE isql
> ========================
> 1>  select c1, c2 from t1  order by 1 
> 2> go
>  c1          c2                   
>  ----------- -------------------- 
>            1     123456792.000000 
>            2      12345678.000000 
>            3       1234567.125000 
>            4        123456.125000 
>            5         12345.123047
> 
> 
> From Freetds tsql
> ============================
> 1> select c1, c2 from t1  order by 1
> 2> go
> c1      c2
> 1       1.234568e+08
> 2       1.234568e+07
> 3       1234567
> 4       123456.1
> 5       12345.12
> 
> Please see the difference in data in Sybase isql and freetds tsql.
> 
> So my guess is freetds is doing something different from sybase isql.

Manoranjan, 

I think you're being confused by different representations of the same
data.  

If you look carefully, you can see that in each case, the answers match to
within 7 digits, which is the limit of the precision of reals in the
database.  

Sybase's isql is representing all floating point numbers as numeric(20,6),
even though the real datatype is limited to 7 digits of precision and the
float datatype is limited to 15.  That's why the answer you get back for
row 1 doesn't quite match what you inserted.  tsql is showing you 7
digits' worth.  The point isn't which one is better or which one is right.
 The point is they're the *same*!  

Rounded to 7 digits, 123456792.000000 = 1.234568e+08.  That's why I showed
you that the database itself yields that answer if you cast it.  The same
can be said for 12345.123047 = 12345.12.  In the latter case, that
0.003047 is just noise.  In the former case, the noise is a little bigger.
 Observe well:

1> select cast(123456789.123456789 as numeric(18,9)) -
cast(cast(123456789.123456789 as real) as numeric(18,9))
2> go
                       
 ----------------------
           -2.876543211

When you convert 123456789.123456789 to a real, you limit it to 7 digits
of precision, removing some information.  The nearest number it can
represent is slightly larger than 123456789.  Converting it back up to a
larger datatype doesn't magically recreate the information.  

Here's a little different test to help illustrate.  

        create table #t (delta real, c real)

Populate the table with 21 rows, holding c constant:

        declare @d real 
        select @d = -10 
        while @d < 11 begin 
                insert #t values (@d, 123456789.123456789) 
                select @d=@d+1 
        end

Now, add delta+constant, and see how many distinct values there are:

[28] varley.FreeTDS.1> select c, min(delta), max(delta), rounded from
(select cast(delta as smallint) as delta, c, delta + c as rounded from #t)
as A group by c, rounded
[28] varley.FreeTDS.2> go
 c                                  rounded             
 -------------------- ------ ------ --------------------
     123456792.000000    -10     -4     123456784.000000
     123456792.000000     -3      3     123456792.000000
     123456792.000000      4     10     123456800.000000

For 123456792 plus or minus 10, a real datatype can hold only three
distinct values.  As far as a real is concerned, 123456788, 123456789, and
123456790 (and others) are all the same number.  

If we insert those results in a new table #r and display 'rounded' as hex,
the reason becomes obvious:

[33] varley.FreeTDS.1> select cast(rounded as binary) from #r
[33] varley.FreeTDS.2> go
                                                                 
 ----------------------------------------------------------------
   0x00000000000000000000000000000000000000000000000000004ceb79a2
   0x00000000000000000000000000000000000000000000000000004ceb79a3
   0x00000000000000000000000000000000000000000000000000004ceb79a4

We can interpret 0x4ceb79a2 many ways, but one thing's for sure: there's
no value between that and 0x4ceb79a3.  

If you are still not convinced, please pick *one* row that you think is
wrong, and we'll go over it in excruciating detail.  ;-)

Say hello to MG Road for me.  

Regards, 

--jkl

> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Lowden, James K
> Sent: Tuesday, November 22, 2005 2:33 PM To: FreeTDS Development Group
> Subject: Re: [freetds] Truncated data for real data type in Sybase.
> 
> > From: manoranjan_sahu AT agilent.com
> > Sent: Tuesday, November 22, 2005 2:14 PM
> > 
> > I am using unixODBC with freetds v0.63RC10.
> > 
> > The issue I have is that my application is truncating the real
> > number data.
> > 
> > I was able to see this behavior through tsql as well. Here is the
> > test case.
> > 
> [modified to use temporary table]
> 
>         create table #t1 (c1 integer, c2 real)
>         go
>         delete from #t1
>         go
>         insert into #t1 values(1, 123456789.123456789)
>         insert into #t1 values(2, 12345678.123456789)
>         insert into #t1 values(3, 1234567.123456789)
>         insert into #t1 values(4, 123456.123456789)
>         insert into #t1 values(5, 12345.123456789)
>         go
> 
>         select c1, c2 from #t1 order by c1
>         go
> 
> Here are the results in sqsh:
> 
> [30] mpquant.testdb.1> select c1, c2 from #t1 order by c1
> [30] mpquant.testdb.2> select cast(123456789.123456789 as real)
> [30] mpquant.testdb.3> go
>  c1          c2
>  ----------- --------------------
>            1     123456792.000000
>            2      12345678.000000
>            3       1234567.125000
>            4        123456.125000
>            5         12345.123047
> 
> (5 rows affected)
> 
>  --------------------
>      123456792.000000
> 
> In Microsoft's isql:
> 
> 2> select c1, c2 from #t1 order by c1
> 3> go
>  c1          c2
>  ----------- --------------------
>            1  1.234567920000e+008
>            2  1.234567800000e+007
>            3  1.234567125000e+006
>            4        123456.125000
>            5         12345.123047
> 
> > Please note that the actual data is truncated.
> 
> Right.  The real datatype holds 7 digits of precision, irrespective of
> where the decimal is.  That's why "select cast(123456789.123456789 as
> real)" produces a rounded result; it also explains all of your results.
> 
> 
> cf. http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_6r3g.asp
> 
> As you can see, this isn't a library issue.  It's a simple question of
> how many bits you're using to represent your number, and there's no
> configuration option to stuff more than one bit into a bit.  ;-)
> 
> If you need more precision, I recommend float instead.  
> 
> --jkl