# Calculation View: Calculation is wrong in some cases

Hey guys,

a few days ago I noticed that some of the results I get from my OData Service are wrong.

The definition of my views is rather simple:

I have an Attribute View based on a table with one left outer and a few text joins.

On top of this attribute view there is a calculation ("CV_RESPONSETIME_BUSINESS_CASE_AFTER") view with two parameters. The calcview consists of a projection with a filter based on these parameters and the default aggregation node:

Now what I noticed is that when calling the calcview via odata with parameters "min_resptime" = 40000 and "max_resptime" = 0 I got a result of 74 for the sum of the "Value" column, although the result must have been much higher. This was confirmed by a SQL query via HANA Studio - with the same paramters I got a Sum("Value") of 7688!

Trying to find the difference, I enabled developer mode and appended "&profile=html" to the OData URL giving me the SQL Statement:

select sum("ResponseTimeBusinessCaseAfterType2"."Value") "Value" ,

sum("ResponseTimeBusinessCaseAfterType2"."ResponseTime_avg") "ResponseTime_avg" ,

sum("ResponseTimeBusinessCaseAfterType2"."Acceleration_Potential") "Acceleration_Potential"

from (select TO_BIGINT(?) "min_resptime",TO_BIGINT(?) "max_resptime" from sys.dummy) "Param2",

"_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER"(PLACEHOLDER."$$min_resptime$$" => $1,PLACEHOLDER."$$max_resptime$$" => $2) as "ResponseTimeBusinessCaseAfterType2"

where 1=1 and (("ResponseTimeBusinessCaseAfterType2"."UserType")=?)

Executing the same query in HANA Studio with same parameters still gave me the wrong result:

Next I removed the parameters and instead directly put in the values: [QUERY1]

select sum("ResponseTimeBusinessCaseAfterType2"."Value") "Value" ,

sum("ResponseTimeBusinessCaseAfterType2"."ResponseTime_avg") "ResponseTime_avg" ,

sum("ResponseTimeBusinessCaseAfterType2"."Acceleration_Potential") "Acceleration_Potential"

from (select TO_BIGINT(40000) "min_resptime",TO_BIGINT(0) "max_resptime" from sys.dummy) "Param2",

"_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER"(PLACEHOLDER."$$min_resptime$$" => 40000,PLACEHOLDER."$$max_resptime$$" => 0) as "ResponseTimeBusinessCaseAfterType2"

where 1=1 and (("ResponseTimeBusinessCaseAfterType2"."UserType")='A');

Still, wrong values:

Next, I removed the strange "Param2"-Part, which seems only to be needed to get the parameters into the prepared statement (?): [QUERY2]

select sum("ResponseTimeBusinessCaseAfterType2"."Value") "Value" ,

sum("ResponseTimeBusinessCaseAfterType2"."ResponseTime_avg") "ResponseTime_avg" ,

sum("ResponseTimeBusinessCaseAfterType2"."Acceleration_Potential") "Acceleration_Potential"

from --(select TO_BIGINT(40000) "min_resptime",TO_BIGINT(0) "max_resptime" from sys.dummy) "Param2",

"_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER"(PLACEHOLDER."$$min_resptime$$" => 40000,PLACEHOLDER."$$max_resptime$$" => 0) as "ResponseTimeBusinessCaseAfterType2"

where 1=1 and (("ResponseTimeBusinessCaseAfterType2"."UserType")='A');

And viola - now I get the correct values:

After trying some other statements I further found out that this "Param2" part is not the only case when HANA returns wrong values.

For example if I try [QUERY3]

SELECT SUM("Value") AS "Value_SUM"

FROM "_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER" ('PLACEHOLDER' = ('$$min_resptime$$', '40000'), 'PLACEHOLDER' = ('$$max_resptime$$', '0'))

WHERE "UserType" = 'A';

It gives wrong values:

But if I use the same statement and just add the two other columns from above: [QUERY4]

SELECT SUM("Value") AS "Value_SUM", SUM("ResponseTime_avg"), SUM("Acceleration_Potential")

FROM "_SYS_BIC"."ibisness.com.rbe.solution.hanaInnovation.data/CV_RESPONSETIME_BUSINESS_CASE_AFTER" ('PLACEHOLDER' = ('$$min_resptime$$', '40000'), 'PLACEHOLDER' = ('$$max_resptime$$', '0'))

WHERE "UserType" = 'A';

It is correct again:

Next I used the explain plan and visualize plan features and I can see that there are differences.

[QUERY1] (wrong):

Note: The plan goes down to the Attribute view

[QUERY2] (correct):

Note: The plan only goes down to the calculation view

[QUERY3] (wrong):

Note: The plan goes down to the Attribute view

[QUERY4] (correct):

Note: The plan only goes down to the calculation view

It seems to me that HANA is doing some "optimizations" which lead to a wrong result.

I already wrote a message via the HANA One Portal but got the response that they are only there for issues with the Portal, not with HANA itself. I have no other possibility to write a ticket since I don't have any other HANA License than HANA One. I think this is a serious issue because it could possibly happen anywhere. Could somebody of SAP HANA Team please look into this issue?

Thanks,

Fabian