Dopo la pubblicazione sulle peculiarità della tipizzazione in PostgreSQL, il primo commento riguardava le difficoltà nel lavorare con i numeri in virgola mobile. Ho deciso di dare una rapida occhiatina al codice delle query SQL a mia disposizione per vedere quanto spesso viene utilizzato il tipo REAL. Sorprendentemente, è stato usato abbastanza frequentemente e non sempre gli sviluppatori comprendono i pericoli ad esso associati. Questo nonostante il fatto che in rete e su Habra ci siano molte buone risorse sulle peculiarità della memorizzazione dei numeri in virgola mobile in memoria e sul loro utilizzo. Pertanto, in questo articolo cercherò di applicare tali peculiarità a PostgreSQL e proverò a esaminare, in modo semplice, le problematiche connesse, in modo che gli sviluppatori di query SQL possano evitarle più facilmente.
La documentazione di PostgreSQL contiene una sintetica frase: "La gestione di tali errori e la loro propagazione durante i calcoli è oggetto di studio di un intero ramo della matematica e dell'informatica, e non verrà trattato qui" (rimandando saggiamente il lettore allo standard IEEE 754). A quali errori si fa riferimento qui? Discutiamoli uno per uno, e diventerà chiaro perché ho ripreso in mano la penna.
Prendiamo, ad esempio, una semplice query:
********* QUERY *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 riga)
In questo caso non vedremo nulla di speciale – otterremo il previsto 0.1. Ma ora confrontiamolo con 0.1:
********* QUERY *********
SELECT 0.1::REAL = 0.1;
**************************
?colonna?
----------
f
(1 riga)
Non sono uguali! Che meraviglia! Ma c'è di più. Qualcuno dirà, so che REAL si comporta male con le frazioni, bene, allora inserisco solo numeri interi, con quelli di certo andrà tutto bene. Ok, vediamo cosa succede convertendo il numero 123 456 789 al tipo REAL:
********* QUERY *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 riga)
È venuto maggiore di 3! La base ha definitivamente dimenticato come contare! O forse non stiamo comprendendo qualcosa? Analizziamo.
Iniziamo con un ripasso della teoria. Come è noto, qualsiasi numero decimale può essere scomposto in potenze di dieci. Così, il numero 123.456 sarà uguale a 1*10² + 2*10¹ + 3*10⁰ + 4*10⁻¹ + 5*10⁻² + 6*10⁻³. Ma un computer opera con i numeri in formato binario, quindi è necessario rappresentarli come scomposizioni in potenze di due. Pertanto, il numero 5.625 in binario si presenta come 101.101 e sarà uguale a 1*2² + 0*2¹ + 1*2⁰ + 1*2⁻¹ + 0*2⁻² + 1*2⁻³. E se le potenze positive di due danno sempre numeri decimali interi (1, 2, 4, 8, 16, ecc.), con le potenze negative la situazione è più complessa (0,5, 0,25, 0,125, 0,0625, ecc.). Il problema è che non ogni frazione decimale può essere rappresentata come una frazione binaria finita. Così, il nostro famigerato 0.1 in forma frazionaria binaria si presenta come un valore periodico 0.0(0011). Pertanto, il valore finale di questo numero nella memoria della macchina varierà a seconda della sua precisione.
È ora di ricordare come i numeri in virgola mobile vengono memorizzati nella memoria del computer. In generale, un numero in virgola mobile è composto da tre parti principali: segno, mantissa ed esponente. Il segno può essere positivo o negativo, quindi viene riservato un bit per questo. La quantità di bit per la mantissa e l'esponente è determinata dal tipo di dato. Ad esempio, per il tipo REAL la lunghezza della mantissa è di 23 bit (e un bit, pari a 1, viene implicitamente aggiunto all'inizio della mantissa, per un totale di 24), e l'esponente è di 8 bit. In totale fanno 32 bit, ovvero 4 byte. Per il tipo DOUBLE PRECISION, la lunghezza della mantissa sarà di 52 bit e l'esponente di 11 bit, per un totale di 64 bit, ovvero 8 byte. PostgreSQL non supporta una precisione maggiore per i numeri in virgola mobile.
Imballiamo il nostro numero 0.1 in forma decimale in entrambi i tipi – REAL e DOUBLE PRECISION. Poiché il segno e il valore dell'esponente coincidono, concentriamoci sulla mantissa (sto intenzionalmente tralasciando le caratteristiche non ovvie del salvataggio dei valori esponenziali e dei valori reali nulli, poiché complicano la comprensione e distraggono dal nocciolo della questione. Se sei interessato, consulta lo standard IEEE 754). Cosa otteniamo? Nella parte superiore fornirò la "mantissa" per il tipo REAL (considerando l'arrotondamento dell'ultimo bit a 1 al numero rappresentabile più vicino, altrimenti otteniamo 0.099999…), e nella parte inferiore per il tipo DOUBLE PRECISION:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
È chiaro che si tratta di due numeri totalmente diversi! Pertanto, nella comparazione, il primo numero sarà completato con zeri e, di conseguenza, risulterà maggiore del secondo (considerando l'arrotondamento – come indicato in grassetto dalla singola unità). Questo spiega anche l'ambiguità nei nostri esempi. Nel secondo esempio, il numero 0.1 specificato viene convertito in tipo DOUBLE PRECISION, dopo di che viene confrontato con un numero di tipo REAL. Entrambi vengono convertiti nello stesso tipo, e abbiamo esattamente ciò che vediamo sopra. Modifichiamo la query affinché tutto torni al suo posto:
********* RICHIESTA *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 riga)
Infatti, eseguendo il casting del numero 0.1 a REAL e DOUBLE PRECISION otteniamo la risposta al mistero:
********* RICHIESTA *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 riga)
Questo spiega anche il terzo esempio citato sopra. Il numero 123 456 789 semplicemente non può essere rappresentato in 24 bit di mantissa (23 espliciti + 1 implicito). Il massimo numero intero che può essere contenuto in 24 bit è 2^24-1 = 16 777 215. Pertanto, il nostro numero 123 456 789 viene arrotondato al numero rappresentabile più vicino, 123 456 792. Cambiando il tipo in DOUBLE PRECISION, non vedremo più questo scenario:
********* RICHIESTA *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 riga)
Ecco tutto. Non ci sono stati miracoli. Ma tutto ciò descritto è un buon motivo per riflettere su quanto davvero abbiate bisogno del tipo REAL. Forse, il maggior vantaggio del suo utilizzo è la velocità di calcolo, con una perdita di precisione predefinita. Ma sarà questo uno scenario universale che giustifica un uso così frequente di questo tipo? Non lo credo.
Fonte: habr.com
