BUG: Negative Zero on Some Multiplications of Decimal or Numeric

ID: Q189390

The information in this article applies to:

• Microsoft SQL Server version 6.5

SYMPTOMS

When performing multiplication on decimal or numeric values, it is possible to have a negative zero. It is also possible to have the sign of the result affected by parenthetical ordering.

WORKAROUND

To avoid this problem, try one of the following:

• Ensure that at least one of the operands has a scale equal to or greater than the scale of the declared variable.

  -or-

• Multiply the results by 1.0, where the unity value has the same scale as the variable.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The SELECT statements given below illustrate the behavior. The last two SELECT statements (where the values are calculated) reveal that the returned value's sign is affected by the ordering of the parentheses.

   declare
         @a decimal(13,2),
         @b decimal(13,2),
         @c decimal(13,2),
         @d decimal(13,2)    /* This is the same equation as used to
                                calculate @a but multiplied by 1.00  */ 

   select
      @a=(-0.0)*(-0.0) , @b = (-0.00) * (-0.0),
      @c = (-0.0) * (-0.00), @d = 1.00 * (-0.0)*(-0.0)
   select @a, @b, @c, @d

   select
      @a=(-1.0)*(-0.0), @b = (-1.00)*(-0.0),
      @c = (-1.0)*(-0.00), @d = 1.00 * (-1.0)*(-0.0)
   select @a, @b, @c, @d

   select
      @a= -0.0*(-0.0), @b = -0.00 *(-0.0),
      @c = -0.0 * (-0.00), @d = 1.00 * -0.0*(-0.0)
   select @a, @b, @c, @d

   select
      @a= (-0.0)*-0.0, @b = (-0.00)*-0.0,
      @c = (-0.0)*-0.00, @d = 1.00 * (-0.0)*-0.0
   select @a, @b, @c, @d

   @A                @B                @C                @D
   ----------------- ----------------- ----------------- -----------------
   0.00              0.00              0.00              0.00

   ----------------- ----------------- ----------------- -----------------
   -0.00             0.00              0.00              0.00

   ----------------- ----------------- ----------------- -----------------
   0.00              0.00              0.00              0.00

   ----------------- ----------------- ----------------- -----------------
   -0.00             0.00              0.00              0.00

Keywords          : SSrvGen kbbug6.50 
Version           : WINNT:6.5
Platform          : winnt
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: July 15, 1998