2

This is a question related to both Entity Framework and SQL Server (2016).

I've noticed some strange behaviour in SQL server when summing a decimal column with a lower scale & precision, and unioning that with another decimal column with higher scale & precision. The resulting values all have the lower scale & precision.

This doesn't seem correct, as this page suggests that when UNIONing, the precision will be adjusted to be the widest necessary to accommodate it.

The problem can be easily seen with this example:

create table low_scale (val decimal(13,2))
create table high_scale (val decimal(19,8))

insert into low_scale values (10.00), (2.23)
insert into high_scale values (0.0000002), (2.02302023)

-- Query 1: Works fine - Result is at the widest precision required to accomodate the two types:
select * from low_scale
union all 
select * from high_scale

-- Query 2: Strange - result is rounded to two decimal places:
select sum(val) from low_scale
union all 
select sum(val) from high_scale

As you'd expect, the result from Query 1 is:

10.00000000
2.23000000
0.00000020
2.02302023

However, this is the result from Query 2:

12.23
2.02

It appears I can work around this by first casting the lower-precision column to a higher precision, like so:

-- Result is at the expected 8 decimal places:
select sum(CAST(val as decimal(19,8))) from low_scale
union all 
select sum(val) from high_scale

However, I'm using Entity Framework 6, and don't have much control over what SQL gets generated. Is there any way to force Entity Framework to cast to a higher precision when doing the SUM, or some other way to ensure the correct behavior?

Edit: I'm not sure why this is being flagged as a duplicate of a question about floating points. This has nothing to do with floating points - it is using decimal - a fixed-point data type.

Tom
  • 326
  • 3
  • 8
  • Possible duplicate of [Is floating point math broken?](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) – Eli Sadoff Dec 15 '16 at 21:41
  • How so? This is about fixed-point math. Nothing in here is using floating point data types, and is about loss of precision, rather than the fluff you can get when approximating with floating-point types. – Tom Dec 15 '16 at 21:50
  • 1
    Don't sweat it Tom, it takes 5 members with sufficient privileges to mark it as a duplicate, hence the "Possible" at the front. A suggestion, I'd put "Is there any way to force Entity Framework to cast to a higher precision when doing the SUM, or some other way to ensure the correct behavior?" at the top of the post since you have realized SQL behavior on working with the lowest precision. – S3S Dec 15 '16 at 22:21
  • 1
    `select sum(val) from low_scale` returns a result of datatype `numeric(38,2)` `select sum(val) from high_scale` returns a result of datatype `numeric(38,8)` If you `UNION ALL` together those two datatypes SQL Server will opt for `numeric(38,2)` – Martin Smith Dec 15 '16 at 22:23
  • Ah, so SUM will always go to the highest possible precision - 38? I suppose it has to in order to allow for potentially millions of rows being summed together? – Tom Dec 15 '16 at 22:45
  • @Tom Sorry that was my mistake. As scsimon said, that's why the system is in place for more than one person to have to approve of a closure. I retracted my vote. – Eli Sadoff Dec 15 '16 at 23:48

1 Answers1

2

Expanding on what Martin Smith mentioned in a comment, this is what is happening:

From the sql server documentation on SUM, the result of SUM on a type of decimal(p, s) is always decimal(38, s).

Thus the input to the first half of the UNION is decimal(38, 2), and the second half decimal(38, 8)

This page states that when UNIONing, the resulting precision is max(s1, s2) + max(p1-s1, p2-s2), and scale is max(s1, s2)

So, putting s1 = 2, s2 = 8, p1, p2 = 38 into that, we get a precision of 44 and scale of 8.

However that same page states 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.

So, it reduces the scale by 6 (bringing it down to 2) to bring the total precision back down to 38.

This explains the behaviour, but doesn't offer a solution. That said, there are options:

  1. Issue the two halves of the UNION as seperate queries. At this point we'll be dealing with a .Net decimal, and we can .Conact() it without having to worry about the SQL behaviour.
  2. Keep all decimal types at the same precision - but always round appropriately when storing to the DB.
  3. (nasty) - Cast the result of Sum() to a float or double before calling .Conact() or .Union(). Obviously this introduces a bunch of other issues, but in some cases it may be viable.

In my case, I'll likely opt for #2, as there will certainly be other places where these two tables will be summed & unioned, and I wouldn't expect other devleopers to recognize they will be affected by this SQL server behaviour (as, from EF's point of view, we're dealing with .Net decimals).

Tom
  • 326
  • 3
  • 8