Nach der Veröffentlichung Über die Besonderheiten der Typisierung in PostgreSQL wurde im ersten Kommentar die Komplexität der Arbeit mit Gleitzahlen erwähnt. Ich habe beschlossen, schnell den Code der mir verfügbaren SQL-Abfragen zu durchsehen, um zu prüfen, wie oft der Typ REAL darin verwendet wird. Überraschenderweise wird er relativ häufig verwendet, und nicht immer sind sich die Entwickler der Gefahren bewusst, die damit verbunden sind. Das ist bemerkenswert, da es im Internet und auf Habr viele gute Artikel über die Besonderheiten der Speicherung von Gleitzahlen im Arbeitsspeicher und den Umgang mit ihnen gibt. Daher werde ich in diesem Artikel versuchen, diese Besonderheiten auf PostgreSQL anzuwenden und die damit verbundenen Schwierigkeiten anschaulich zu erklären, damit es den Entwicklern von SQL-Abfragen leichter fällt, diese zu vermeiden.
Die PostgreSQL-Dokumentation enthält den prägnanten Satz: „Die Handhabung solcher Fehler und deren Verbreitung im Berechnungsprozess ist Gegenstand eines gesamten Bereichs der Mathematik und Informatik, der hier nicht behandelt wird“ (wobei sie den Leser weise auf den Standard IEEE 754 verweist). Welche Fehler sind hier gemeint? Lassen Sie uns diese der Reihe nach besprechen, und bald wird klar, warum ich wieder zum Stift gegriffen habe.
Nehmen wir als Beispiel eine einfache Abfrage:
********* ABFRAGE *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 Zeile)
Infolgedessen werden wir nichts Besonderes sehen – wir erhalten das erwartete 0.1. Aber vergleichen wir es nun mit 0.1:
********* ABFRAGE *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 Zeile)
Nicht gleich! Was für Wunder! Aber das ist noch nicht alles. Jemand könnte sagen, ich weiß, dass REAL sich schlecht mit Bruchzahlen verhält, also werde ich ganze Zahlen eingeben, bei denen muss ja alles gut sein. Okay, lassen Sie uns die Zahl 123 456 789 in den REAL-Typ umwandeln:
********* ABFRAGE *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 Zeile)
Und es wurde um 3 höher! Die Datenbank hat das Rechnen endgültig verlernt! Oder verstehen wir etwas nicht? Lassen Sie uns das klären.
Fangen wir mit der Materie an. Wie bekannt ist, lässt sich jede Dezimalzahl in Potenzen von zehn zerlegen. So entspricht die Zahl 123,456 dem Wert 1*10² + 2*10¹ + 3*10⁰ + 4*10⁻¹ + 5*10⁻² + 6*10⁻³. Der Computer arbeitet jedoch mit Zahlen im Binärformat, weshalb sie in Form von Zerlegungen in Potenzen von zwei dargestellt werden müssen. Damit wird die Zahl 5,625 im Binärformat 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 ergeben (1, 2, 4, 8, 16 usw.), ist es bei den negativen komplizierter (0,5, 0,25, 0,125, 0,0625 usw.). Das Problem ist, dass nicht jede Dezimalbrüche als endlicher Binärbruch dargestellt werden kann. So zeigt 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 Arbeitsspeicher je nach Bitbreite variieren.
Jetzt ist es an der Zeit, sich daran zu erinnern, wie Gleitkommazahlen im Speicher eines Computers gespeichert werden. Allgemein gesagt, besteht eine Gleitkommazahl aus drei Hauptteilen – dem Vorzeichen, der Mantisse und dem Exponenten. Das Vorzeichen kann entweder positiv oder negativ sein, weshalb dafür ein Bit verwendet wird. Die Anzahl der Bits für Mantisse und Exponenten wird durch den Gleitkommatyp bestimmt. So hat der Typ REAL eine Mantissenlänge von 23 Bits (ein Bit, das gleich 1 ist, wird implizit an den Anfang der Mantisse hinzugefügt, sodass es 24 ergibt), und der Exponent hat 8 Bits. Insgesamt ergibt das 32 Bits oder 4 Bytes. Bei dem Typ DOUBLE PRECISION beträgt die Mantissenlänge bereits 52 Bits, und der Exponent hat 11 Bits, zusammen also 64 Bits oder 8 Bytes. Eine höhere Genauigkeit für Gleitkommazahlen wird von PostgreSQL nicht unterstützt.
Lassen Sie uns unsere Zahl 0.1 in dezimaler Form in beiden Typen – REAL und DOUBLE PRECISION – verpacken. Da das Vorzeichen und der Wert des Exponenten übereinstimmen, konzentrieren wir uns auf die Mantisse (ich lasse bewusst nicht offensichtliche Merkmale der Speicherung von Exponentenwerten und nullen reellen Werten außen vor, da sie das Verständnis erschweren und vom Kern des Problems ablenken; wenn es Sie interessiert, schauen Sie sich den Standard IEEE 754 an). Was erhalten wir? In der oberen Zeile gebe ich die „Mantisse“ für den Typ REAL an (unter Berücksichtigung der Rundung der letzten Eins auf die nächstgelegene darstellbare Zahl, sonst erhält man 0.099999…), und in der unteren für den Typ DOUBLE PRECISION:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Offensichtlich sind das zwei völlig verschiedene Zahlen! Daher wird beim Vergleichen die erste Zahl mit Nullen aufgefüllt und ist folglich größer als die zweite (unter Berücksichtigung der Rundung – des fett markierten Eins). Damit wird die Mehrdeutigkeit aus unseren Beispielen erklärt. Im zweiten Beispiel wird die klar angegebene Zahl 0.1 in den Typ DOUBLE PRECISION umgewandelt und anschließend mit der Zahl des Typs REAL verglichen. Beide werden in denselben Typ konvertiert, und wir haben genau das, was oben zu sehen ist. Lassen Sie uns die Abfrage abändern, damit alles an seinen Platz kommt:
********* ANFRAGE *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 Zeile)
Und tatsächlich, wenn wir die Zahl 0.1 doppelt in REAL und DOUBLE PRECISION umwandeln, erhalten wir die Antwort auf das Rätsel:
********* ANFRAGE *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 Zeile)
Das erklärt auch das dritte Beispiel aus den oben genannten. Die Zahl 123 456 789 passt einfach nicht in 24 Bit Mantisse (23 explizite + 1 implizite). Die größte Ganzzahl, die in 24 Bit Platz finden kann, ist 2^24-1 = 16 777 215. Daher wird unsere Zahl 123 456 789 auf die nächstgelegene darstellbare 123 456 792 gerundet. Wenn wir auf DOUBLE PRECISION umschalten, sehen wir dieses Szenario bereits nicht mehr:
********* ANFRAGE *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 Zeile)
Das war's. Es scheint keine Wunder zu geben. Aber alles, was beschrieben wurde, ist ein guter Anlass, darüber nachzudenken, wie notwendig der Typ REAL für Sie wirklich ist. Vielleicht ist der größte Vorteil seiner Verwendung die Geschwindigkeit der Berechnungen bei gewisser Genauigkeitsverlust. Doch wird dies ein universelles Szenario sein, das den häufigen Einsatz dieses Typs rechtfertigt? Ich denke nicht.
Quelle: habr.com
