Skip to Content.
Sympa Menu

freetds - Re: [freetds] Truncated data for real data type in Sybase.

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Truncated data for real data type in Sybase.
  • Date: Tue, 22 Nov 2005 22:18:57 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page