Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question