Dopo la pubblicazione sulle peculiarità della tipizzazione in PostgreSQL, il primo commento ha riguardato le difficoltà nell'uso dei numeri reali. Ho deciso di dare un'occhiata veloce al codice delle query SQL a mia disposizione per vedere quanto spesso venisse utilizzato il tipo REAL. È stato usato abbastanza frequentemente, come si è rivelato, e non sempre gli sviluppatori comprendono i pericoli che lo accompagnano. Questo nonostante ci siano molte buone articoli su Internet e su Habr riguardo alle peculiarità della memorizzazione dei numeri reali nella memoria del computer e al loro utilizzo. Pertanto, in questo articolo cercherò di applicare tali peculiarità a PostgreSQL, e proverò a esaminare «letteralmente» i problemi a essi associati, per facilitare agli sviluppatori di query SQL l'evitarli.
La documentazione di PostgreSQL contiene una frase concisa: «La gestione di tali errori e la loro diffusione nel corso dei calcoli è oggetto di studio di un'intera branca della matematica e dell'informatica, e qui non viene trattata» (rimandando quindi saggiamente il lettore allo standard IEEE 754). Quali errori si intendono? Discutiamoli in ordine, e presto diventerà chiaro perché ho deciso di riprendere la penna.
Prendiamo, ad esempio, una semplice query:
********* QUERY *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 riga)
Di conseguenza, 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 meraviglie! Ma c'è di più. Qualcuno dirà, so che REAL si comporta male con le frazioni, quindi inserisco numeri interi, con quelli sicuramente andrà bene. Ok, proviamo a convertire il numero 123 456 789 al tipo REAL:
********* QUERY *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 riga)
E magicamente è diventato più grande di 3! La base ha completamente dimenticato come si fa a contare! O siamo noi a non comprendere qualcosa? Vediamo di chiarire.
Iniziamo rivedendo le basi. Come sappiamo, 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 il computer opera con numeri in forma binaria, quindi è necessario rappresentarli come scomposizione in potenze di due. Pertanto, il numero 5.625 in forma binaria si rappresenta come 101.101 e sarà uguale a 1*2² + 0*2¹ + 1*2⁰ + 1*2⁻¹ + 0*2⁻² + 1*2⁻³. Mentre le potenze positive di due danno sempre numeri decimali interi (1, 2, 4, 8, 16, ecc.), le potenze negative sono più complesse (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 famoso 0.1 in forma di frazione binaria si presenta come valore periodico 0.0(0011). Di conseguenza, il valore finale di questo numero nella memoria del computer varierà a seconda della larghezza di parola.
Ora è il momento di ricordare come i numeri reali vengono memorizzati nella memoria del computer. Parlando in termini generali, un numero reale consiste di tre parti principali: segno, mantissa ed esponente. Il segno può essere sia positivo che negativo, quindi occupa un bit. La quantità di bit per la mantissa e l'esponente è definita dal tipo di dato reale. Per il tipo REAL, la lunghezza della mantissa è di 23 bit (un bit, pari a 1, viene aggiunto implicitamente all'inizio della mantissa, per un totale di 24), mentre l'esponente è di 8 bit. In totale, abbiamo 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 maggiore precisione 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 trascurando le particolarità non ovvie della memorizzazione dei valori esponenziali e dei valori reali nulli, in quanto appesantiscono la comprensione e distraggono dall'essenza del problema; se siete interessati, consultate lo standard IEEE 754). Cosa otteniamo? Nella riga superiore fornirò la "mantissa" per il tipo REAL (considerando l'arrotondamento dell'ultimo bit in 1 al numero rappresentabile più vicino, altrimenti otterremmo 0.099999…), e nella riga inferiore per il tipo DOUBLE PRECISION:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
È evidente che si tratta di due numeri completamente diversi! Pertanto, durante il confronto, il primo numero sarà completato con zeri e, di conseguenza, sarà maggiore del secondo (considerando l'arrotondamento – la singola unità in grassetto). Questo spiega l'ambiguità nei nostri esempi. Nel secondo esempio, il numero specificato 0.1 viene convertito nel tipo DOUBLE PRECISION, dopodiché viene confrontato con un numero di tipo REAL. Entrambi sono convertiti nello stesso tipo, e abbiamo esattamente ciò che vediamo sopra. Modifichiamo la query affinché tutto torni al suo posto:
********* QUERY *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 riga)
E infatti, eseguendo la doppia conversione del numero 0.1 in REAL e DOUBLE PRECISION otteniamo la risposta all'enigma:
********* QUERY *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 riga)
Questo spiega anche il terzo esempio tra quelli sopra indicati. Il numero 123 456 789 è semplicemente impossibile da adattare in 24 bit di mantissa (23 espliciti + 1 implicito). Il numero intero massimo che può essere posizionato in 24 bit sarà 224-1 = 16 777 215. Pertanto, il nostro numero 123 456 789 viene arrotondato al valore rappresentabile più vicino, 123 456 792. Cambiando tipo in DOUBLE PRECISION, non vedremo più tale scenario:
********* QUERY *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 riga)
Ecco fatto. Si rivela che non ci sono miracoli. Ma quanto descritto è un buon motivo per riflettere su quanto sia davvero necessario il tipo REAL. Probabilmente, il maggior vantaggio del suo utilizzo è la rapidità dei calcoli con una perdita di precisione già nota. Ma sarà questo uno scenario universale che giustifichi un uso così frequente di questo tipo? Non lo credo.
Fonte: habr.com
