Dopo la pubblicazione riguardo alle caratteristiche della digitazione in PostgreSQL, il primo commento riguardava le difficoltà di lavorare con numeri reali. Ho deciso di dare una rapida occhiata al codice delle query SQL a mia disposizione per vedere quanto spesso utilizzano il tipo REAL. Si scopre che viene utilizzato abbastanza spesso e gli sviluppatori non sempre ne comprendono i pericoli. E questo nonostante ci siano molti buoni articoli su Internet e su Habré sulle peculiarità di memorizzare numeri reali nella memoria del computer e su come lavorare con essi. Pertanto, in questo articolo cercherò di applicare tali funzionalità a PostgreSQL e proverò a dare una rapida occhiata ai problemi ad esse associati, in modo che sia più facile per gli sviluppatori di query SQL evitarli.
La documentazione di PostgreSQL afferma succintamente: “La gestione di tali errori e la loro propagazione durante il calcolo è oggetto di un intero ramo della matematica e dell'informatica, e non è trattata qui” (rimandando saggiamente il lettore allo standard IEEE 754). Che tipo di errori si intendono qui? Discutiamoli in ordine, e presto diventerà chiaro il motivo per cui ho ripreso in mano la penna.
Prendiamo ad esempio una semplice richiesta:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
Di conseguenza, non vedremo nulla di speciale: otterremo lo 0.1 previsto. Ma ora confrontiamolo con 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
Non uguale! Che miracoli! Ma oltre, di più. Qualcuno dirà, so che REALE si comporta male con le frazioni, quindi inserirò lì i numeri interi e con loro andrà sicuramente tutto bene. Ok, trasformiamo il numero 123 in REALE:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
E si sono rivelati altri 3! Questo è tutto, il database ha finalmente dimenticato come si conta! Oppure stiamo fraintendendo qualcosa? Scopriamolo.
Innanzitutto, ricordiamo il materiale. Come sai, qualsiasi numero decimale può essere espanso in potenze di dieci. Quindi, il numero 123.456 sarà uguale a 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Ma il computer opera con i numeri in forma binaria, quindi devono essere rappresentati sotto forma di espansione in potenze di due. Pertanto, il numero 5.625 in binario viene rappresentato come 101.101 e sarà uguale a 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. E se le potenze positive di due danno sempre numeri decimali interi (1, 2, 4, 8, 16, ecc.), Allora con quelle negative tutto è più complicato (0.5, 0.25, 0.125, 0,0625, ecc.). Il problema è che Non tutti i decimali possono essere rappresentati come una frazione binaria finita. Pertanto, il nostro famigerato 0.1 sotto forma di frazione binaria appare come valore periodico 0.0 (0011). Di conseguenza, il valore finale di questo numero nella memoria del computer varierà a seconda della profondità di bit.
Ora è il momento di ricordare come vengono archiviati i numeri reali nella memoria del computer. In generale, un numero reale è composto da tre parti principali: segno, mantissa ed esponente. Il segno può essere più o meno, quindi gli viene assegnato un bit. Ma il numero di bit della mantissa e dell'esponente è determinato dal tipo reale. Pertanto, per il tipo REAL, la lunghezza della mantissa è di 23 bit (un bit uguale a 1 viene implicitamente aggiunto all'inizio della mantissa e il risultato è 24) e l'esponente è di 8 bit. Il totale è 32 bit o 4 byte. E per il tipo DOUBLE PRECISION, la lunghezza della mantissa sarà di 52 bit e l'esponente sarà di 11 bit, per un totale di 64 bit, o 8 byte. PostgreSQL non supporta una maggiore precisione per i numeri in virgola mobile.
Imballiamo il nostro numero decimale 0.1 nei tipi REALE e DOUBLE PRECISION. Poiché il segno e il valore dell'esponente sono gli stessi, ci concentreremo sulla mantissa (ometto deliberatamente le caratteristiche non ovvie della memorizzazione dei valori dell'esponente e dei valori reali zero, poiché complicano la comprensione e distraggono dall'essenza del problema, se interessati, vedere lo standard IEEE 754). Cosa otterremo? Nella riga superiore darò la “mantissa” per il tipo REALE (tenendo conto dell'arrotondamento dell'ultimo bit per 1 al numero rappresentabile più vicino, altrimenti sarà 0.099999...), e nella riga inferiore - per il tipo DOPPIA PRECISIONE:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Ovviamente si tratta di due numeri completamente diversi! Pertanto, durante il confronto, il primo numero verrà riempito con zeri e, quindi, sarà maggiore del secondo (tenendo conto dell'arrotondamento, quello contrassegnato in grassetto). Questo spiega l’ambiguità dei nostri esempi. Nel secondo esempio, il numero 0.1 specificato esplicitamente viene convertito nel tipo DOUBLE PRECISION e quindi confrontato con un numero del tipo REAL. Entrambi sono ridotti allo stesso tipo e abbiamo esattamente ciò che vediamo sopra. Modifichiamo la query in modo che tutto vada a posto:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
E infatti, eseguendo una doppia riduzione del numero 0.1 a REALE e DOPPIA PRECISIONE, otteniamo la risposta all'enigma:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
Questo spiega anche il terzo esempio sopra. Il numero 123 è semplice è impossibile adattare la mantissa a 24 bit (23 espliciti + 1 implicita). Il numero intero massimo che può essere contenuto in 24 bit è 224-1 = 16. Pertanto, il nostro numero 777 viene arrotondato al rappresentabile più vicino 215. Cambiando il tipo in DOUBLE PRECISION, non vediamo più questo scenario:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
È tutto. Si scopre che non ci sono miracoli. Ma tutto quanto descritto è un buon motivo per pensare a quanto hai davvero bisogno del tipo REALE. Forse il più grande vantaggio del suo utilizzo è la velocità dei calcoli con una nota perdita di precisione. Ma sarebbe questo uno scenario universale tale da giustificare un utilizzo così frequente di questo tipo? Non pensare.
Fonte: habr.com