Bad Boy: SQL i typ FLOAT

Być może jestem zbyt wyczulony na tym punkcie, ale nóż w kieszeni mi się otwiera, kiedy widzę „programistę”, używającego radośnie typu FLOAT w operacjach finansowych (najczęściej  jest to albo bardzo słaby koder, albo świeżo wypieczony absolwent uczelni wyższej, żyjący ciągle w wierze, że uczelnia dała mu wiedzę absolutną i pełną). Najbardziej jednak irytuje fakt, że te same błędy – które udało się wytępić z jednego języka programowania, np. Javy, gdzie po udzieleniu reprymendy panuje niepodzielnie BigDecimal – powracają ciągle i ciągle w nowych odsłonach. Dziś wróciły w projekcie tabelki bazy danych.

Większość języków programowania dostarcza implementację dla typu liczb rzeczywistych (zmiennoprzecinkowych) – nazwanych float lub double. Na nieszczęście, niektóre bazy danych posiadają typ danych o tej samej nazwie, ot choćby bardzo popularna baza MySQL. I tak, wielu programistów, kiedy potrzebuje zapisać w bazie danych jakąś liczbę rzeczywistą, wybiera – oczywisty dla nich – typ danych SQL: FLOAT. Bez zastanowienia zapisują tam dane finansowe, i co gorsza, nie przejmują się tym, że suma na fakturze nie jest prawidłowa! Po co to sprawdzić?! To nie moje pieniądze, kto by się przejmował 1 groszem ( po zaokrągleniu). Otóż są tacy, co się przejmą… Stawiam siebie jako pierwszego.

 

Zaokrąglenia

 

Programista, który skończył studia, musi wiedzieć, że typ float, zgodnie ze swoim standardem IEEE-754, nie nadaje się do pewnych operacji na liczbach, ze względu na jego skończoną dokładność, sposób zaokrągleń oraz sposób reprezentacji, i wynikające z niego konsekwencje (nie każdą liczbę dziesiętną o skończonym rozwinięciu można zapisać jako skończoną liczbę binarną). Weźmy bardzo prosty przykład:


1/3 + 1/3 + 1/3 = 1.000

ale

0.333 + 0.333 + 0.333 = 0.999

Nawet zwiększając precyzję do nieskończoności, zawsze otrzymamy wynik 0.99999… który nigdy nie będzie tym samym co 1.0. Oczywiście, jak wypiszę floata z dokładnością do np. dwóch miejsc znaczących, to otrzymam swoje 1.00. Czy to rozwiązuje problem? Oczywiście nie!

FLOAT a zapytania SQL

 

Większość silników baz danych zaokrągla wartości float do podanej precyzji, co często sprawia, że programista uważa swój projekt za spełniający standardy, bo wynik ‚kontrolnego’ zapytania jest prawidłowy.

SELECT stawka_godzinowa from Pracownik where pracownik_id = 100;
-- Zwraca: 39.95

Ale uwaga, stawka_godzinowa w tej tabelce to FLOAT. Powyższe zapytanie pokazuje, ile pracownik od ID = 100 zarabia na godzinę. A jak sprawdzić ile zapłacimy mu za 1000000000 godzin (tak żeby łatwo liczyć):

SELECT stawka_godzinowa * 1000000000 from Pracownik where pracownik_id = 100;
-- Zwraca: 39950000762.939

Ups, nie tak jak oczekiwaliśmy… Pracownikowi trzeba zapłacić 762.94 więcej, niż mieliśmy założone. A co jeśli to nie byłby pracownik, a konto bankowe. I taki operacji mnożenia, dzielenia – nawet przez mniejsze liczby mielibyśmy setki tysięcy na godzinę? Klienci banku pewnie by nie zauważyli premii w wysokości paru groszy, ale nasz pracodawca – bank, byłby w poważnych tarapatach.

Jeszcze jeden ciekawy przykład. Znajdźmy pracownika po stawce godzinowej:

SELECT * from Pracownik where stawka_godzinowa = 39.95;
-- Zwraca: brak wyników wyszukiwania

I znowu mała wtopa, prawda? Jak już widzieliśmy wcześniej podczas mnożenia, to co rzeczywiście jest zapisane w bazie, to „troszkę więcej” niż szukane 39.95, więc nie udało się wyszukać wartości dokładnej 39.95. Można z tej patowej sytuacji wybrnąć, podobnie jak to się robiło w C/C++ chcąc porównać dwie zmienne typu float. Szukamy liczby efektywnie takiej samej, albo inaczej, takiej samej z dokładnością do jakiegoś współczynnika epsilon:

SELECT * from Pracownik where ABS(stawka_godzinowa - 39.95) < 0.000001

Jest to jednak obejście problemu tak samo słabe, jak sam projekt bazy z kolumną FLOAT. Starczy dobrać bowiem mniejszy/większy margines błędu epsilon i wynik znowu będzie niezgodny z oczekiwaniem. A może być jeszcze gorzej…

Kiedy słyszysz ‚WTF’ ?

 

Opisany powyżej problem będzie jeszcze bardziej widoczny, jeżeli użyjesz dodatkowo funkcji agregujących. Policzmy całkowity koszt zatrudnienia pracowników w projekcie X:

SELECT SUM(p.stawka_godzinowa * z.liczba_godzin) AS koszt_projektu
FROM Zadania AS z
JOIN Pracownik AS p ON (z.przydzielone_do = p.pracownik_id);

W powyższym zapytaniu wszystkie błędy wynikające z zaokrągleń i sposobu reprezentacji liczb w bazie, będą się kumulować. Teoretycznie błąd nie będzie duży, ale wielokrotność tej operacji będzie pogłębiać problem. Weźmy na przykład proste mnożenie 1 * 1.0 – matematycznie zawsze będzie równe  jedności, niezależnie od tego, ile razy tą operację wykonamy. A co się stanie, kiedy użyjemy FLOAT? Nasze mnożenie przyjmie (rzeczywistą) postać 1 * 0.9999… . Starczy odrobina matematyki na poziomie podstawówki, żeby dojść do wniosku, że każde ponowne wykonanie działania będzie zmieniać wynik na mniejszy – już tysiąckrotne powtórzenie sprowadzi go z oczekiwanego 1.0 do 0.3677 !

Co mam więc zrobić?

 

Nigdy, przenigdy nie używać FLOAT, jeżeli nie jesteś przekonany, że tego typu danych potrzebujesz (a może być przecież taka sytuacja kiedy precyzja wyniku nie jest wymagana). Użyj zamiast niego jednego z typów: DECIMAL, NUMBER, NUMERIC – w zależności co oferuje twoja baza danych. Jak to zrobić? Zdefiniuj swoją kolumnę jako:

ALTER TABLE Pracownik ADD COLUMN stawka_godz NUMERIC (10, 2);

Daje Ci to precyzję rzędu 10 cyfr znaczących i 2 po przecinku. Oczywiście możesz ten format zdefiniować inaczej, jeżeli potrzebujesz mniejszej / większej dokładności.

SELECT stawka_godz * 1000000000 from Pracownik where pracownik_id = 100;
-- Zwraca: 39950000000

Leave a comment