BUG: Negative Zero on Some Multiplications of Decimal or Numeric Values (189390)
The information in this article applies to:
This article was previously published under Q189390
BUG #: 16672 (SQLBUG_65)
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
Results:
@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
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug kbpending KB189390 |
|---|
|