Exact and Approximate

  • Paul White

    SSC Guru

    Points: 150442

    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

    SSC Guru

    Points: 150442

    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!

  • jts2013

    Hall of Fame

    Points: 3226

    Luckily I had had my coffee first! 😀

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • Cliff Jones

    SSChampion

    Points: 10517

    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.

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • Revenant

    SSC-Forever

    Points: 42467

    A good question and an non-intuitive answer. Thanks!

  • steven.malone

    Say Hey Kid

    Points: 688

    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.

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3994

    Thanks Paul - great question.

  • SQLRNNR

    SSC Guru

    Points: 281243

    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 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

  • steven.malone

    Say Hey Kid

    Points: 688

    [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.

  • Ken Wymore

    SSCoach

    Points: 16611

    Nice QOD. Thanks!

  • Frank Hamersley

    SSC-Addicted

    Points: 450

    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 56 total)

You must be logged in to reply to this topic. Login to reply