Looks like Bug: Two calculated column with same formula and logic return different value.

[param0308]

HI HANA EXPERTS,

We have two calculation view with the same structure. And we have one calculated column in each view, with same logic and formula.

But in output calculated column value is different for both the views.(Please see the attachment for values)

View structure.:

View_1
Column name	col1(varchar(4))	col2(double)	col3(real)	col4(decimal(13,2))
SOURCE	Direct from Table	col3 * col4	Direct from table	Direct from table
View_2
Column name	col1(varchar(4))	col2(double)	col3(real)	col4(decimal(13,2))
SOURCE	Direct from Table	col3 * col4	Direct from table	Direct from table

*Real values are attached as a picture.

I ALSO KNOW

REAL and DOUBLE types are stored in the system using binary numbers. The fractional part of these numbers is represented using a combination of 1/2, 1/4, 1/8, 1/16, and so on. For this reason they cannot completely represent rational numbers with fractional digits. 0.1 for example cannot be represented exactly by combining these binary fractions. In this case, you will obtain inaccurate results when using the DOUBLE or REAL type. This is not an issue with the SAP HANA database. It is the correct behavior for these data types. For a demonstration of this property in action, please see the example below.

 SELECT TO_DOUBLE(0.1) + TO_DOUBLE(4.6) AS DOUBLE_SUM FROM DUMMY;  DOUBLE_SUM 4.6999999999

I agree with the above statement but why different value for the same calculation in two different view.

To solve this mystery I did the same thing in SQL query.

select to_double(round(TO_REAL(380.222) * to_decimal(723213.8),2)) as souble_sum from dummy;

select to_double(round(TO_REAL(380.222) * to_decimal(723213.8),2)) as souble_sum from dummy;

Guess what for both the select, Value is same 274981792 , but doesn't match to any of the view's value.(which is 274982007.22 for 1st view and 274981985.15 for 2nd view)

Ok if it work like this only please provide me the solution , So I can get same set of values in both view.

Looking forward for real solution.

[lbreddemann]

Is one of the calcviews flagged for SQL Engine execution and the other not?

[param0308]

Hi Lars,

Where I can find out that flag in calculation view.

I know about sql expressions like for normal addition we can use sqladd() function.

[lbreddemann]

Depends on your HANA version.

But when you select the semantics node and check the properties list, it should be in there.

- Lars

[param0308]

Hi,

It's empty for both views.

[lbreddemann]

For the sake of efficient root cause analysis, please open a support incident for this.

I don't think that this is going to be straight-forward to analyse here in the discussion forum.

- Lars