Rounding, FLOAT vs DECIMAL

Database

Rounding, FLOAT vs DECIMAL

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.

 

Thoughts?

 

2 REPLIES
Teradata Employee

Re: Rounding, FLOAT vs DECIMAL

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

Junior Contributor

Re: Rounding, FLOAT vs DECIMAL

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.

http://floating-point-gui.de/

https://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

 

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.