BUG: Aggregate of an Expression May Return the Incorrect Scale (237973)


The information in this article applies to:

  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q237973
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 that this is a problem in the Microsoft products that are listed at the beginning of this article.
Modification Type:MajorLast Reviewed:11/17/2003
Keywords:kbBug kbpending KB237973
©2003 Microsoft Corporation. All rights reserved.