asked
Calculation result and datatype
hello experts - I came across behaviour I don't understand today. I was calculating some values from a temporary table where my source value was stored as an integer. There was an error in the calculation which was spotted and corrected, but the correction had no effect. I then altered the datatype of the source value to numeric and found that the issue was resolved. This is illustrated in the example below; the calculation was missing parentheses around the '9*(Num)' part of the calculation, but adding it did not change the calculation result. It was only when the datatype of the source column was changed that the result was corrected, i.e. the result named Calc_with_parentheses in the second query below is correct. Can anyone explain please why adding the parentheses to the calculation did not correct the issue and why changing the datatype was required? or, would there have been a better solution? Thanks for taking the time to read, Mickey CREATE TABLE #test1 ( ID INT, Num INT ) INSERT INTO #test1 VALUES ( 1, 100 ) INSERT INTO #test1 VALUES ( 2, 150 ) INSERT INTO #test1 VALUES ( 3, 200 ) INSERT INTO #test1 VALUES ( 4, 250 ) INSERT INTO #test1 VALUES ( 5, 300 ) SELECT * , Num * POWER(( 1 - ( 1 / 9 * ( Num ) ) - ( 1.959964 / ( 3 * SQRT(Num) ) ) ), 3) AS Calc_missing_parentheses , Num * POWER(( 1 - ( 1 / ( 9 * ( Num ) ) ) - ( 1.959964 / ( 3 * SQRT(Num) ) ) ), 3) AS Calc_with_parentheses FROM #test1 ALTER TABLE #test1 ALTER COLUMN num NUMERIC(10,6) SELECT * , Num * POWER(( 1 - ( 1 / 9 * ( Num ) ) - ( 1.959964 / ( 3 * SQRT(Num) ) ) ), 3) AS Calc_missing_parentheses , Num * POWER(( 1 - ( 1 / ( 9 * ( Num ) ) ) - ( 1.959964 / ( 3 * SQRT(Num) ) ) ), 3) AS Calc_with_parentheses FROM #test1
There are 2 things at play here. The first is datatypes, the second is operator precedence. Take the example when Num is an Integer and the value is 100. The part of the formula where this makes a difference is 1 / 9 * (Num) when Num (int) = 100, then this is 1 / 9 * 100 T-SQL operator precedence gives the same level to both multiplication and division, so they are evaluated left-to-right, meaning that this could be written as (1 / 9) * 100 1 and 9 are integers, so the result of integer division is 0 0 * 100 = 0 even if you add parentheses, it's still integer division 1 / (9 * (Num)) = 1 / (9 * 100) = 1 / 900 = 0 Now if you change the datatype to numeric(10,6) then the Num =100 becomes 100.000000. Without parentheses this is now 1 / 9 * 100.000000 which according to operator precedence rules is evaluated like (1 / 9 ) * 100.000000 there is still integer division, so becomes 0 * 100.000000 = 0.000000 only when you add parentheses here do you get what you want 1 / ( 9 * 100.000000) = 1 / 900.000000 = 0.0011111111111 and the rest of the formula is affected......
17 People are following this question.
