Disasterous Decimal Debacle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Disasterous Decimal Debacle

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-31 : 18:49:22

I'm doing a table of light speed variations. Here's my code:

drop table [dbo].[SOL_CONSTANTS]
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists
(select * from dbo.sysobjects
where id = object_id(N'[dbo].[SOL_CONSTANTS]')
and xtype in (N'FN', N'IF', N'TF'))
drop table [dbo].[SOL_CONSTANTS]
GO

create table [dbo].[SOL_CONSTANTS] (PCT_SOL float, DIVISOR float, GAMMA float)
go
declare @PCT_SOL decimal (4,2)
set @PCT_SOL = 1.00
while @PCT_SOL < 10
begin
INSERT SOL_CONSTANTS values (@PCT_SOL, SQRT(1 - (SQUARE(@PCT_SOL)/10000)),1 /SQRT(1 -(SQUARE(@PCT_SOL/100))))
SET @PCT_SOL = @PCT_SOL+.10
end
GO

SELECT * FROM [dbo].[SOL_CONSTANTS]
go

-------and here's are my results (they don't seperate well in the preview)
PCT_SOL DIVISOR GAMMA
----------------------- --------------------- ----------------------
1.0 0.99994999874993751 1.0000500037503126
1.1000000000000001 0.99993949816976424 1.0000605054909286
1.2 0.99992799740781335 1.0000720077769332
1.3 0.99991549642957334 1.0000845107118836
1.3999999999999999 0.99990199519752931 1.0000980144083533

the problem is that 1.100000000000001 and 1.3999999999999 ad nauseum in PCT_SOL should read 1.0 and 1.4 respectively.

I've tried CASTing CONVERTing and ROUNDing. Nothing works.

Would you please show me where I erred and how to fix this?

Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-31 : 18:55:24

That's the problem with using floats. Try decimal(18,2) or however many decimal places you want to see.

Tara Kizer

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-31 : 19:15:24

quote:
Originally posted by tkizer

That's the problem with using floats. Try decimal(18,2) or however many decimal places you want to see.

Tara Kizer

Goddess,

I'm confused. I already established the variable at declaration. Why doesn't it retain that form?

BTW, I changed it from float to decimal (4,2) in the insert command and it hasn't finished processing for the past 11 minutes!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-08-31 : 19:17:39

quote:
I'm confused. I already established the variable at declaration. Why doesn't it retain that form

You're inserting the data into a column declared as float.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-31 : 19:32:26

Rob,

I already applied the change and now this thing runs like its stuck in a loop....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-08-31 : 20:00:55

%$#!#$%^ I must learn to read some day.

Try running it without using a loop, just copy and paste about 5 insert lines and run them. Then try 10. See if that works. If it does, put the loop back but try 1 iteration, then 5, then 10, etc. Don't use the @PCT_SQL variable in your WHILE condition, use another counter with an int datatype.

I have a theory that the WHILE should be written as while @PCT_SOL < 10.00 or while @PCT_SOL < cast(10.00 as decimal(4,2)), and the incrementer as SET @PCT_SOL = @PCT_SOL+cast(0.10 as decimal(4,2)). I've often seen screwy behavior when mixing precise and imprecise types, and SQL has its own rules about how it converts and casts values in the absence of explicit instructions. My guess is that .10 is somehow rounding down to zero, or some other value below the precision of 4,2.

You can test this by using print @PCT_SOL inside the loop, but make sure to use a different counter mechanism as I described earlier.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-31 : 20:08:28

It is really a display issue when you use a float. The problem is that float is internally a binary representation of the number, and the binary representation cannot always be converted directly to a decimal representation that is exactly equivalent. Float is intended for scientific applications where computational accuracy is the most important requirement.

Check this out:


select	n = 1, f=convert(float,1.4) union all
select	2, convert(float,0.0000000000000001)  union all
select	3, convert(float,1.4)+convert(float,0.0000000000000001)  union all
select	4, convert(float,1.4)+convert(float,0.0000000000000002)

Results:


n           f                                                     
----------- ----------------------------------------------------- 
1           1.3999999999999999
2           9.9999999999999998E-17
3           1.3999999999999999
4           1.4000000000000001

(4 row(s) affected)

CODO ERGO SUM

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-31 : 20:31:40

quote:
Originally posted by Michael Valentine Jones
Float is intended for scientific applications where computational accuracy is the most important requirement.

?

-ec

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-31 : 20:36:43

quote:
Originally posted by Michael Valentine Jones

It is really a display issue when you use a float. The problem is that float is internally a binary representation of the number, and the binary representation cannot always be converted directly to a decimal representation that is exactly equivalent. Float is intended for scientific applications where computational accuracy is the most important requirement.

Check this out:

select n = 1, f=convert(float,1.4) union all
select 2, convert(float,0.0000000000000001) union all
select 3, convert(float,1.4)+convert(float,0.0000000000000001) union all
select 4, convert(float,1.4)+convert(float,0.0000000000000002)

Results:

n f
----------- -----------------------------------------------------
1 1.3999999999999999
2 9.9999999999999998E-17
3 1.3999999999999999
4 1.4000000000000001

(4 row(s) affected)

CODO ERGO SUM

Colonel, I am totally blown away! WOW!

Thanks for the harrowing explanation!

This is gonna make me re-think every single calculation I make! YIKES!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-31 : 22:04:13

quote:
Originally posted by eyechart

quote:
Originally posted by Michael Valentine Jones
Float is intended for scientific applications where computational accuracy is the most important requirement.

?

-ec

Notice that the float stays very close to the correct power of 31.1 after repeated calculations, while the decimal diverges more from the correct value with each additional calculation.


declare @s decimal(30,20)
declare @f float
declare @d decimal(30,20)

select @s = 31.1
select @d = sqrt(@s)
select @f  = sqrt(@s)

select @s	[s] union all
select @s*@s	union all
select @s*@s*@s 	union all
select @s*@s*@s*@s	union all
select @s*@s*@s*@s*@s

select @f*@f	[float] union all
select @f*@f*@f*@f	union all
select @f*@f*@f*@f*@f*@f	union all
select @f*@f*@f*@f*@f*@f*@f*@f	union all
select @f*@f*@f*@f*@f*@f*@f*@f*@f*@f

select @d*@d	[decimal] union all
select @d*@d*@d*@d	union all
select @d*@d*@d*@d*@d*@d	union all
select @d*@d*@d*@d*@d*@d*@d*@d	union all
select @d*@d*@d*@d*@d*@d*@d*@d*@d*@d

Results:


s                                        
---------------------------------------- 
31.100000
967.210000
30080.231000
935495.184100
29093900.225510

(5 row(s) affected)

float                                                 
----------------------------------------------------- 
31.100000000000005
967.21000000000026
30080.231000000014
935495.18410000054
29093900.225510024

(5 row(s) affected)

decimal                                  
---------------------------------------- 
31.100000
967.210000
30080.230999
935495.184070
29093900.224579

(5 row(s) affected)