Why is SQL Server changing operation order and boxing the way it does?

Question

Four simple SELECT statements:

SELECT 33883.50 * -1;
SELECT 33883.50 / -1.05;
SELECT 33883.50 * -1 / 1.05;
SELECT (33883.50 * -1) / 1.05;

But the results are not as I would expect:

-33883.50
-32270.000000
-32269.96773000
-32270.000000

That third result is the one that seems questionable. I can see what is happening, first SQL Server evaluates this:

SELECT -1 / 1.05;

Getting an answer of:

-0.952380

Then it takes that answer and uses it to perform this calculation:

SELECT 33883.50 * -0.952380;

To get the (wrong) answer of:

-32269.96773000

But why is it doing this?

Answer 1

In your example

33883.50 * -1 / 1.05

is evaluated as

33883.50 * (-1 / 1.05) 

instead of

(33883.50 * -1) / 1.05

which results in a loss in precision.

I played a bit with it. I used SQL Sentry Plan Explorer to see the details of how SQL Server evaluates expressions. For example,

2 * 3 * -4 * 5 * 6 

is evaluated as

((2)*(3)) * ( -((4)*(5))*(6))

I'd explain it like this. In T-SQL unary minus is made to be the same priority as subtraction, which is lower than multiplication. Yes,

When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression.

, but here we have an expression that mixes operators with different priorities and parser follows these priorities to the letter. Multiplication has to go first, so it evaluates 4 * 5 * 6 at first and then applies unary minus to the result.

Normally (say in C++) unary minus has higher priority (like bitwise NOT) and such expressions are parsed and evaluated as expected. They should have made unary minus/plus same highest priority as bitwise NOT in T-SQL, but they didn't and this is the result. So, it is not a bug, but a bad design decision. It is even documented, though quite obscurely.

When you refer to Oracle - that the same example works differently in Oracle than in SQL Server:

• Oracle may have different rules for operator precedence than SQL Server. All it takes is to make unary minus highest priority as it should.
• Oracle may have different rules for determining result precision and scale when evaluating expressions with decimal type.
• Oracle may have different rules for rounding intermediate results. SQL Server "uses rounding when converting a number to a decimal or numeric value with a lower precision and scale".
• Oracle may be using completely different types for these kind of expressions, not decimal. In SQL Server "a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3."
• Even definition of decimal may be different in Oracle. Even in SQL Server "the default maximum precision of numeric and decimal data types is 38. In earlier versions of SQL Server, the default maximum is 28."