SQL for 8-year-old Math…But Be Careful of Implicit Type Conversion

  • David Rueter

    SSCrazy

    Points: 2632

    Comments posted to this topic are about the item SQL for 8-year-old Math…But Be Careful of Implicit Type Conversion

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Nice article, David!

    I wrote about exact numerics not being exact a few years ago: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/10/17/so-called-exact-numerics-are-not-at-all-exact.aspx

    When doing math with numeric data types, there are some rules for precision and scale of the result. They are described at https://msdn.microsoft.com/en-us/library/ms190476.aspx. In a complex formula such as yours, you will have to step through the formula (in order of evaluation), to find precision and scale of the result.

    Simplified example: DECLARE @a numeric(3,1); SELECT @a / (@a + @a);

    The addition is done first, and adding numeric(3,1) to numeric(3,1) yields numeric(4,1). The division is then numeric(3,1) / numeric(4,1), resulting in numeric(9,6).

    Change @a to be numeric (17,5) and this changes - the addition now yields numeric(18,5), and the division results in numeric(40,23), which is then simpliied to numeric(38,21) (with possible loss of precision as result).

    If you do this exercise for all possible numeric datatypes, and for all the steps in your formula (and yes, that would take a week or so), you will understand why each of the data types produces the results it does.


    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/

  • RDBerks60

    SSC-Addicted

    Points: 479

    I always liked maths and this caught my eye. An interesting look into the way SQL handles number types, thank you.

    For my part I couldn't help trying to expand it and resolve the dilemma.

    Working on the fact that decimals are creeping in due to the divisions in the formula, I used the MODULO function and added two lines to the WHERE clause, to ensure whole numbers result from a division:

    AND (CAST(b.n AS int) % CAST(c.n AS int)) = 0

    AND (CAST(h.n AS int) % CAST(i.n AS int)) = 0

    This gave six results and they all work to give 66 exactly.

    This was a quick fix and there may be more solutions, possibly changing the JOIN. I would love to see how they are achieved.

  • jclemens 24860

    SSC Rookie

    Points: 25

    One small detail of terminology. An infinitely repeating decimal is NOT irrational. A non-repeating, infinite decimal is irrational.

  • Andy Reilly

    Hall of Fame

    Points: 3677

    I just added

    AND 13 * b.n / c.n = CAST( 13 * b.n / c.n AS int)

    AND g.n * h.n / i.n = CAST(g.n * h.n / i.n AS int)

    this gave me 20 answers

  • Lee Fisher

    SSC Veteran

    Points: 290

    Another way to deal with the conversion issue is to exclude it since decimal values are not allowed in the solution. By using Modulo and excluding the invalid decimals the list of possible answers drops to 170.

    WHERE a.n + 13 * b.n / c.n + d.n + 12 * e.n - f.n - 11 + g.n * h.n / i.n - 10 = @R

    AND (a.n + 13 * b.n) % c.n = 0

    AND (a.n + 13 * b.n / c.n + d.n + 12 * e.n - f.n - 11 + g.n * h.n ) % i.n = 0

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    That was fun. Thanks.

  • jennym

    Old Hand

    Points: 367

    Thanks for the very clean example giving a basic understanding of the potential impact of type conversions. Enjoyed it!

  • Luis Cazares

    SSC Guru

    Points: 183587

    I would have loved if you referenced Data Type Precedence in the article to understand SQL Server decisions on implicit conversions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • paul.hunt

    SSC Rookie

    Points: 48

    I agree here. You shouldn't exclude the 16 solutions with repeating decimals.

    They are all cases where denominator is 3 e.g 1/3+2/3 = 1 exactly so obviously 0.333'+0.666'=1 exactly.

    On a related similar note you can prove that 0.999'=1.0 exactly. The above is 1 example.

  • RDBerks60

    SSC-Addicted

    Points: 479

    The original question was using each number just once, so 1/3 + 2/3 = 1, though correct, is not applicable here as it won't happen.

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Rusxs (7/15/2015)


    The original question was using each number just once, so 1/3 + 2/3 = 1, though correct, is not applicable here as it won't happen.

    1/3 + 4/6


    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/

  • paul.hunt

    SSC Rookie

    Points: 48

    thanks Hugo, the 1/3+2/3 was an example of the mathematics not the problem. Clumsy I know but fuel for the pedants!

Viewing 13 posts - 1 through 13 (of 13 total)

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