I am observing different rounding behaviors for DECIMAL and FLOAT data types on our Teradata 15.0 system.
Here is a test query:
sel cast(cast(37.915 as float) as decimal(10,2)) a,
cast(cast(37.915 as decimal(10,3)) as decimal(10,2)) b;
The results I get are:
a = 37.91
b = 37.92
In our environment, we have this DBS control parameter set:
RoundHalfwayMagUp = FALSE
The test result for B is expected, but not A. Is FLOAT rounding in Teradata different from DECIMAL rounding?
I can't reduce it to a simple test case, but I am also observing a query that has multiple expressions and aggregations, with both DECIMAL and FLOAT datatypes, and a narrowing cast to a DECIMAL, return results that are 0.01 different on successive runs of the exact same query (with no changes to the underlying data). I am having a hard time with this, as I believe database queries should be deterministic. It is as if the query sometimes goes down the (above) decimal to decimal conversion and at other times down the float to decimal conversion.
Odd - even the round() function handles these differently. I don't know why...
sel round(cast(37.915 as float),2) a, round(cast(37.915 as decimal(10,3)),2) b;
3.79100000000000E 001 37.920
This is probably due to the fact that the FLOAT datatype is not exact numeric :-)
SELECT Cast(Cast(37.915 AS FLOAT) AS DECIMAL(38,36)); 37,914999999999999147348717087879776955 SELECT Cast(Cast(0.1 AS FLOAT) + Cast(0.2 AS FLOAT) AS DECIMAL(38,37)) 0,3000000000000000444089209850062616169
There are lots of resource on that topic, e.g.
Regarding different results running the same query multiple times, there's no guarantee that rows are processed in exactly the same order and summing the same FLOATs in different order might return slightly different results.