Despues de publicacion Sobre las características de escribir en PostgreSQL, el primer comentario fue sobre las dificultades de trabajar con números reales. Decidí echar un vistazo rápido al código de las consultas SQL disponibles para ver con qué frecuencia usan el tipo REAL. Resulta que se utiliza con bastante frecuencia y los desarrolladores no siempre comprenden los peligros que conlleva. Y esto a pesar de que en Internet y en Habré hay bastantes buenos artículos sobre las posibilidades de almacenar números reales en la memoria de una computadora y cómo trabajar con ellos. Por lo tanto, en este artículo intentaré aplicar dichas características a PostgreSQL e intentaré echar un vistazo rápido a los problemas asociados con ellas, para que sea más fácil para los desarrolladores de consultas SQL evitarlas.
La documentación de PostgreSQL dice de manera sucinta: “La gestión de tales errores y su propagación durante el cálculo es el tema de toda una rama de las matemáticas y la informática, y no se trata aquí” (al tiempo que remite sabiamente al lector al estándar IEEE 754). ¿A qué tipo de errores nos referimos aquí? Discutamoslos en orden y pronto quedará claro por qué volví a tomar la pluma.
Tomemos, por ejemplo, una solicitud sencilla:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
Como resultado, no veremos nada especial: obtendremos el 0.1 esperado. Pero ahora comparémoslo con 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
¡No es igual! ¡Qué milagros! Pero más allá, más. Alguien dirá, sé que REAL se porta mal con las fracciones, así que ingresaré números enteros allí y definitivamente todo estará bien con ellos. Bien, transformemos el número 123 en REAL:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
¡Y resultaron ser 3 más! Eso es todo, ¡la base de datos finalmente ha olvidado cómo contar! ¿O estamos entendiendo mal algo? Vamos a resolverlo.
Primero, recordemos el material. Como sabes, cualquier número decimal se puede ampliar a potencias de diez. Entonces, el número 123.456 será igual a 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Pero el ordenador trabaja con números en forma binaria, por lo que hay que representarlos en forma de expansión en potencias de dos. Por tanto, el número 5.625 en binario se representa como 101.101 y será igual a 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. Y si las potencias positivas de dos siempre dan números decimales enteros (1, 2, 4, 8, 16, etc.), entonces con las negativas todo es más complicado (0.5, 0.25, 0.125, 0,0625, etc.). El problema es ese No todos los decimales se pueden representar como una fracción binaria finita. Así, nuestro famoso 0.1 en forma de fracción binaria aparece como el valor periódico 0.0(0011). En consecuencia, el valor final de este número en la memoria de la computadora variará dependiendo de la profundidad de bits.
Ahora es el momento de recordar cómo se almacenan los números reales en la memoria de la computadora. En términos generales, un número real consta de tres partes principales: signo, mantisa y exponente. El signo puede ser más o menos, por lo que se le asigna un bit. Pero el número de bits de la mantisa y el exponente está determinado por el tipo real. Entonces, para el tipo REAL, la longitud de la mantisa es de 23 bits (un bit igual a 1 se suma implícitamente al comienzo de la mantisa y el resultado es 24) y el exponente es de 8 bits. El total es 32 bits o 4 bytes. Y para el tipo DOBLE PRECISIÓN, la longitud de la mantisa será de 52 bits y el exponente será de 11 bits, para un total de 64 bits, u 8 bytes. PostgreSQL no admite una mayor precisión para números de punto flotante.
Empaquemos nuestro número decimal 0.1 en tipos de PRECISIÓN REAL y DOBLE. Dado que el signo y el valor del exponente son iguales, nos centraremos en la mantisa (omito deliberadamente las características no obvias de almacenar los valores del exponente y los valores reales cero, ya que complican la comprensión y distraen de la esencia del problema, si está interesado, consulte el estándar IEEE 754). ¿Qué obtendremos? En la línea superior daré la “mantisa” para el tipo REAL (teniendo en cuenta el redondeo del último bit en 1 al número representable más cercano, de lo contrario será 0.099999...), y en la línea inferior - para el tipo DOBLE PRECISIÓN:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
¡Obviamente estos son dos números completamente diferentes! Por tanto, al comparar, el primer número se rellenará con ceros y, por tanto, será mayor que el segundo (teniendo en cuenta el redondeo, el marcado en negrita). Esto explica la ambigüedad de nuestros ejemplos. En el segundo ejemplo, el número 0.1 especificado explícitamente se convierte al tipo DOBLE PRECISIÓN y luego se compara con un número del tipo REAL. Ambos se reducen al mismo tipo y tenemos exactamente lo que vemos arriba. Modifiquemos la consulta para que todo encaje en su lugar:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
Y efectivamente, realizando una doble reducción del número 0.1 a REAL y DOBLE PRECISIÓN, obtenemos la respuesta al enigma:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
Esto también explica el tercer ejemplo anterior. El número 123 es simple. es imposible encajar la mantisa en 24 bits (23 explícitos + 1 implícito). El número entero máximo que cabe en 24 bits es 224-1 = 16 777 215. Por lo tanto, nuestro número 123 456 789 se redondea al representable más cercano 123 456 792. Al cambiar el tipo a DOBLE PRECISIÓN, ya no vemos este escenario:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
Eso es todo. Resulta que no hay milagros. Pero todo lo descrito es una buena razón para pensar cuánto necesitas realmente el tipo REAL. Quizás la mayor ventaja de su uso sea la velocidad de los cálculos con una conocida pérdida de precisión. Pero, ¿sería éste un escenario universal que justificaría un uso tan frecuente de este tipo? No pienses.
Fuente: habr.com