主页 / dba / 问题 / 6900
Accepted
Nick Chammas
Asked: 2011-10-18 13:13:48 +0800 CST

为什么“SELECT POWER(10.0, 38.0);” 抛出算术溢出错误？

  • 772

我正在更新我的IDENTITY溢出检查脚本以说明DECIMAL和NUMERIC IDENTITY列。

作为检查的一部分，我计算每一IDENTITY列的数据类型范围的大小；我用它来计算该范围的百分比已用尽。对于该范围的大小，DECIMAL精度在哪里。NUMERIC 2 * 10^p - 2p

我创建了一堆带有DECIMAL和NUMERIC IDENTITY列的测试表，并尝试按如下方式计算它们的范围：

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

这引发了以下错误：

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

我将它缩小到IDENTITY类型的列DECIMAL(38, 0)（即具有最大精度），所以我然后POWER()直接尝试对该值进行计算。

以下所有查询

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

也导致了同样的错误。

  • 为什么 SQL Server 会尝试将POWER()类型为 的输出转换FLOAT为NUMERIC（尤其是在FLOAT具有更高优先级时）？
  • 如何为所有可能的精度（当然包括）动态计算 aDECIMAL或列的范围？NUMERICp = 38
sql-server sql-server-2008

3 个回答

  1. 与其进一步干涉马丁的回答，我将在POWER()这里添加我的其余发现。

    抓住你的短裤。

    前言

    首先，我向您展示展览 A，即 MSDN 文档POWER()：

    句法

    POWER ( float_expression , y )

    论据

    float_expression 是浮点类型或可隐式转换为浮点类型的表达式。

    返回类型

    与 相同float_expression。

    POWER()您可以通过阅读返回类型为的最后一行得出结论FLOAT，但请再次阅读。float_expression是“浮点型或可以隐式转换为浮点型的类型”。所以，尽管它的名字，float_expression实际上可能是 a FLOAT、 aDECIMAL或 an INT。由于 的输出与 的输出POWER()相同float_expression，它也可能是其中一种类型。

    所以我们有一个标量函数，其返回类型取决于输入。可以吗？

    观察

    我向您展示了展览 B，该测试展示了POWER()根据其输入将其输出转换为不同数据类型的测试。

    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;

    相关结果是：

    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

    似乎正在发生的事情是POWER()转换float_expression成适合它的最小类型，不包括BIGINT.

    因此，SELECT POWER(10.0, 38);失败并出现溢出错误，因为10.0被强制转换NUMERIC(38, 1)为不足以容纳 10 38的结果。那是因为 10 38扩展为小数点前 39 位，而NUMERIC(38, 1)可以存储小数点前 37 位加上小数点后 1 位。因此，可以保持的最大值NUMERIC(38, 1)为 10 37 - 0.1。

    有了这种理解，我可以编造另一个溢出失败，如下所示。

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

    10 亿（与第一个示例中的 1 万亿相反，它被转换为NUMERIC(38, 0)）只是小到可以放入INT. 然而，10 亿的三次方对于 来说太大了INT，因此会出现溢出错误。

    其他几个函数表现出类似的行为，它们的输出类型取决于它们的输入：

    • 数学函数: POWER(), CEILING(), FLOOR(), RADIANS(), DEGREES(), 和ABS()
    • 系统函数和表达式：NULLIF(), ISNULL(), COALESCE(), IIF(), CHOOSE(), 和CASE表达式
    • 算术运算符：例如，当变量属于指定数据类型时，两者SELECT 2 * @MAX_INT;都会SELECT @MAX_SMALLINT + @MAX_SMALLINT;导致算术溢出。

    结论

    在这种特殊情况下，解决方案是使用SELECT POWER(1e1, precision).... 这将适用于所有可能的精度，因为1e1被强制转换为FLOAT，它可以容纳非常大的数字。

    由于这些函数非常常见，因此了解您的结果可能会被四舍五入或由于它们的行为可能会导致溢出错误，这一点很重要。如果您期望或依赖特定数据类型的输出，请根据需要显式转换相关输入。

    所以孩子们，既然你知道了这一点，你就可以前进并繁荣昌盛。

    • 24
  2. Best Answer

    从POWER文档中：

    句法

    POWER ( float_expression , y )

    论据

    float_expression
    是float类型或可以隐式转换为float的类型的表达式。

    y
    是提高float_expression的幂。y可以是精确数值或近似数值数据类型类别的表达式，位数据类型除外。

    返回类型

    返回与float_expression中提交的类型相同的类型。例如，如果将小数(2,0) 作为 float_expression 提交，则返回的结果为小数(2,0)。

    float如有必要，第一个输入将被隐式转换为。

    float内部计算由标准 C 运行时库 (CRT) 函数使用算术执行pow。

    然后将float输出pow转换回左侧操作数的类型（暗示numeric(3,1)当您使用文字值 10.0 时）。

    在您的情况下使用显式float工作正常：

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

    10 38的精确结果不能存储在 SQL Server 中decimal/numeric，因为它需要 39 位精度（1 后跟 38 个零）。最大精度为 38。

    • 19

  3. 我在更新我自己的身份溢出检查脚本时调查溢出错误时遇到了这篇文章:)

    Martin Smith 在这里接受的答案和他在https://stackoverflow.com/a/5663463/1508467上的另一个答案提供了技术信息，但我可以为问题的第二部分添加另一种方法 - 如何创建最小值/最大值十进制列的值。

    使用字符串REPLICATE函数的精度和比例来构建一个 9 的字符串，然后转换为合适的小数。

    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

    对于十进制/数字标识列，比例必须为零，因此可以进一步简化上述内容。

    • 1

相关问题