Two t-SQL (MS SQL 2017) queries:
Select 125. / -124/ 125.
Select 125. / (-124)/125.
Difference only on parentheses.
But, results are not equal:
-126.0080645161
-0.0080645120
Why the results are different?
Asked
Active
Viewed 89 times
1
Suraj Kumar
- 5,547
- 8
- 20
- 42
Tom Kev
- 146
- 10
-
2See also https://stackoverflow.com/questions/54513450/a-strange-operation-problem-in-sql-server-100-10010-0. – Dávid Laczkó Feb 07 '20 at 08:44
2 Answers
4
The addition of the parentheses in the second case is changing the order of operations. To avoid any ambiguity, you should specify parentheses explicitly. For the first result, you should use:
SELECT 125. / (-124 / 125.)
and for the second case:
SELECT (125. / -124) / 125.
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
-
Amen. Know the precedence rules. Write code that is clear and easily understood. Even if not needed, the parentheses make clear the intent. – SMor Feb 07 '20 at 11:56
3
The explanation is that in T-SQL, the * (Multiplication) and / (Division) operators have higher precedence than the - (Negative) operator. So what is exactly evaluated is:
Expession 1: 125. / - (124 / 125) , equals to : -126.0080645161
Expession 2: 125. / (-124) / 125 , equals to : -0.0080645120
Note, that the correct result is: -0.0080645120.
Zhorov
- 28,486
- 6
- 27
- 52
-
The operator is unary negative https://learn.microsoft.com/en-us/sql/t-sql/language-elements/unary-operators-negative?view=sql-server-ver15 – Martin Smith Feb 07 '20 at 08:26