SQL HANA : Double data type - Real Behavior

[kgaurav2k14]

Hi Friends,

I am trying to understand when to use decimal over double data type and vice versa in HANA SQL.

According to SAP in below link, it says that the fraction part of the floating point numbers is represented using a combination of 1/2, 1/4, 1/8, 1/16, and so on.

SQL HANA - help.sap.com - Data Types

For this reason, they cannot completely represent rational numbers with fractional digits. For example, 0.1 cannot be represented exactly by combining these binary fractions. In this case, you obtain inaccurate results when using a floating-point data type. This is the correct behavior for these data types. The following example demonstrates the behavior and returns 4.6999999999 for DOUBLE_SUM:

SELECT TO_DOUBLE(0.1) + TO_DOUBLE(4.6) AS DOUBLE_SUM FROM DUMMY;
= 4.699999999999999
(Abnormal)

This gives us result as : 4.699999999999999 instead of 4.7

I tried very hard to understand this behavior, does it mean:

1/2 = 0.5 , 1/4 = 0.25 , 1/8 = 0.125 , 1/16 = 0.0625 , 1/32 = 0.03125 , 1/64 = 0.015625 , 1/128 = 0.0078125 ......

These combination of fraction part of above mentioned numbers can only be represented in floating point numbers(Double in HANA SQL)?

If that is true then even the below mentioned two queries should also behave the same and give us wrong results. However, as shown below, it is behaving fine. Here, 0.2 is also not allowed fraction, the smallest fraction is 0.5 as I have shown in above statement, but it gives us right result which is 4.8 :

SELECT TO_DOUBLE(0.2) + TO_DOUBLE(4.6) AS DOUBLE_SUM FROM DUMMY;
= 4.8
(Expected Result)

SELECT TO_DOUBLE(0.4) + TO_DOUBLE(4.6) AS DOUBLE_SUM FROM DUMMY;
= 5.0
(Expected Result)

However , if I execute with 0.3, it again misbehave.

SELECT TO_DOUBLE(0.3) + TO_DOUBLE(4.6) AS DOUBLE_SUM FROM DUMMY;
= 4.8999999999999995
(Abnormal)

Can somebody explain this behavior of Double in SQL HANA? What do we mean by combination of these binary fractions is only allowed?

Thanks,

Gaurav

Version - HDB 2.0 SP3

[Former Member]

@kgaurav2k14 where you ever able to find an answer? And is no body else having this problem? Seems like this should be occuring quiet often with any SAP HANA database who is working with doubles and doing basic math.

[baiming_gao]

This is very interesting. I also did a test with the 3 calculations provided by Gaurav, but differently:

CREATE COLUMN TABLE "TEST" ("A" NVARCHAR(1), "X" DOUBLE, "Y" DOUBLE);

INSERT INTO "TEST" VALUES('1', 0.2, 4.6);

INSERT INTO "TEST" VALUES('2', 0.3, 4.6);

INSERT INTO "TEST" VALUES('3', 0.2, 4.8);

SELECT *, X+Y AS Z from "TEST";

I used wireshark to check data returned from HANA Server. It looks HANA is working perfectly for double data type, all the binary data are exactly expected.

But the client side ( HANA studio, eclipse, or nodejs and so on) has done conversion from Double data type to decimal for display, and obviously the result of the conversion is hard to understand ( in that if "0.3" + "4.6" gives "4.8999999999999995" (i.e., keeping 16 digits after decimal point), then "0.2" + "4.6" should better gives "4.7999999999999998" ( also keeping 16 digits after decimal point) rather than "4.8").

[Kai_Mueller]

Hello,

I'm sorry for the late reply, but I just found this question. First of all, the inaccuracy is expected with floating point numbers. This is well explained here. It's a side effect of the floating point representation of IEEE754.

So 0.1+4.6 and 0.3+4.6 are expected to show "abnormal" results. Please note, that it's NOT recommended to use floating point numbers for financial and other high precision calculations. It's a well known issue in computer engineering.

However, the result 0.2+4.6 seems weird to me. I'll follow up internally on it and will update this answer as soon as I have further information.

Best regards, Kai

EDIT:

As said, I discussed this topic internally. To our understanding this is most likely related to exponent normalization, meaning that not significate digits are erased. This is beyond the SAP HANA double type implementation and more likely related to C++ or even more down the chain. So, as far as I can tell you all scenarios noted by you have a result which is expected.