BUG: Aggregate of an Expression May Return the Incorrect Scale

ID: Q237973

The information in this article applies to:

Microsoft SQL Server versions 6.5, 7.0

BUG #: 42324 (SQLBUG_70)
BUG #: 18771 (SQLBUG_65)

SYMPTOMS

When adding two expressions e1 and e2 with scales s1 and s2 (each a decimal type) the expected result for the scale is the max(s1,s2) for the result. For example:


e1    = 10.0001     s1 = 4
e2    = 10.000001   s2 = 6  -- max 

e1+e2 = 20.000101   scale = 6

When either the SUM or AVG aggregates are used in one of the expressions, the scale of the result may be incorrect. In the case of SUM, the resulting scale is the smallest scale provided as input to the SUM function. In the case of the AVG function, the scale is a fixed value that may not reflect either the scale of e1 or e2.

WORKAROUND

Here are two workarounds that you can implement to avoid this problem:

Use the CONVERT function to convert the output of the SUM or AVG function into a different scale.

-or-

Change the column definition in the base table to reflect the desired scale and the SUM function produces the desired results.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 and SQL Server version 7.0.

Additional query words:

Keywords : kbSQLServ650bug kbSQLServ700bug
Version : winnt:6.5,7.0
Platform : winnt
Issue type : kbbug