Despois da publicación sobre as características de escribir en PostgreSQL, o primeiro comentario foi sobre as dificultades de traballar con números reais. Decidín botarlle unha ollada rápida ao código das consultas SQL dispoñibles para min para ver cantas veces usan o tipo REAL. Acontece que úsase con bastante frecuencia e os desenvolvedores non sempre entenden os perigos que hai detrás. E iso a pesar de que hai bastantes artigos bos en Internet e en Habré sobre as características de almacenar números reais na memoria do ordenador e sobre o traballo con eles. Polo tanto, neste artigo tratarei de aplicar este tipo de funcións a PostgreSQL e intentarei darlle unha rápida ollada aos problemas asociados con elas, para que sexa máis doado para os desenvolvedores de consultas SQL evitalas.
A documentación de PostgreSQL di de forma sucinta: "A xestión de tales erros e a súa propagación durante o cálculo é o tema de toda unha rama das matemáticas e da informática, e non se trata aquí" (mentres remite sabiamente ao lector ao estándar IEEE 754). Que tipo de erros quere dicir aquí? Comentemos por orde, e pronto quedará claro por que retomei a pluma.
Poñamos por exemplo unha solicitude sinxela:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
Como resultado, non veremos nada especial: obteremos o esperado 0.1. Pero agora comparámolo con 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
Non é igual! Que milagres! Pero máis aló, máis. Alguén dirá: Sei que o REAL se comporta mal coas fraccións, así que introducirei alí números enteiros e definitivamente todo estará ben con elas. Ok, imos enviar o número 123 a REAL:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
E resultaron ser 3 máis! Iso é todo, a base de datos por fin esqueceu como contar! Ou estamos mal entendendo algo? Imos descubrir.
En primeiro lugar, lembremos o material. Como sabes, calquera número decimal pódese expandir a potencias de dez. Así, o número 123.456 será igual a 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Pero o ordenador opera con números en forma binaria, polo tanto, teñen que ser representados en forma de expansión en potencias de dous. Polo tanto, o número 5.625 en binario represéntase como 101.101 e será igual a 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. E se as potencias positivas de dous sempre dan números decimais enteiros (1, 2, 4, 8, 16, etc.), entón coas negativas todo é máis complicado (0.5, 0.25, 0.125, 0,0625, etc.). O problema é que Non todos os decimales se poden representar como unha fracción binaria finita. Así, o noso notorio 0.1 en forma de fracción binaria aparece como o valor periódico 0.0 (0011). En consecuencia, o valor final deste número na memoria do ordenador variará dependendo da profundidade de bits.
Agora é o momento de lembrar como se almacenan os números reais na memoria do ordenador. En xeral, un número real consta de tres partes principais: signo, mantisa e expoñente. O signo pode ser máis ou menos, polo que se lle asigna un bit. Pero o número de bits da mantisa e do expoñente está determinado polo tipo real. Polo tanto, para o tipo REAL, a lonxitude da mantisa é de 23 bits (un bit igual a 1 engádese implícitamente ao comezo da mantisa, e o resultado é 24), e o expoñente é de 8 bits. O total é de 32 bits ou 4 bytes. E para o tipo DOBLE PRECISIÓN, a lonxitude da mantisa será de 52 bits, e o expoñente será de 11 bits, para un total de 64 bits, ou 8 bytes. PostgreSQL non admite unha maior precisión para os números de coma flotante.
Empaquetemos o noso número decimal 0.1 en tipos REAL e DOBRE PRECISIÓN. Dado que o signo e o valor do expoñente son o mesmo, centrarémonos na mantisa (omitiré deliberadamente as características non obvias de almacenar os valores do expoñente e os valores reais cero, xa que complican a comprensión e distraen da esencia). do problema, se está interesado, consulte o estándar IEEE 754). Que conseguiremos? Na liña superior darei a "mantissa" para o tipo REAL (tendo en conta o redondeo do último bit por 1 ao número representable máis próximo, se non, será 0.099999...), e na liña inferior - para tipo DOBLE PRECISIÓN:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Obviamente estes son dous números completamente diferentes! Polo tanto, ao comparar, o primeiro número encherase con ceros e, polo tanto, será maior que o segundo (tendo en conta o redondeo: o marcado en negra). Isto explica a ambigüidade dos nosos exemplos. No segundo exemplo, o número 0.1 especificado explícitamente lánzase ao tipo DOUBLE PRECISION e, a continuación, compárase cun número do tipo REAL. Ambos están reducidos ao mesmo tipo, e temos exactamente o que vemos arriba. Modifiquemos a consulta para que todo quede no seu lugar:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
E efectivamente, ao realizar unha dobre redución do número 0.1 a REAL e DOBRE PRECISIÓN, obtemos a resposta ao enigma:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
Isto tamén explica o terceiro exemplo anterior. O número 123 é sinxelo é imposible encaixar a mantisa en 24 bits (23 explícito + 1 implícito). O número enteiro máximo que pode caber en 24 bits é 224-1 = 16. Polo tanto, o noso número 777 arredondase ao 215 representable máis próximo. Ao cambiar o tipo a DOBLE PRECISIÓN, xa non vemos este escenario:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
Iso é todo. Resulta que non hai milagres. Pero todo o descrito é unha boa razón para pensar canto necesitas realmente o tipo REAL. Quizais a maior vantaxe do seu uso sexa a velocidade dos cálculos cunha perda coñecida de precisión. Pero sería este un escenario universal que xustificaría un uso tan frecuente deste tipo? Non penses.
Fonte: www.habr.com