Samstag, 19. Dezember 2009

Aggregatfunktionen und der Datentyp FLOAT

Bereits vor einer Weile bin ich auf ein sehr spezielles Problem bei der Verwendung von FLOAT Werten bei der Berechnung von Aggregaten gestoßen (siehe auch hier: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=465147).

Das folgende Experiment zeigt noch einmal, worum es dabei geht.
Wir legen zunächst eine kleine Testtabelle an:

use tempdb
go

-- Testtabelle anlegen
if (object_id('t1') is not null)
drop table t1
go
create table T1
(
x nchar(200) not null default '#'
,c1 float not null
,c2 decimal(38,5)
)
go


Die Tabelle hat zwei numerische Spalten, eine vom Typ FLOAT und eine weitere vom Typ DECIMAL. In beide Spalten tragen wir dieselben Werte ein. Insgesamt werden durch das folgende Skript 400.000 Zeilen eingefügt:

-- 200.000 Zeilen in die Testtabelle eintragen
declare @x decimal(38,5)
set @x = 1000000000000000.91238
insert T1(c1,c2)
select @x, -@x
from (select row_number() over(order by current_timestamp) as rn
from sys.trace_event_bindings as a
,sys.trace_event_bindings as b) as rd
where rn <= 200000


-- Noch einmal 200.000 Zeilen.
-- Diesmal mit umgekehrten Vorzeichen
insert T1(c1,c2)
 select -@x, @x from
  (select row_number() over(order by current_timestamp) as rn 
     from sys.trace_event_bindings as a ,sys.trace_event_bindings as b) as rd where rn <= 200000
go

Ok, es existieren nun also 400.000 Zeilen. Bilden wir die Summe für die Spalten c1 und c2 über alle Zeilen, dann sollte der Wert 0 herauskommen, nicht wahr? Hier ist die entsprechende Abfrage:

-- Beide Summen sollten den Wert 0 liefern
select sum(c1) as SumFloat,sum(c2) as SumDecimal from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1
union all
select sum(c1),sum(c2) from T1


Und hier ist das erstaunliche Ergebnis:


Die Summe über den DECIMAL Wert liefert das erwartete Resultat – nämlich den Wert 0. Für den FLOAT Wert sieht es nicht ganz so gut aus. Nicht nur, dass das Resultat erheblich abweicht (dies ist auf Rundungsfehler bei der Gleitkommaarithmetik zurückzuführen), nein: Der Wert ändert sich auch von Ausführung zu Ausführung! Das Ergebnis ist also nicht deterministisch und das ist schon sehr erstaunlich, nicht wahr?
Der Effekt ist darauf zurückzuführen, dass die Abfrage parallel ausgeführt wird. Hier ist der Ausführungsplan:


Die Daten aus der Tabelle werden also in mehreren Threads gelesen, die dann irgendwann wieder zu einem Ergebnis (einer Summe) zusammengeführt werden. Hierbei ist die Reihenfolge der Zusammenführung nicht sichergestellt. Genau dadurch sind die auftretenden Rundungsfehler bei jeder Ausführung anders. Das Kommutativgesetz der Addition gilt also nicht für den Datentyp FLOAT!

Darum: Lieber auf die Verwendung von FLOAT Datentypen verzichten! Leider gibt es Situationen, in denen dies nicht Möglich ist kann. So legt zum Beispiel SSAS für numerische Werte in sogenannten Writeback Tabellen die entsprechenden Spalten automatisch mit dem Datentyp FLOAT an. Die möglichen Folgen seht ihr im obigen Beispiel.