Rundungsproblem mit MSSQL

[gmc616]

Hallo Delphi-Gemeinde,

ich haben einen komischen Effekt mit dem MSSQL-2017 im Delphi Rio (Win32/VCL-Anwendung) (WIN-10).
Ich möchte aus einer Rechnung die Summe aller Netto-Beträge, multipliziert mit deren Steuersatz als Brutto-Betrag ermitteln.
Aber irgendwie bekomme ich komische, falsch gerundete Beträge zurück.

Hier ein SQL der das Problem verdeutlichen soll:

Code:

Select
      sum (l.[Preis]) as Netto_Org
,     round(sum ([Preis]),2) as Netto_Round
,     206.5 * 1.19 as Brutto_Korrekt
,     round(206.5 * 1.19,2) as Brutto_Korrekt_round
,     sum( l.[Preis] * ( (r.steuersatz +100) / 100)) as Brutto_Org
,     sum( round(l.[Preis],2) * ( (r.steuersatz +100) / 100)) as Brutto_1
,     round(sum( round(l.[Preis],2) * ( (r.steuersatz +100) / 100)),2) as Brutto_2
,     round( sum( l.[Preis] * ( (r.steuersatz +100) / 100)),2) as Brutto_3
,     round( sum( l.[Preis] * 1.19) ,2) as Brutto_4
,     sum( round( l.[Preis] * 1.19 ,2)) as Brutto_5
,     sum( round( l.[Preis],2) * 1.19 ) as Brutto_6

from RechnungListe l, Rechnung r
where ....
group by r.steuersatz;

also Ergebnis dieser Query bekomme ich:

Code:

Netto_Org   Netto_Round Brutto_Korrekt Brutto_Korrekt_round Brutto_Org  Brutto_1    Brutto_2    Brutto_3    Brutto_4    Brutto_5    Brutto_6
----------- ----------- -------------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
206,5       206,5       245,735        245,74               245,735     245,735     245,73      245,73      245,73      245,72      245,735

Alle Einzelpositionen (l.[Preis]) sind floats und mit 2 Nachkommastellen gespeichert.
Der Netto-Betrag dieser Rechnung ist tatsächlich 206.50 (€), der Steuersatz ist 19 (%).
Schaut man sich aber [Brutto_1], [Brutto_2] oder [Brutto_3] an, stimmen die Rundung nicht. [Brutto_4], [Brutto_5] und [Brutto_6] sind Versuche mögliche Fehler auszuschließen.

Und jetzt wird's richtig wild.
Fetsche ich den [Netto_Org]-Wert heraus (im Debugger = 206.5) und schreibe den Wert in eine extended-Variable, multipliziere mit 1.19 erhalte ich wieder 245.735
Runde ich anschließen die Variable mit RoundTo (Wert,-2) bekomme ich 245.73 heraus, statt 245.74.

Delphi-Quellcode:

var
  nPow, nValue : extended;
begin 
  nPow := power(10,2);

// Test 1
  nValue := ADO.FieldByName('Netto_org').AsFloat;  // 206.5 laut Debugger
  nValue := nValue * 1.19;
  nValue := RoundTo(nValue,-2);
//  = 245.73 , falsch

// Test 2
  nValue := ADO.FieldByName('Netto_org').AsFloat; // 206.5 laut Debugger
  nValue := nValue * 1.19;
  nValue := nValue * nPow;
  nValue := nValue + 0.5;
  nValue := trunc(nValue);  // <- hier passiert etwas komisches !! 24574 wird zu 24573 ???
  nValue := nValue/nPow;
//  = 245.73 , falsch

// Test 3
  nValue := 206.5;
  nValue := nValue * 1.19;
  nValue := RoundTo(nValue,-2);
//  = 245.74 , korrekt.

// Test 4
  nValue := ADO.FieldByName('Netto_round').AsFloat; // ebenfalls 206.5 laut Debugger
  nValue := nValue * 1.19;
  nValue := RoundTo(nValue,-2);
//  = 245.74 , korrekt.

Irgendwie bekomme ich von der Datenbank nicht den korrekten Wert zurück, bzw. die Rundung im SQL-Server funktioniert nicht richtig.
Kann mir jemand erklären was hier schief läuft?

Danke.

[mkinzler]

Du summierst gerundete Werte, damit summierst Du auch die Rundungsdifferenzen

[Bernhard Geyer]

Für Geldbeträge empfiehlt sich der passend Datentyp Money (MS SQL Server) bzw. die Methoden AsCurreny wenn man in Delphi damit arbeitet.
das mit sollten die Rundungsprobleme die sich durch die Verwendung von Single/Extended-Datentypen ergeben erledigen.

[gmc616]

Wo summiere ich im Test-1 und Test-2 gerundete Werte?

Der Tipp mit AsCurreny funktioniert.
Hätte ich auch selbst drauf kommen können, das mal auszuprobieren. Manchmal ist man einfach festgefahren.

Aber wieso funktioniert das? Was ist der Unterschied zu AsFloat?

Auch Brutto_3-Betrag hätte doch einen korrekten Wert zurück liefern müssen. Aber das liegt wohl am Datentyp.

[Andreas13]

Hallo gmc616,
Du solltest anstelle von nPow := power(10,2); besserConst nPow = 100 schreiben, denn power(..) arbeitet mit Logarithmen, wodurch automatisch Rundungsfehler in den letzten Stellen entstehen.

Bis Du Dir sicher, daß der Wert von Netto_Org genau 206,5 ist? Ein winziger Rundungsfehler bei der internen binären Darstellung z.B. von 206,4999999999 würde schon dazu führen, daß korrekt auf 245,73 abgerundet wird. Kannst Du den aktuellen Wert Netto_Org auch 18 Stellen ausdrucken lassen? Im Debugger sieht man oft nur gerundete Werte.

Welchen Datentyp hat Netto_Org in Deiner Datenbank?

Gruß, Andreas

[Andreas13]

Der Datentyp Currency ist allerdings keine „Allzweckwaffe“, denn die seine festen 4 Nachkommastellen sind nur bei Addition und Subtraktion von Währungseinheiten (mit max. 4 Nachkommastellen!) exakt. Bei Multiplikation, Division und sonstigen transzendenten Operationen pflanzen sich die Rundungsfehler in hintere Nachkommastellen fort und das Problem bleibt bestehen.

Wenn Du nPow := power(10,2); anstellen einer Konstanten-Deklaration verwenden willst, dann solltest Du eine einfache Funktion Function IntegerPotenz(Basis, Potenz: Integer): Integer; schreiben, welche die Basis (hier: 10) n-mal (her: 2-mal) miteinander multipliziert. Dann bist Du flexibler.
Gruß, Andreas

[Andreas13]

Wenn Du nur die Integer-Potenzen von 10 benötigtst, könntest Du so vorgehen:

Delphi-Quellcode:

Function IntPower_of_10(n: Word): Extended;
// Integerpotenzen von 10
VAR
  i, Pow: Integer;
Begin
  Pow:= 1;

  For i:= 1 To n Do
  Begin
    Pow:= Pow*10;
  End;

  Result:= Pow;
End;{IntPower_of_10}

Gruß, Andreas

[gmc616]

Hallo Andreas13,

Danke für den Tipp mit Power().
Ich hatte ein Stück meiner alternativen Round-Funktion für das Beispiel oben übernommen.
Ich kenne das RundungsProblem mit doubles aus C++ / Visual Studio. Dafür habe ich damals eine Funktion zusammengeschrieben, welche berechnete double-Werte korrekt rundet. Und da ich auch im Delphi immer wieder mal Rundungs-Probleme mit doubles hatte, habe ich diese Funktion von C nach Delphi übersetzt und der Einfachheit halber Power(10,AnzNachkommaStellen) zum Potenzieren genutzt. Das hatte damals mein RundungsProblem gelöst.

Wie ich aber gelesen habe, sollen die größeren Datentypen wie real oder extended nicht mehr diese RundungProblem haben. Ging die letzten paar Jahre ja auch gut.
Daher fand ich es komisch, dass, wenn ich im Delphi mit der 206.5 [Netto_Org] weiter rechne, dann doch wieder dieser Rundungsfehler wieder auftritt.
Ist mit AsCurrency gelöst.
Komisch finde ich es immernoch, weshalb AsCurrency funktioniert und AsFloat nicht. Naja ...

Das der Datentyp Currency auf "nur" max. 4 Nachkommastellen exakt ist, ist mir im Rechnungswesen ausreichend.
Hatte mich nur gewundert, weshalb der SQL-Server hier falsch rundet. Aber das wird wohl dem DB-Datentyp float geschuldet sein.
Hätte der Entwickler (ist nicht mein Projekt) hier Money benutzt, wäre das Problem evtl. gar nicht aufgetreten.

Das der Debugger auch nur gerundete Werte anzeigt, is natürlich ne fiese Falle. Kenne ich aus Visual Studio anders.

Und ja, [Netto_Org] ist genau 206,50. Zumindest so weit ich das in der DB sehen und zusammen rechnen kann. Steht auch so auf dem Papier.
Auch kenne ich weitestgehend den Code der Anwendung (nicht mein Projekt), die die Rechnungen erstellt. Hier werden explizit nur 2-stellige Beträge in die DB geschrieben, eben um spätere Rundungsfehler zu vermeinden.
Scheint dann ja wohl doch nicht zu funktionieren^^ Nicht mein Projekt!

[Andreas13]

Bei allen RealTypen gibt es unvermeidbare Rundungsfehler. Das liegt in der Natur der Sache, denn es gibt unendlich viele Reelle Zahlen, die wir mittels Single, Real48, Double oder Extended auf eine endliche Menge binärer Darstellungen mit endlichen Ziffern abbilden.
Selbst die Dezimalzahl 0,1 läßt sich binär nicht exakt darstellen, wodurch bereits bei der Eingabe ein Rundungsfehler entsteht. Da hilft es auch nicht, wenn man die Dateneingabe auf 2 Nachkommastellen begrenzt.

Zitat:

Ging die letzten paar Jahre ja auch gut.

Ja, Du hast eben Glück gehabt, oder der Fehler ist niemandem aufgefallen.

Rundungsfehler sind ein ewiges Problem der Real-Zahl-Arithmetik. Und es gibt wegen der obigen Problematik „unendlich gegen endlich“ keine Lösung dafür. Es hilft nur, wenn man mit wesentlich meeeeeeehr Ziffern rechnet, also Extended oder noch mehr, und erst ganz am Ende der Berechnung rundet. Für meine kritischen Berechnungen verwende ich Multipräzisions-Arithmetik-Routinen mit 200 … 500 Stellen und konvertiere das Ergebnis auf Extended. Und trotzdem muß ich zahlreiche Vorkehrungen treffen, wenn z. B. eine eigentlich positive kleine Zahl infolge von Rundungsfehlern plötzlich negativ wird und die darauffolgende Operation (Quadratwurzel, Logarithmus etc.) versagen würde. Rundungsfehler haben mich bisher manch ein graues Haar und schlaflose Nächte gekostet.
Gruß, Andreas

[Andreas13]

Noch eine kleine Ergänzung, falls es jemanden interessieren sollte. Neben der Verwendung von vieeeeeelen Nachkommastellen (Multipräzisions-Arithmetik) gibt es etliche Literaturquellen mit ausgefeilten Algorithmen zur Fehlerkompensation diverser Rechenoperationen innerhalb der endlich begrenzten Stellenzahl (meistens für den DatenType Double).

Gute Beschreibungen sind zu finden u. a. in folgenden exzellenten Büchern:

1): Knuth: The Art of computer programming, Volume 1 bis 4A (1997, 1998, 2011)
2): Higham: Accuracy an Stability of Numerical Algoritms (2002)
3): Einarsson: Accuracy and Reliability in Scientific Computing (2005)

Gruß, Andreas
[edit]
PS:
... und noch mehr gute Fachbücher:

4): Muller + Brisebarre + Dinechin + Jeannerod et al: Handbook of Floating-Point Arithmetic (2010)
5): Nassif + Fayyad: Introduction To Numerical Analysis And Scientific Computing (2014)
6): Sterbenz: Floating-Point Computation (1974)