Why does "SELECT POWER(10.0, 38.0);" throw an arithmetic overflow error?

Question

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.

As part of the check I compute the size of the data type's range for every IDENTITY column; I use that to calculate what percentage of that range has been exhausted. For DECIMAL and NUMERIC the size of that range is 2 * 10^p - 2 where p is the precision.

I created a bunch of test tables with DECIMAL and NUMERIC IDENTITY columns and attempted to calculate their ranges as follows:

SELECT POWER(10.0, precision)
FROM sys.columns
WHERE 
       is_identity = 1
   AND type_is_decimal_or_numeric
;

This threw the following error:

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric. 

I narrowed it down to the IDENTITY columns of type DECIMAL(38, 0) (i.e. with the maximum precision), so I then tried the POWER() calculation directly on that value.

All of the following queries

SELECT POWER(10.0, 38.0);
SELECT CONVERT(FLOAT, (POWER(10.0, 38.0)));
SELECT CAST(POWER(10.0, 38.0) AS FLOAT);

also resulted in the same error.

• Why does SQL Server try to convert the output of POWER(), which is of type FLOAT, to NUMERIC (especially when FLOAT has a higher precedence)?
• How can I dynamically calculate the range of a DECIMAL or NUMERIC column for all possible precisions (including p = 38, of course)?

Answer 1

From the POWER documentation:

Syntax

POWER ( float_expression , y )

Arguments

float_expression
Is an expression of type float or of a type that can be implicitly converted to float.

y
Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).

---

The first input is implicitly cast to float if necessary.

The internal calculation is performed using float arithmetic by the standard C Runtime Library (CRT) function pow.

The float output from pow is then cast back to the type of the left hand operand (implied to be numeric(3,1) when you use the literal value 10.0).

Using an explicit float works fine in your case:

SELECT POWER(1e1, 38);
SELECT POWER(CAST(10 as float), 38.0);

An exact result for 10³⁸ cannot be stored in a SQL Server decimal/numeric because it would require 39 digits of precision (1 followed by 38 zeros). The maximum precision is 38.

Answer 2

Instead of meddling with Martin's answer any further, I'll add the rest of my findings regarding POWER() here.

Hold on to your knickers.

Preamble

First, I present to you exhibit A, the MSDN documentation for POWER():

Syntax

POWER ( float_expression , y )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

Same as float_expression.

You may conclude from reading that last line that POWER()'s return type is FLOAT, but read again. float_expression is "of type float or of a type that can be implicitly converted to float". So, despite its name, float_expression may actually be a FLOAT, a DECIMAL, or an INT. Since the output of POWER() is the same as that of float_expression, it too may also be one of those types.

So we have a scalar function with return types that depend on the input. Could it be?

Observations

I present to you exhibit B, a test demonstrating that POWER() casts its output to different data types depending on its input.

SELECT 
    POWER(10, 3)             AS int
  , POWER(1000000000000, 3)  AS numeric0     -- one trillion
  , POWER(10.0, 3)           AS numeric1
  , POWER(10.12305, 3)       AS numeric5
  , POWER(1e1, 3)            AS float
INTO power_test;

EXECUTE sp_help power_test;

DROP TABLE power_test;

The relevant results are:

Column_name    Type      Length    Prec     Scale
-------------------------------------------------
int            int       4         10       0
numeric0       numeric   17        38       0
numeric1       numeric   17        38       1
numeric5       numeric   17        38       5
float          float     8         53       NULL

What appears to be happening is that POWER() casts float_expression into the smallest type that fits it, not including BIGINT.

Therefore, SELECT POWER(10.0, 38); fails with an overflow error because 10.0 gets cast to NUMERIC(38, 1) which isn't big enough to hold the result of 10³⁸. That's because 10³⁸ expands to take 39 digits before the decimal, whereas NUMERIC(38, 1) can store 37 digits before the decimal plus one after it. Therefore, the maximum value NUMERIC(38, 1) can hold is 10³⁷ - 0.1.

Armed with this understanding I can concoct another overflow failure as follows.

SELECT POWER(1000000000, 3);    -- one billion

One billion (as opposed to the one trillion from the first example, which is cast to NUMERIC(38, 0)) is just small enough to fit in an INT. One billion raised to the third power, however, is too big for INT, hence the overflow error.

Several other functions exhibit similar behavior, where their output type is dependent on their input:

• Mathematical functions: POWER(), CEILING(), FLOOR(), RADIANS(), DEGREES(), and ABS()
• System functions and expressions: NULLIF(), ISNULL(), COALESCE(), IIF(), CHOOSE(), and CASE expressions
• Arithmetic operators: Both SELECT 2 * @MAX_INT; and SELECT @MAX_SMALLINT + @MAX_SMALLINT;, for example, result in arithmetic overflows when the variables are of the named data type.

Conclusion

In this particular case, the solution is to use SELECT POWER(1e1, precision).... This will work for all possible precisions since 1e1 gets cast to FLOAT, which can hold ridiculously large numbers.

Since these functions are so commonplace, it's important to understand that your results may be rounded or may cause overflow errors due to their behavior. If you expect or rely on a specific data type for your output, explicitly cast the relevant input as necessary.

So kids, now that you know this, you may go forth and prosper.

Answer 3

I came across this post whilst investigating overflow errors whilst updating my own identity overflow check script :)

Martin Smith's accepted answer here and another answer of his at https://stackoverflow.com/a/5663463/1508467 provide the technical info but I can add an alternative approach for the second part of the question - how to create the min/max value for a decimal column.

Use the precision and scale with the string REPLICATE function to build a string of 9s then cast to a suitable decimal.

use tempdb
go

create table dbo.TestTable (
    col1 decimal(38, 0)    
)

select
    cast(replicate('9', c.precision - c.scale) + '.' + replicate('9', c.scale) as decimal(38,0)) as MaxValue,
    cast('-' + replicate('9', c.precision - c.scale) + '.' + replicate('9', c.scale) as decimal(38,0)) as MinValue
from
    sys.columns c
where
    object_id = object_id('dbo.TestTable')

drop table dbo.TestTable 

For decimal/numeric identity columns the scale must be zero so the above can be simplified further.