January 3, 2012 at 7:15 am
L' Eomot Inversé (1/3/2012)
It is a good question. Nicely shoots down the myth that these fixed point "exact" numerics have fewer (or less serious) rounding issues than floating point approximate numerics.
Thank you!
L' Eomot Inversé (1/3/2012)
I would have thought that if anyone ran it they would notice the difference between 570 and 567 as the last three digits of the result, so it hardly seems a test of reading skills.
That's perhaps true, but it did catch Hugo, so I do have a small regret about not using an example that left no room at all for misreading.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 7:15 am
Diogy (1/3/2012)
Ohh come on!! I'm wrong again. I'm starting to think this is not meant for common people. LOL! But i'm learning much so thanks. 😉
:laugh: You're welcome!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 7:27 am
Luckily I had had my coffee first! 😀
January 3, 2012 at 7:51 am
L' Eomot Inversé (1/3/2012)
I would have thought that if anyone ran it they would notice the difference between 570 and 567 as the last three digits of the result, so it hardly seems a test of reading skills.
On my system, the results displayed ended in 57 (not 570) and 567 - both in grid and text mode.
But that's not what tricked me. I ran the code after replying. I had it wrong for the simple reason that I forgot to check the precision and scale of the result for the decimal, even though I am VERY much aware of this issue (and have, in fact, explained this on several online forums, inclding this one, a multitude of times already!).
If I had bothered to REALLY look at the resutls (instead of casually glancing them) after running the code, I would immediately have understood where my error was. But I didn't, I was already so convinced that Paul mistook the displayed result of the real value for the actual result that I neither checked the results with appropriate attention to detail, nor read the explanation accurately enough.
Sometimes, I have an idea stuck in my head so tightly that I fail to open my mind to other possibilities. Today was such a day.
January 3, 2012 at 8:20 am
Hugo Kornelis (1/3/2012)
Sometimes, I have an idea stuck in my head so tightly that I fail to open my mind to other possibilities. Today was such a day.
It happens to all of us - and to me more often than I like to admit.
Tom
January 3, 2012 at 8:49 am
This is a good lesson. Microsoft's warnings about the use of approximate data types have been known to trip some of us up on occasion.
January 3, 2012 at 9:02 am
L' Eomot Inversé (1/3/2012)
Hugo Kornelis (1/3/2012)
Sometimes, I have an idea stuck in my head so tightly that I fail to open my mind to other possibilities. Today was such a day.It happens to all of us - and to me more often than I like to admit.
And me.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 9:23 am
A good question and an non-intuitive answer. Thanks!
January 3, 2012 at 9:35 am
Very interesting.
Declaring the decimal variables as DECIMAL(35,20) gives the correct result.
But declaring the decimal variables as DECIMAL(35,19) gives the rounded result.
It looks like BOL is wrong in Precision, Scale, and Length (Transact-SQL).
From the testing I can do at work, the formulas make more sense if p is the integer precision rather than the total precision.
So the code in the question has integer precision of 18 (38 - 20) so SQL wants the result to have integer precision of 37, which only leaves one place to the right of the decimal point so the "at least 6 to the right rule kicks in.
Multiplying two DECIMAL(35,20)s which have integer precision of 15 requires integer precision of 31 (15+15+1) which leaves 7 digits to the right of the decimal point.
While DECIMAL(35,19) has integer precision of 16 and the product would need 33, again triggering truncation.
The actual moral of the story is to only use the integer precision you will need.
January 3, 2012 at 9:49 am
steven.malone (1/3/2012)
Very interesting.Declaring the decimal variables as DECIMAL(35,20) gives the correct result.
But declaring the decimal variables as DECIMAL(35,19) gives the rounded result.
Two DEC(35,20) multiplied gives a result of (71,40). Precision 71 exceeds the available 38 by 33, so scale is reduced by 33 to 7. Result is DEC(38,7) and the result is correct.
Two DEC(35,19) multiplied gives a result of (71,38). Precision 71 exceeds the available 38 by 33, so scale is reduced by 33 to 5. However, minimum scale is 6, so the result is DEC(38,6) with a risk of very large values causing an error, and the result is rounded to 6 decimal places.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 9:50 am
Thanks Paul - great question.
January 3, 2012 at 10:01 am
SQL Kiwi (1/3/2012)
steven.malone (1/3/2012)
Very interesting.Declaring the decimal variables as DECIMAL(35,20) gives the correct result.
But declaring the decimal variables as DECIMAL(35,19) gives the rounded result.
Two DEC(35,20) multiplied gives a result of (71,40). Precision 71 exceeds the available 38 by 33, so scale is reduced by 33 to 7. Result is DEC(38,7) and the result is correct.
Two DEC(35,19) multiplied gives a result of (71,38). Precision 71 exceeds the available 38 by 33, so scale is reduced by 33 to 5. However, minimum scale is 6, so the result is DEC(38,6) with a risk of very large values causing an error, and the result is rounded to 6 decimal places.
Good explanation Paul. If @steven-3 is interested in reading more on it, I wrote this article[/url] a few moons ago.
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
January 3, 2012 at 11:49 am
[font="Courier New"]SELECT COFFEE FROM PROGRAMER.CUP[/font]
Msg 7342, Level 16, State1, Line 1
An unexpected NULL value was returned...
Programmer cannot continue.
It's dangerous to hit reply the first day back before getting enough coffee.
BOL (and you) are correct. It is however a bit like C code: simple and elegant but the logic is not always readily apparent.
January 3, 2012 at 12:31 pm
Nice QOD. Thanks!
January 4, 2012 at 5:25 am
How weird stuff like this survives in a commercial offering beggars belief (well mine if no-one elses)!
My implicit expectation - formed in earlier days (aka Fortran IV and CDC ASM albeit with some gray cells "preserved" by vitamin B) - that whilst decimal precision would not neccesarily be increased by multiplication ... it would _never_ be diminished.
Whomever the bright sparks were that decided an arbitary 6 digit minimum is to be forced on the resultant without any other consideration except adding up the number of significant digits on the input side....I am still gobsmacked that anyone would think this is viable. Slack in my book.
I guess I am living in earlier days where the computation unit used accumulators that greatly exceeded the accuracy of the inputs for precisely this reason - and this has not been encoded in SQL Server. That said I accept no apologists - as how hard would it be to lock in the decimals and trap any digits overflow occuring (in the significant digits)?
</rant>
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply