# 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

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

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)

• 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

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)