Decimal type expression result depend on operands order and on added zeroes by VM-Pire

The precision/scale for decimal arithmetic is adjusted based on the inputs' precision/scale and the maximum supported precision/scale. We use some hueristics to adjust the precision and scale. This may cause some unexpected behavior.

We do have a request tracking the implementation of the IEEE 754r specification which will provide better decimal arithmetic and higher precision/scale. Hopefully, we can implement that in some future version of SQL Server to address some of these deficiencies.

--
Umachandar, SQL Programmability Team

By bad, I understand the cause now.
I agree that it is by design. And I also think that the design is bad.

The rules that you referring are the same for Res1 and Res2 but the results are different so it is still a bug.
The Res1 and Res2 expressions are differ only by order of adding @F and the @F = 0. How adding zero could affect the result precision?

Hello,
Thanks for your feedback. But as far as I can tell, the behavior you are seeing is by design. The precision & scale for decimal expressions are calculated based on a formula and this can affect the end result as you noticed. The formula that we use to calculate the precision / scale of a result for an arithmetic operation involving two decimal values can be found at the link below:

http://msdn.microsoft.com/en-us/library/ms190476

If you use that formula, you will find that for Res1 & Res3 the resulting precision and scale is 38,7. So this aligns with the result you are seeing. Here is a modified version of your repro that shows how to determine the resulting precision scale of any decimal expression for example:


declare @N decimal(28,6), @A decimal(28,6), @R decimal(28,6), @D decimal(28,6), @F decimal(28,6), @NA decimal(28,6);
set @N = 285000;
set @A = -3000;
set @R = 0;
set @D = 0;
set @F = 0;
set @NA = 15489000;
select
(@N + @F + (@A + @R + @D))/@NA AS Res1,
(@N + (@A + @R + @D)+ @F)/@NA AS Res2,
(@N + (@A + @R + @D))/@NA AS Res3,
(@N + @A + @R + @D)/@NA AS Res4;
declare @v1 sql_variant = (@N + @F + (@A + @R + @D))/@NA
     , @v2 sql_variant = (@N + (@A + @R + @D)+ @F)/@NA
     , @v3 sql_variant = (@N + (@A + @R + @D))/@NA
     , @v4 sql_variant = (@N + @A + @R + @D)/@NA
select SQL_VARIANT_PROPERTY(@v1, 'precision') as p1, SQL_VARIANT_PROPERTY(@v1, 'scale') as s1
     , SQL_VARIANT_PROPERTY(@v2, 'precision') as p2, SQL_VARIANT_PROPERTY(@v2, 'scale') as s2
     , SQL_VARIANT_PROPERTY(@v3, 'precision') as p3, SQL_VARIANT_PROPERTY(@v3, 'scale') as s3
     , SQL_VARIANT_PROPERTY(@v4, 'precision') as p4, SQL_VARIANT_PROPERTY(@v4, 'scale') as s4;

--
Umachandar, SQL Programmability Team