October 18, 2011 at 4:47 am
SQLRNNR (10/18/2011)
CoolCodeShare (10/18/2011)
Guys,do you think for the examples given in this question, min(s1+s2,37-(p1+p2)) is the reduction policy as Tom suggests.
SQLRNNR can you clarify Tom's point?
That is the policy that Tom suggests it should be - but that is not.
But even that is not right solution.
DECLARE @a DECIMAL(38,20)
DECLARE @b-2 DECIMAL(38,20)
SET @a=1.0
SET @b-2=12345.1234567
SELECT @a*@b
Why the product's scale is 6? it is not min(8,37-(2+12)).
October 18, 2011 at 4:53 am
CoolCodeShare (10/18/2011)
SQLRNNR (10/18/2011)
CoolCodeShare (10/18/2011)
Guys,do you think for the examples given in this question, min(s1+s2,37-(p1+p2)) is the reduction policy as Tom suggests.
SQLRNNR can you clarify Tom's point?
That is the policy that Tom suggests it should be - but that is not.
But even that is not right solution.
DECLARE @a DECIMAL(38,20)DECLARE @b-2 DECIMAL(38,20)
SET @a=1.0
SET @b-2=12345.1234567
SELECT @a*@b
Why the product's scale is 6? it is not min(8,37-(2+12)).
Where are you getting 8,2 and 12?
Also, Tom suggested that formula be used - but that formula is obviously not being used.
Read the link I provided for a little exercise in calculating precision and scale.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 18, 2011 at 5:13 am
very good question !!!
October 18, 2011 at 5:18 am
L' Eomot Inversé (10/18/2011)
Good interesting question.Just shows what bizarre and ridiculous results you can get because of the decision not to attempt accuracy with "exact" numerics.
Try it with DECLARE @a decimal(38,33) (or decimal(38,1), for that matter)
DECLARE @b-2 decimal(38,33)
and you will get a different result.
It's quite thoroughly broken. If the calculation of scale for multiplication were sensible
[new scale = min(s1+s2,37-(p1+p2)) is the easy option, based on exactly the same scale reduction policy as was chosen for addition and subtraction] it would be less thoroughly broken and this particular query would have a different (more useful) answer.
Of course with DECLARE @a float(25) (or any legal float definition for that matter) and DECLARE @b-2 float(25) (or float(53), or anything in between) you get 12345.123456699999224, which is an error of about only 7 parts in a quadrillion, roughly 11.2 million times as accurate as the result using decimal(38,20). So for this calculation using ordinary 8 byte floats gives a vastly nore accurate result than using 17 byte "exact numerics" unless you juggle your scale very carefully. Perhaps that will make some of those who shriek "never use float - it's approximate" think again - or perhaps not. Using Float(24) (4-byte floats) for @b-2 would of course bring in a much bigger error, but anyone who expects 12 significant (ecimal) digits out of a 23 bit significand can't do simple arithmetic.
I wonder when SQL will catch up with IEEE 754-2008 and offer decimal based (instead of binary based) floating point so that we can have the flexibility and accuracy and error detection and reporting associated with IEE 754 while avoiding the input and output rounding errors caused by converting into binary-based floating point. I expect it will take a long time, as SQL standardisation is a notoriously slow and clumsy process (at least it was in the early 90s, and I would be very surprised if it's any different now).
I agree with you Tom!!!
October 18, 2011 at 5:21 am
October 18, 2011 at 5:44 am
Good question, thanks for sutmitting. I got it right but wasn't exactly sure why the result was going to be different. Learned something today.
http://brittcluff.blogspot.com/
October 18, 2011 at 5:47 am
Since I'm totally lost and still don't get it after reading this entire thread, I'm just going to stay away from the decimal type for the rest of my career. Deal? 😀
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
October 18, 2011 at 5:51 am
Thanks to OP for the question. And also to the others for their interesting comments. That's why I hang around the QotD so much - I tend to learn the most right here.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
October 18, 2011 at 6:14 am
Okay, I've played with some numbers, and read... and the only thing that seems consistent is SQLRNNR's blog where he says that the minimum scale is 6. Anyone know if there are rules or some formula, or if the minimum scale is always 6?
October 18, 2011 at 7:02 am
Very interesting. Thanks!
Dan B
October 18, 2011 at 9:02 am
SQLRNNR (10/18/2011)
CoolCodeShare (10/18/2011)
Guys,do you think for the examples given in this question, min(s1+s2,37-(p1+p2)) is the reduction policy as Tom suggests.
SQLRNNR can you clarify Tom's point?
That is the policy that Tom suggests it should be - but that is not.
Not only is not - also should not be. My brain was having brief malfunction when I wrote that formula. I think I meant to write s1+s2+min(0,37-(p1+p2)). But now it's after taking wife out to special lunch for her birthday and the after-effects (of the alcohol) is detracting from the mathematics.
edit: now that I've recovered from lunch: the simple formula that avoids the silly anomalies is min(S1+S2,p1+p2+1-(ceiling(log(A1))+ceiling(log(A2)))) where A 1, A2 are the two operands of the multiplication.
This of course is not quite as good as doing the exact multiplication and taking the log of the result, but it allows exact multiplication to be avoided (which is the only imaginable excuse for the current sorry nonsense), and maybe it's worth having some avoidable loss of significance on rare occassions just to avoid the exact arithmetic.
Tom
October 18, 2011 at 9:08 am
Rich Weissler (10/18/2011)
Okay, I've played with some numbers, and read... and the only thing that seems consistent is SQLRNNR's blog where he says that the minimum scale is 6. Anyone know if there are rules or some formula, or if the minimum scale is always 6?
If the source precisions add up to 37 or less, the scale is the sum of the source scales; otherwise it is 6. At least that's what MS says in BoL. It is not what actually happens, which is maybe slightly less stupid than what MS says happens, but I don't know the detail of what actually happens (but it seems to be at best only slightly less stupid that what MS says happens, judging by results of multiplications).
Tom
October 18, 2011 at 9:12 am
Wow. It makes sense now, but it took about 30 minutes of study for it to sink in (sure, it seems simple now, but I'm slow). I thought reals were trouble, now I need to start thinking about decimal issues too. Sigh... Thanks guys!
October 18, 2011 at 9:47 am
Great question - often misunderstood detail - caused me considerable grief when i first started doing SQL development.
Thanks Tom for your contributions also.
Cheers!
October 18, 2011 at 9:53 am
Good question and very interesting discussion, thanks.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply