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

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

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

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

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

  • 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

  • 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

  • That was fun. Thanks.

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

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

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

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

  • 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