Início / dba / Perguntas / 6900
Accepted
Nick Chammas
Asked: 2011-10-18 13:13:48 +0800 CST

Por que "SELECT POWER(10.0, 38.0);" lançar um erro de estouro aritmético?

  • 772

Estou atualizando meu IDENTITYscript de verificação de estouro para contabilizar DECIMALe NUMERIC IDENTITYcolunas .

Como parte da verificação, calculo o tamanho do intervalo do tipo de dados para cada IDENTITYcoluna; Eu uso isso para calcular qual porcentagem desse intervalo foi esgotada. Pois DECIMALe NUMERIC o tamanho desse intervalo é2 * 10^p - 2 onde pestá a precisão.

Criei várias tabelas de teste com colunas DECIMALe NUMERIC IDENTITYe tentei calcular seus intervalos da seguinte maneira:

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

Isso lançou o seguinte erro:

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

Eu reduzi para as IDENTITYcolunas do tipo DECIMAL(38, 0)(ou seja, com a precisão máxima), então tentei o POWER()cálculo diretamente naquele valor.

Todas as consultas a seguir

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

também resultou no mesmo erro.

  • Por que o SQL Server tenta converter a saída de POWER(), que é do tipo FLOAT, para NUMERIC(especialmente quando FLOATtem uma precedência mais alta )?
  • Como posso calcular dinamicamente o intervalo de uma coluna DECIMALou para todas as precisões possíveis (incluindo , é claro)?NUMERICp = 38
sql-server sql-server-2008

3 respostas

  1. Em vez de me intrometer mais na resposta de Martin, adicionarei o restante de minhas descobertas POWER()aqui.

    Segure-se em sua calcinha.

    Preâmbulo

    Primeiro, apresento a você o anexo A, a documentação do MSDN paraPOWER() :

    Sintaxe

    POWER ( float_expression , y )

    argumentos

    float_expression É uma expressão do tipo float ou de um tipo que pode ser convertido implicitamente em float.

    Tipos de retorno

    O mesmo que float_expression.

    Você pode concluir lendo a última linha que POWER()o tipo de retorno é FLOAT, mas leia novamente. float_expressioné "do tipo float ou de um tipo que pode ser implicitamente convertido em float". Portanto, apesar do nome, float_expressionpode ser na verdade a FLOAT, a DECIMALou an INT. Como a saída de POWER()é a mesma de float_expression, também pode ser um desses tipos.

    Portanto, temos uma função escalar com tipos de retorno que dependem da entrada. Poderia ser?

    Observações

    Eu apresento a você a demonstração B, um teste demonstrando que POWER()converte sua saída para diferentes tipos de dados, dependendo de sua entrada .

    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;

    Os resultados relevantes são:

    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

    O que parece estar acontecendo é que POWER()lança float_expressionno menor tipo que se encaixa, não incluindo BIGINT.

    Portanto, SELECT POWER(10.0, 38);falha com um erro de estouro porque 10.0é convertido para o NUMERIC(38, 1)qual não é grande o suficiente para conter o resultado de 10 38 . Isso ocorre porque 10 38 se expande para receber 39 dígitos antes do decimal, enquanto NUMERIC(38, 1)pode armazenar 37 dígitos antes do decimal mais um depois dele. Portanto, o valor máximo que NUMERIC(38, 1)pode conter é 10 37 - 0,1.

    Armado com esse entendimento, posso inventar outra falha de estouro da seguinte maneira.

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

    Um bilhão (ao contrário do trilhão do primeiro exemplo, que é convertido para NUMERIC(38, 0)) é pequeno o suficiente para caber em um INT. Um bilhão elevado à terceira potência, no entanto, é muito grande para INT, daí o erro de estouro.

    Várias outras funções exibem comportamento semelhante, onde seu tipo de saída depende de sua entrada:

    Conclusão

    Neste caso particular, a solução é usar SELECT POWER(1e1, precision).... Isso funcionará para todas as precisões possíveis, pois 1e1é convertido para FLOAT, que pode conter números ridiculamente grandes .

    Como essas funções são tão comuns, é importante entender que seus resultados podem ser arredondados ou causar erros de estouro devido ao seu comportamento. Se você espera ou depende de um tipo de dados específico para sua saída, converta explicitamente a entrada relevante conforme necessário.

    Então, crianças, agora que vocês sabem disso, podem seguir em frente e prosperar.

    • 24
  2. Best Answer

    Da POWERdocumentação :

    Sintaxe

    POWER ( float_expression , y )

    argumentos

    float_expression
    É uma expressão do tipo float ou de um tipo que pode ser implicitamente convertido em float .

    y
    É a potência para a qual elevar float_expression . y pode ser uma expressão da categoria de tipo de dados numérico exato ou numérico aproximado, exceto para o tipo de dados bit .

    Tipos de retorno

    Retorna o mesmo tipo enviado em float_expression . Por exemplo, se um decimal (2,0) for enviado como float_expression, o resultado retornado será decimal (2,0).

    A primeira entrada é convertida implicitamente, floatse necessário.

    O cálculo interno é executado usando floataritmética pela função C Runtime Library (CRT) padrão pow.

    A floatsaída de powé, então, convertida de volta para o tipo do operando esquerdo (supostamente numeric(3,1)quando você usa o valor literal 10.0).

    Usar um explícito floatfunciona bem no seu caso:

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

    Um resultado exato para 10 38 não pode ser armazenado em um SQL Server decimal/numericporque exigiria 39 dígitos de precisão (1 seguido de 38 zeros). A precisão máxima é 38.

    • 19

  3. Me deparei com esta postagem enquanto investigava erros de estouro enquanto atualizava meu próprio script de verificação de estouro de identidade :)

    A resposta aceita de Martin Smith aqui e outra resposta dele em https://stackoverflow.com/a/5663463/1508467 fornecem as informações técnicas, mas posso adicionar uma abordagem alternativa para a segunda parte da pergunta - como criar o min/max valor para uma coluna decimal.

    Use a precisão e a escala com a função REPLICATE de string para criar uma string de 9s e convertê-la em um decimal adequado.

    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

    Para colunas de identidade decimal/numérica, a escala deve ser zero para que o acima possa ser simplificado ainda mais.

    • 1

relate perguntas