July 13, 2015 at 10:09 pm
Comments posted to this topic are about the item SQL for 8-year-old Math…But Be Careful of Implicit Type Conversion
July 14, 2015 at 3:06 am
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.
July 14, 2015 at 3:35 am
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.
July 14, 2015 at 4:37 am
One small detail of terminology. An infinitely repeating decimal is NOT irrational. A non-repeating, infinite decimal is irrational.
July 14, 2015 at 6:09 am
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
July 14, 2015 at 7:08 am
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
July 14, 2015 at 7:25 am
That was fun. Thanks.
July 14, 2015 at 9:39 am
Thanks for the very clean example giving a basic understanding of the potential impact of type conversions. Enjoyed it!
July 14, 2015 at 9:41 am
I would have loved if you referenced Data Type Precedence in the article to understand SQL Server decisions on implicit conversions.
July 14, 2015 at 6:00 pm
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.
July 15, 2015 at 1:53 am
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.
July 15, 2015 at 12:55 pm
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
July 15, 2015 at 4:55 pm
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply