November 24, 2007 at 9:12 am
Hey Jeff,
I just tried both examples (in 2005, not 2000) and everything matched up perfectly to the hard-coded values. Why is that?
November 24, 2007 at 9:16 am
I wonder if MS made the mistake of doing the rounding on the new display? What does the code for the problem come up with for an answer on the following?
[font="Courier New"]DECLARE @MATij FLOAT(53)
DECLARE @MATpj FLOAT(53)
DECLARE @m FLOAT(53)
SET @MATij = 2.77555756156289E-17
SET @MATpj = 15
SET @m = 1.85037170770859E-18
SELECT @MATij + (@MatPJ * -@m)
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2007 at 9:20 am
5.23852944873328E-32
November 24, 2007 at 10:41 am
It's doing the Binary math as 2k does... can you try one more time? Do this in the text mode, please... thanks, Dave.
[font="Courier New"]DECLARE @MATij FLOAT(53)
DECLARE @MATpj FLOAT(53)
DECLARE @m FLOAT(53)
DECLARE @Result FLOAT(53)
SET @MATij = 2.77555756156289e-17
SET @MATpj = 15
SET @m = 1.85037170770859e-18
SELECT 'Entered',
'2.77555756156289e-17' AS '@Matij',
'15' AS '@MATpj',
'1.85037170770859e-18' AS '@m'
SELECT 'Actual',@MATij,@MATpj,@m[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2007 at 10:44 am
Had already done that.
No difference.
November 24, 2007 at 11:32 am
Dang... not good... hides the "problem".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2007 at 11:31 am
Yes... I agree... in DECIMAL math, you would be absolutely correct. But FLOAT does not use Decimal math... it uses BINARY math... and there are many Decimal numbers that cannot be exactly represented by the scale of the registers in Binary.
So let me switch into BINARY mode. Things do start to get interesting. Let's look at these three numbers:
[font="Courier New"]1 0011 1100 0111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1100 +2.77555756156289E-17
2 1011 1100 0111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1110 1011 -2.77555756156288E-17
3 0011 1001 0111 0001 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 +5.23852944873328E-32
..SEEE EEEE EEEE MANTISSA................................................MANTISSA[/font]
Most double precision numbers use the first bit for the sign bit, the next 11 bits for the exponent (to base 2 biased by 1023) and the next 52 bits for the mantissa (with an implied 1 before the decimal place which thus gives you a total of 53 bits of precision). See http://en.wikipedia.org/wiki/Double_precision
First of all, I find it very unusal for the OP (Max) to have found the first two operands whose mantissas consist of almost all ones. It's pretty much like winning the lottery. Or is this a setup for a trick question?
The 3rd number lends itself to a quick exercise to see if it's correct (i.e. binary value equals decimal value). On the binary side the exponent is hex 397 which equals decimal 919 (using the handy Windows calculator). Thus we do 2^(919-1023) which equals 4.9303806576313237838233035330174e-32. The mantissa is 1/16 plus the implied 1 giving me 1.0625. Multiply the two and you get 5.238529448733281520312260003831e-32. That's close enough for me.
Now if I were a computer and if I had to deal with the first two numbers I would do simple subtraction. Because I'm doing subtraction, we can ignore the sign bit of the second operand. And because the exponents are the same we simply ignore them for the moment (i.e. exp*(a-b) where we are concentrating on a-b). When you now substract the second mantissa from the first mantissa you (and the computer) should get 51 zeroes followed by a 1. Let's ignore for the moment the fact that we have clobbered the implied 1 in the mantissa.
We bring back the exponent of the first two operands, hex 3c7 or decimal 967, we do 2^(967-1023) * 1/(2^52) which gets me 3.0814879110195773648895647081359e-33. This represents less than 1/10th of the value returned by VB or SQL Server. My BINARY mode must be doing something wrong.
But I'm a perfectionist. Let's try to convert this into a number where the mantissa has an implied value of 1 before the decimal place (in our subtraction, that implied mantissa disappeared, i.e. it became zero). The best way to do this is to simply left shift that bit 52 times. For every left shift (the equivalent of a multiplication by 2) we compensate by subtracting 1 from the exponent (the equivalent of dividing by 2). Of course, this will not change my final value. I now have 2^(915-1023) * 1 - which is the same.
Thus my answer should be
0011 1001 0011 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 3.0814879110195773648895647081359e-33
In fact, when I burn those bits into a double word and display the result I get this value.
That difference of 4 in the exponent and that 1 bit in the 4th position of the VB result has me wondering. Is it a coincidence?
November 26, 2007 at 1:29 am
Hi Michael,
what do you mean with
First of all, I find it very unusal for the OP (Max) to have found the first two operands whose mantissas consist of almost all ones. It's pretty much like winning the lottery. Or is this a setup for a trick question?
could you kindly elaborate?
my english is so so, i would not misunderstand your post.
it seems that my problem has raise new problem: does MS SQL server manages correctly small numbers?
am i correct? do i understand well all of your posts?
unfortunately i need such a precision.
i knew vb6 is not the right language to perform math operation, but it seems that SQL server has other problems! c++ would be better but i cannot use it.
November 26, 2007 at 3:21 am
Max,
No offense. It was my idea of a bad joke.
Nevertheless, having a mantissa with all those 1 bits is still a bit unusual.
In the meantime, I also discovered a small error in my analysis. I guess I was going a bit bit-blind and missed out on a bit in the second operand.
Thus the result of the difference should be 47 zeroes followed by 10001. In fact this actually confirms the suspicion I expressed at end. Thus the result (5.238529448733281520312260003831e-32) produced by VB is correct.
The VB code I used to get this result is
Dim dblD1 As Double
Dim dblD2 As Double
Dim dblD3 As Double
dblD1 = 2.77555756156289E-17
dblD2 = -1.85037170770859E-18 * 15
dblD3 = dblD1 + dblD2
Debug.Print dblD3
results in 5.23852944873328E-32
I decided to run this SQL Server code
declare @d1 float(53)
declare @d2 float(53)
declare @d float(53)
set @d1 = 2.77555756156289E-17
set @d2 = -1.85037170770859E-18 * 15
set @d = @d1 + @d2
select @d
which results in
5.2385294487332815E-32
I guess I should have trusted Ramesh's and Jeff's earlier responses.
Nevertheless, I would go easy on the meaning of a number like 5.23852944873328E-32 representing the difference of two very small numbers expressed to a very high level of precision and sitting on the edge of being practically equal.
November 26, 2007 at 4:57 am
No problem Michael. i didn't mean you offense me.
I really appreciate the effort you all puts into this problem
I just want to understand what you all are trying to tell me!
numbers will be small and perhaps smaller that the numbers i used to log the problem.
ok ok.. why numbers are so small? we are talking about micron. the math operation is part of a routine to compute a linear estimation. the vb code was wrote from an excel workbook where the function LinEst where used.
What is the problem in your opinion?
Do you think that we must expect a kind of constant error when numbers becomes too small?
It will be possible to compute the error and use it as a constant?
Regards,
Max
November 26, 2007 at 5:46 am
Max,
First of all, I would be curious to know if you can finally get our answer in VB or Excel or SQL Server.
Regarding my philosophical statement on the interpretation of the result, it simply means you are 'pushing the envelope' as to its precision. Look at my earlier post that shows what's going on in decimal form. The two operands differ only in the last digit. It's like saying 2 - 1 = 0.516149225095779. Very precise, but who knows what that means.
It's not a matter of the dimension of the number. It is a matter of not having enough digits with which to represent your numbers. You should be looking for 128bit software/hardware for dealing with your numbers. Once you have enough bits you need to make sure that your two operands use them. Thus you would then be doing the equivalent of
2.52382281 - 1.55789013 = 0.96593268. Now that feels meaningful.
November 26, 2007 at 8:03 am
Hi Michael,
VB and Excel returns the same value.
I'm currently coding T-SQL again to see where the problem lies.
in my modest opinion if it's a os/hw problem i should not have this problem as vb, excel and sql server runs in the same environment (32 bit)
regards,
max
November 26, 2007 at 8:12 am
Hi Michael,
VB and Excel returns the same value.
I'm currently coding T-SQL again to see where the problem lies.
Be specific, please... what value is that?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2007 at 8:23 am
Guys, this is driving me crazy!
T-SQL (all of the variables are FLOAT(53))
SELECT @Tmp = measureData FROM dbo.New_AMatrix WHERE x = @i AND y = @j-2
SELECT @tmp2 = measureData FROM dbo.New_AMatrix WHERE x = @P AND y = @j-2
SET @NewValue = @Tmp + ( @tmp2 * -@m )
-@m = -3.96508e-018
@Tmp = 2.77556e-017
@tmp2 = 7
@NewValue = 0
VB6 (all of the variables are Array as Double or Double)
Matrix(i, j) = Matrix(i, j) + (Matrix(P, j) * -m)
Pivot = 7
-m = -3.96508223080413E-18
Matrix(i, j) = 2.77555756156289E-17
Matrix(P, j) = 7
Matrix(i, j) = 1.54074395550979E-33
Regards,
Max
November 26, 2007 at 9:30 am
The values are NOT the same! Look at what you have for "m"... not the same... none of them are the same...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply