Nach der Veröffentlichung Über die Besonderheiten der Typisierung in PostgreSQL war der erste Kommentar über die Schwierigkeiten im Umgang mit Fließkommazahlen. Ich habe beschlossen, schnell durch den Code der mir verfügbaren SQL-Abfragen zu schauen, um zu sehen, wie oft der Typ REAL darin verwendet wird. Es wird, wie sich herausstellt, ziemlich häufig verwendet, und nicht immer verstehen die Entwickler die damit verbundenen Gefahren. Und das trotz der Tatsache, dass im Internet und auf Habr viele gute Artikel über die Besonderheiten der Speicherung von Fließkommazahlen im Arbeitsspeicher und den Umgang mit ihnen zu finden sind. Daher werde ich in diesem Artikel versuchen, diese Besonderheiten auf PostgreSQL anzuwenden und die damit verbundenen Probleme „anhand von Beispielen“ zu erörtern, damit es den Entwicklern von SQL-Abfragen leichter fällt, sie zu vermeiden.
Die Dokumentation von PostgreSQL enthält den prägnanten Satz: „Das Management solcher Fehler und deren Verbreitung im Berechnungsprozess ist Gegenstand eines gesamten Bereichs der Mathematik und Informatik und wird hier nicht behandelt“ (und verweist dabei weise den Leser auf den Standard IEEE 754). Welche Fehler sind hier gemeint? Lassen Sie uns diese nacheinander diskutieren, und bald wird klar, warum ich wieder zur Feder gegriffen habe.
Nehmen wir zum Beispiel eine einfache Abfrage:
********* ABFRAGE *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 Zeile)
Als Ergebnis werden wir nichts Besonderes sehen – wir erhalten das erwartete 0.1. Aber jetzt vergleichen wir es mit 0.1:
********* ABFRAGE *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 Zeile)
Sind ungleich! Was für ein Wunder! Aber es geht noch weiter. Jemand könnte sagen, ich weiß, dass REAL mit Bruchzahlen schlecht umgeht, also werde ich ganze Zahlen dort eingeben, mit denen wird doch alles gut sein. In Ordnung, lassen Sie uns die Zahl 123 456 789 in den Typ REAL umwandeln:
********* ABFRAGE *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 Zeile)
Und sie ist 3 größer geworden! Die Datenbank hat endgültig das Rechnen verlernt! Oder missverstehen wir etwas? Lassen Sie uns das klären.
Um anzufangen, lassen Sie uns die Grundkenntnisse auffrischen. Wie bekannt ist, kann jede Dezimalzahl in Potenzen von zehn zerlegt werden. So wird die Zahl 123.456 gleich 1*10² + 2*10¹ + 3*10⁰ + 4*10⁻¹ + 5*10⁻² + 6*10⁻³. Aber der Computer arbeitet mit Zahlen in binärer Form, daher müssen sie in Form von Potenzen von zwei dargestellt werden. Deshalb wird die Zahl 5.625 in binärer Form als 101.101 dargestellt und entspricht 1*2² + 0*2¹ + 1*2⁰ + 1*2⁻¹ + 0*2⁻² + 1*2⁻³. Während positive Potenzen von zwei immer ganze Dezimalzahlen (1, 2, 4, 8, 16 usw.) ergeben, ist es bei negativen komplizierter (0,5, 0,25, 0,125, 0,0625 usw.). Das Problem ist, dass nicht jede dezimale Bruchzahl als endlicher binärer Bruch dargestellt werden kann. So präsentiert sich unser berüchtigtes 0,1 in Form eines binären Bruchs als periodischer Wert 0,0(0011). Folglich wird der Endwert dieser Zahl im maschinellen Speicher je nach Genauigkeit variieren.
Jetzt ist der richtige Zeitpunkt, um sich daran zu erinnern, wie Gleitkommazahlen im Computer gespeichert werden. Grob gesagt besteht eine Gleitkommazahl aus drei Hauptteilen – Vorzeichen, Mantisse und Exponent. Das Vorzeichen kann entweder plus oder minus sein, daher wird dafür ein Bit reserviert. Die Anzahl der Bits für Mantisse und Exponent wird jedoch durch den Gleitkommatyp bestimmt. So beträgt bei dem Typ REAL die Länge der Mantisse 23 Bits (ein Bit, das gleich 1 ist, wird implizit am Anfang der Mantisse hinzugefügt, sodass es insgesamt 24 wird), und der Exponent hat 8 Bits. Zusammen ergibt das 32 Bits oder 4 Byte. Bei dem Typ DOUBLE PRECISION beträgt die Länge der Mantisse bereits 52 Bits, und der Exponent hat 11 Bits, insgesamt also 64 Bits oder 8 Byte. PostgreSQL unterstützt keine höhere Genauigkeit für Fließkommazahlen.
Lassen Sie uns unsere Zahl 0.1 in dezimaler Form in beiden Typen – REAL und DOUBLE PRECISION – verpacken. Da Vorzeichen und Wert des Exponenten übereinstimmen, konzentrieren wir uns auf die Mantisse (ich lasse bewusst die nicht offensichtlichen Besonderheiten der Speicherung von Exponentenwerten und null Gleitkommazahlen weg, da sie das Verständnis erschweren und vom Kern des Problems ablenken; bei Interesse – siehe Standard IEEE 754). Was erhalten wir? In der oberen Zeile gebe ich die „Mantisse“ für den Typ REAL an (unter Berücksichtigung der Rundung des letzten Bits auf 1 auf die nächstgelegene darstellbare Zahl; andernfalls ergibt sich 0,099999 …), und in der unteren Zeile die für den Typ DOUBLE PRECISION:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Offensichtlich sind das zwei völlig unterschiedliche Zahlen! Daher wird beim Vergleich die erste Zahl mit Nullen ergänzt und ist somit größer als die zweite (unter Berücksichtigung der gerundeten – fett markierten Eins). Dies erklärt die Mehrdeutigkeit in unseren Beispielen. Im zweiten Beispiel wird die ausdrücklich angegebene Zahl 0.1 in den Typ DOUBLE PRECISION umgewandelt, bevor sie mit einer Zahl vom Typ REAL verglichen wird. Beide werden in denselben Typ konvertiert, und wir haben genau das, was oben zu sehen ist. Lassen Sie uns die Abfrage so ändern, dass alles an seinen Platz rückt:
********* ABFRAGE *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 Zeile)
Und tatsächlich, nachdem wir die Zahl 0.1 zweimal in REAL und DOUBLE PRECISION umgewandelt haben, erhalten wir die Antwort auf das Rätsel:
********* ABFRAGE *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 Zeile)
Dies erklärt auch das dritte Beispiel aus den oben genannten. Die Zahl 123 456 789 ist einfach nicht in 24 Bit Mantisse unterzubringen (23 explizite + 1 implizite). Die maximale ganze Zahl, die in 24 Bit untergebracht werden kann, beträgt 2^24-1 = 16 777 215. Daher wird unsere Zahl 123 456 789 auf die nächstgelegene darstellbare Zahl 123 456 792 gerundet. Wenn wir den Typ auf DOUBLE PRECISION ändern, sehen wir dieses Szenario bereits nicht mehr:
********* ABFRAGE *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 Zeile)
Das ist alles. Es stellt sich heraus, dass es keine Wunder gibt. Aber all dies ist ein guter Anlass, darüber nachzudenken, wie notwendig Ihnen der Typ REAL wirklich ist. Vielleicht ist der größte Vorteil seiner Verwendung die Schnelligkeit der Berechnungen mit einer unvermeidlichen Verlust an Genauigkeit. Aber wird dies ein universelles Szenario sein, das die häufige Verwendung dieses Typs rechtfertigt? Ich denke nicht.
Quelle: habr.com
