Após publicação sobre as funcionalidades de digitação no PostgreSQL, o primeiro comentário foi sobre as dificuldades de trabalhar com números reais. Decidi dar uma olhada rápida no código das consultas SQL disponíveis para ver com que frequência elas usam o tipo REAL. Acontece que ele é usado com bastante frequência e os desenvolvedores nem sempre entendem os perigos por trás dele. E isso apesar de existirem muitos artigos bons na Internet e no Habré sobre as características de armazenar números reais na memória do computador e sobre como trabalhar com eles. Portanto, neste artigo tentarei aplicar esses recursos ao PostgreSQL e tentarei dar uma olhada rápida nos problemas associados a eles, para que seja mais fácil para os desenvolvedores de consultas SQL evitá-los.
A documentação do PostgreSQL afirma sucintamente: “O gerenciamento de tais erros e sua propagação durante a computação é o assunto de todo um ramo da matemática e da ciência da computação, e não é abordado aqui” (embora remeta sabiamente o leitor ao padrão IEEE 754). Que tipo de erros se referem aqui? Vamos discuti-los em ordem e logo ficará claro por que peguei a caneta novamente.
Tomemos como exemplo uma solicitação simples:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
Como resultado, não veremos nada de especial – obteremos o esperado 0.1. Mas agora vamos compará-lo com 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
Não igual! Que milagres! Mas além disso, mais. Alguém dirá, eu sei que REAL se comporta mal com frações, então vou inserir números inteiros lá, e com certeza tudo ficará bem com eles. Ok, vamos converter o número 123 em REAL:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
E acabou sendo mais 3! É isso, o banco de dados finalmente esqueceu como contar! Ou estamos entendendo mal alguma coisa? Vamos descobrir.
Primeiro, vamos lembrar o material. Como você sabe, qualquer número decimal pode ser expandido em potências de dez. Portanto, o número 123.456 será igual a 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Mas o computador opera com números na forma binária, portanto eles devem ser representados na forma de expansão em potências de dois. Portanto, o número 5.625 em binário é representado como 101.101 e será igual a 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. E se potências positivas de dois sempre dão números decimais inteiros (1, 2, 4, 8, 16, etc.), então com os negativos tudo é mais complicado (0.5, 0.25, 0.125, 0,0625, etc.). O problema é que Nem todo decimal pode ser representado como uma fração binária finita. Assim, nosso notório 0.1 na forma de fração binária aparece como o valor periódico 0.0(0011). Consequentemente, o valor final deste número na memória do computador irá variar dependendo da profundidade de bits.
Agora é a hora de lembrar como os números reais são armazenados na memória do computador. De modo geral, um número real consiste em três partes principais – sinal, mantissa e expoente. O sinal pode ser positivo ou negativo, então um bit é alocado para ele. Mas o número de bits da mantissa e do expoente é determinado pelo tipo real. Assim, para o tipo REAL, o comprimento da mantissa é de 23 bits (um bit igual a 1 é adicionado implicitamente ao início da mantissa e o resultado é 24), e o expoente é de 8 bits. O total é de 32 bits ou 4 bytes. E para o tipo DOUBLE PRECISION, o comprimento da mantissa será de 52 bits, e o expoente será de 11 bits, totalizando 64 bits, ou 8 bytes. O PostgreSQL não suporta maior precisão para números de ponto flutuante.
Vamos agrupar nosso número decimal 0.1 nos tipos REAL e DOUBLE PRECISION. Como o sinal e o valor do expoente são iguais, vamos nos concentrar na mantissa (omiti deliberadamente as características não óbvias de armazenar os valores do expoente e zero valores reais, pois complicam a compreensão e desviam a atenção da essência do problema, caso tenha interesse consulte o padrão IEEE 754). O que vamos conseguir? Na linha superior darei a “mantissa” para o tipo REAL (levando em conta o arredondamento do último bit por 1 para o número representável mais próximo, caso contrário será 0.099999...), e na linha inferior - para o tipo DUPLA PRECISÃO:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Obviamente estes são dois números completamente diferentes! Portanto, na comparação, o primeiro número será preenchido com zeros e, portanto, será maior que o segundo (levando em conta o arredondamento - aquele marcado em negrito). Isso explica a ambigüidade de nossos exemplos. No segundo exemplo, o número 0.1 especificado explicitamente é convertido no tipo DOUBLE PRECISION e depois comparado com um número do tipo REAL. Ambos são reduzidos ao mesmo tipo e temos exatamente o que vemos acima. Vamos modificar a consulta para que tudo se encaixe:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
E, de fato, realizando uma dupla redução do número 0.1 para REAL e DUPLA PRECISÃO, obtemos a resposta para o enigma:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
Isso também explica o terceiro exemplo acima. O número 123 é simples é impossível encaixar a mantissa em 24 bits (23 explícitos + 1 implícito). O número inteiro máximo que pode caber em 24 bits é 224-1 = 16. Portanto, nosso número 777 é arredondado para o representável mais próximo 215. Ao alterar o tipo para DOUBLE PRECISION, não vemos mais este cenário:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
Isso é tudo. Acontece que não existem milagres. Mas tudo o que foi descrito é um bom motivo para pensar no quanto você realmente precisa do tipo REAL. Talvez a maior vantagem de seu uso seja a rapidez dos cálculos com conhecida perda de precisão. Mas seria este um cenário universal que justificaria o uso tão frequente deste tipo? Não pense.
Fonte: habr.com