CODEWITHSUNDEEP
sql-serverlinqdecimalrounding
Mikey Mouse
Mikey Mouse

Reputation: 3098

Linq to SQL rounding of decimal after division

I've come across a precision issue when calculating the ration between two units of measures.

The Ratio is stored in a SQL Server database in a UnitOfMeasureTable as: NumberOfDefaultUnits decimal(28,18)

In my linqPad example I have a class to demonstate what is going wrong. 

 private class Test
 {
   public decimal saleUom { get; set; }
   public decimal piUom { get; set; }

   public decimal RatioRight { get; set; }
   public decimal RatioWrong { get; set; }
   public decimal CalcledAlsoRight { get { return saleUom / piUom; } }
}

void Main() {

var xx = from uom in UnitsOfMeasures.Where(d=> d.Id == 9)
        let buyUom =  uom.NumberOfDefaultUnits
        let sellUom = UnitsOfMeasures.First(d=> d.Id == 13).NumberOfDefaultUnits
        select new Test

{
    saleUom = sellUom,
    piUom = buyUom,
    RatioRight = sellUom / (buyUom * 1m),
    RatioWrong = sellUom / buyUom,
};

xx.First().Dump();

}

The results are:

saleUom 453.592370000000000000 
piUom 1000000.000000000000000000 
RatioRight 0.000453592370000000000 
RatioWrong 0.0004535923 
CalcledAlsoRight 0.00045359237

It took a while to figure out you have to multiply the divisor by 1m to get the correct result. It gets even weirder if you multiply the sellUom by 1m. Then the result is: 

RatioRight = (sellUom * 1m) / (buyUom)
RatioRight 0.000453

I'm guessing this is something to do with how SQL Server stores the Decimal(28,18) and how Linq converts the divide command.

Update: All values are Decimals enter image description here

Update 2: Looks like this is entirely a SQL rounding thing. Removing the .Net from the equation

 select top 1 uom.NumberOfDefaultUnits
      from UnitOfMeasures uom
      where uom.Id = 13

 select (select top 1 uom.NumberOfDefaultUnits
      from UnitOfMeasures uom
      where uom.Id = 13 ) 
    / 
      (select top 1 uom.NumberOfDefaultUnits
      from UnitOfMeasures uom
      where uom.Id = 9)

The first query returns: 453.592370000000000000

The second: 0.0004535923

Upvotes: 1

Views: 497

Answers (2)

Steve Ford
Steve Ford

Reputation: 7753

This is definitely due to how SQL Server handles precision & scale in calculations.

If you change @mikeymouses example to use a scale of 6 for @p1 you get consistent results:

DECLARE @p2 Decimal(28,6) = 1000000
DECLARE @p3 Decimal(28,18) = 453.59237

 select @p3 / @p2

 DECLARE @p1 Decimal(19,18) = 1  -- This is how Linq sends the * 1m

 select @p3 / (@p2 *@p1)
 select (@p3 *@p1) / @p2

Results:

0.0004535923700000000000

0.00045359237000

0.00045359237000000000

The precision and scale outcomes are documented on MSDN, but the key points are: For Multiplication:

  • precision of result = p1 + p2 + 1
  • Scale of result = s1 + s2

For Division:

  • Precision of result = p1 - s1 + s2 + max(6, s1 + p2 + 1)
  • Scale of result = max(6, s1 + p2 + 1)

Where p1 and p2 are the precision of the operands and s1, s2 are the scale of the operands.

It should be remembered that the result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated. I think this is what is happening here.

Upvotes: 3

Mikey Mouse
Mikey Mouse

Reputation: 3098

It looks like this is just how SQL deals with these declared decimal sizes being divided by eachother

 DECLARE @p2 Decimal(28,18) = 1000000
 DECLARE @p3 Decimal(28,18) = 453.59237

 select @p3 / @p2

 DECLARE @p1 Decimal(19,18) = 1  -- This is how Linq sends the * 1m

 select @p3 / (@p2 *@p1)
 select (@p3 *@p1) / @p2

Results:

0.0004535923

0.000453

0.00045359

Upvotes: 1

Related Questions