Hi,

I'm sure this is pretty simple, but why do I get "Arithmetic overflow
occurred" for this:

SELECT POWER(2,31)

but this computes without error:

DECLARE @Two NUMERIC(38,0)
SELECT @Two = 2
SELECT POWER(@Two,31)

I would think that POWER(2,31) overflows because the 2 would be
interpreted as an integer and thus the result would also be an
integer, whose maximum would be 2147483647, and POWER(2,31) equates to
2147483648. Thus by declaring @Two as a NUMERIC(38,0), the result
would be of the same datatype, and thus no overflow occurs. I find
that if @Two is declared as INT, an overflow occurs as in the first
case. So far so good.

But using that logic, I would expect POWER(@Two,8) to overflow if @Two
were defined as a TINYINT because the maximum tinyint is 255. But the
result computes without error to 256, implying that the result is not
a tinyint. POWER(@2,30) does not overflow either, while POWER(@2,31)
does overflow.

Therefore, I assume that if the value raised to a power is a TINYINT
or SMALLINT, then the result will be of datatype INT.

Am I on the right track?

Dale Kerr
Melbourne, Australia

"Dale Kerr" <dalekerrNOSPAM@lycos.com> wrote in message
news:9710df07.0308252306.48a5377c@posting.google.c om...
> Hi,
>
> I'm sure this is pretty simple, but why do I get "Arithmetic
overflow
> occurred" for this:
>
> SELECT POWER(2,31)
>
> but this computes without error:
>
> DECLARE @Two NUMERIC(38,0)
> SELECT @Two = 2
> SELECT POWER(@Two,31)
>
> I would think that POWER(2,31) overflows because the 2 would be
> interpreted as an integer and thus the result would also be an
> integer, whose maximum would be 2147483647, and POWER(2,31) equates
to
> 2147483648. Thus by declaring @Two as a NUMERIC(38,0), the result
> would be of the same datatype, and thus no overflow occurs. I find
> that if @Two is declared as INT, an overflow occurs as in the first
> case. So far so good.
>
> But using that logic, I would expect POWER(@Two,8) to overflow if
@Two
> were defined as a TINYINT because the maximum tinyint is 255. But
the
> result computes without error to 256, implying that the result is
not
> a tinyint. POWER(@2,30) does not overflow either, while POWER(@2,31)
> does overflow.
>
> Therefore, I assume that if the value raised to a power is a TINYINT
> or SMALLINT, then the result will be of datatype INT.
>
> Am I on the right track?
>
> Dale Kerr
> Melbourne, Australia

Yes, I think you're right. A literal integer is always interpreted as
'int' AFAIK.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------