Decimal raised to power produces wrong answer

Does anyone know of a fix or patch for SQL 2000 for the crazy results
produced by the following script ...
- or alternately is this just a PRINT problem?
DECLARE @.decimalParameter DECIMAL(38,0)
DECLARE @.decimalPower DECIMAL
DECLARE @.decimalBase DECIMAL
SET @.decimalBase = 10
SET @.decimalPower = 1
WHILE (@.decimalPower < 38)
BEGIN
SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
PRINT @.decimalParameter
SET @.decimalPower = @.decimalPower + 1
END
The results are (clearly a problem):
10
100
1000
10000
100000
1000000
10000000
100000000
1000000000
10000000000
100000000000
1000000000000
10000000000000
100000000000000
1000000000000000
10000000000000000
100000000000000000
1000000000000000000
10000000000000000000
100000000000000000000
1000000000000000000000
10000000000000000000000
100000000000000010000000
999999999999999980000000
10000000000000001000000000
100000000000000000000000000
1000000000000000000000000000
9999999999999999600000000000
100000000000000010000000000000
1000000000000000000000000000000
9999999999999999600000000000000
100000000000000010000000000000000
999999999999999950000000000000000
9999999999999999500000000000000000
99999999999999997000000000000000000
1000000000000000000000000000000000000
9999999999999999500000000000000000000Wozza,
This isn't a print problem. POWER is giving wrong answers.
I reported this a couple of years ago - see this thread for some discussion:
http://groups.google.co.uk/groups?q...6923828125+kass
So while this is a known bug, unfortunately, it has not
"made the bar" for importance and it's possible it will
not be fixed.
If this bug is causing a serious impact to a production
environment for you, it would be helpful to know and
see a repro or summary of the situation. Perhaps there
is a workaround (using bigint in POWER gives exact results, for
example, up to that type's limit, and one can create slow but
accurate UDFs to do what POWER does), and if not, it
might serve to increase the perceived importance of this bug.
As far as I know, when the answers are wrong, the first 15 or 16
places of precision are always correct, since internally, it appears
that the float time is being used. Here's another example:
select power(cast(1.1 as decimal(25,18)),5), 1.1*1.1*1.1*1.1*1.1
Steve Kass
Drew University
Wozza wrote:

>Does anyone know of a fix or patch for SQL 2000 for the crazy results
>produced by the following script ...
>- or alternately is this just a PRINT problem?
>DECLARE @.decimalParameter DECIMAL(38,0)
>DECLARE @.decimalPower DECIMAL
>DECLARE @.decimalBase DECIMAL
>SET @.decimalBase = 10
>SET @.decimalPower = 1
>WHILE (@.decimalPower < 38)
>BEGIN
> SET @.decimalParameter = POWER(@.decimalBase, @.decimalPower)
> PRINT @.decimalParameter
> SET @.decimalPower = @.decimalPower + 1
>END
>The results are (clearly a problem):
>10
>100
>1000
>10000
>100000
>1000000
>10000000
>100000000
>1000000000
>10000000000
>100000000000
>1000000000000
>10000000000000
>100000000000000
>1000000000000000
>10000000000000000
>100000000000000000
>1000000000000000000
>10000000000000000000
>100000000000000000000
>1000000000000000000000
>10000000000000000000000
>100000000000000010000000
>999999999999999980000000
>10000000000000001000000000
>100000000000000000000000000
>1000000000000000000000000000
>9999999999999999600000000000
>100000000000000010000000000000
>1000000000000000000000000000000
>9999999999999999600000000000000
>100000000000000010000000000000000
>999999999999999950000000000000000
>9999999999999999500000000000000000
>99999999999999997000000000000000000
>1000000000000000000000000000000000000
>9999999999999999500000000000000000000
>
>|||Thanks Steve,
I was basically just wanting to confirm whether there was a patch or not.
The problem occurred in a unit test that I am writing which includes all
datatypes in SQL. Luckily we are not actually using DECIMAL in production (o
r
dev in this case), I just included it for completeness.
Thanks anyway.
Warren
"Steve Kass" wrote:

> Wozza,
> This isn't a print problem. POWER is giving wrong answers.
> I reported this a couple of years ago - see this thread for some discussio
n:
> http://groups.google.co.uk/groups?q...6923828125+kass
> So while this is a known bug, unfortunately, it has not
> "made the bar" for importance and it's possible it will
> not be fixed.
> If this bug is causing a serious impact to a production
> environment for you, it would be helpful to know and
> see a repro or summary of the situation. Perhaps there
> is a workaround (using bigint in POWER gives exact results, for
> example, up to that type's limit, and one can create slow but
> accurate UDFs to do what POWER does), and if not, it
> might serve to increase the perceived importance of this bug.
> As far as I know, when the answers are wrong, the first 15 or 16
> places of precision are always correct, since internally, it appears
> that the float time is being used. Here's another example:
> select power(cast(1.1 as decimal(25,18)),5), 1.1*1.1*1.1*1.1*1.1
> Steve Kass
> Drew University
>
> Wozza wrote:
>
>