Precision, Scale, Sum, Divide.. Truncation

问题:

I have the following code:

SELECT -701385.10 -- -701385.10
SELECT SUM(-701385.10) -- -701385.10
SELECT -701385.10/2889991754.89 -- -0.000242694498630
SELECT SUM(-701385.10)/2889991754.89 -- -0.000242

In the last SELECT the result is truncated to 6 decimal places. I've read through the Precision, Scale, and Length article and unless my working is wrong, I can't understand why the truncation is occurring. The type of the expression SUM(-701385.10) should be DECIMAL(38,2) - see SUM - so the type resulting from the division should have:

Precision:

• p1 - s1 + s2 + max(6, s1 + p2 + 1)
• 38 - 2 + 2 + max(6, 2 + 10 + 1)
• 38 - max(6,13)
• 38 - 13
• 25

Scale:

• max(6, s1 + p2 + 1)
• max(6, 2 + 10 + 1)
• max(6, 13)
• 13

So why are the decimal places being truncated?

回答1:

Your working is wrong

Precision: p1 - s1 + s2 + max(6, s1 + p2 + 1)
Scale: max(6, s1 + p2 + 1)

Gives

Precision: 38 - 2 + 2 + max(6, 2 + 12 + 1) = 53
Scale: max(6, 2 + 12 + 1)                  = 15

Which is greater than 38 so you are getting truncation as covered here