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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 776696 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/16/2013 5:50:29 AM
Access Restriction Public


Results of expression calculation of decimal numbers depend on the order of additions (although no overflow nor underflow exists. See steps to reproduce, difference between Res1 and Res2

The result is also depend on adding zeroes. See steps to reproduce, difference between Res2 and Res3

Both dependencies are incorrect, should be no difference

The bug is reproduced in MSSQL 2005, MSSQL 2008R2, MSSQL 2012, all on x64 platform
Sign in to post a comment.
Posted by Umachandar [MSFT] on 1/18/2013 at 11:49 AM
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
Posted by VM-Pire on 1/18/2013 at 5:15 AM
By bad, I understand the cause now.
I agree that it is by design. And I also think that the design is bad.
Posted by VM-Pire on 1/18/2013 at 1:37 AM
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?
Posted by Umachandar [MSFT] on 1/17/2013 at 3:32 PM
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:

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;
(@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