How does HANA resolve input parameters in calculation views executed in the SQL Engine?

[daniel_mangold]

Hello,

In our project, we use HANA stored procedures called from ABAP using DB proxy (this is from a time where AMDP was not yet available).

However, we previously refactored the procedures in a way that all (mass) data centric operations  (read, convert and further process data) are done inside using graphical calculation views  which are executed in the SQL engine.

Now, with the same version of the HANA server we experience that there is a difference if we execute the procedure from ABAP versus executing it on a standalone HANA server.

In detail, executing the same code in the standalone HANA server is successful whereas executing it from ABAP runs into an error:

[6608]{236093}[39/391650361] 2015-07-02 11:13:08.217560 e cePlanExec       cePlanExecutor.cpp(07222) : Error during Plan execution of model _SYS_BIC:_SYS_SS_CE_232022928_139889446946000_2_INS (-1), reason: "_SYS_BIC"."sap.is.ddf.udf/SP_SWP_MODEL": line 51 col 5 (at pos 1692): [2048] (range 3): column store error:  [2048] "_SYS_BIC"."sap.is.ddf.udf/SP_SW_MODEL": line 36 col 5 (at pos 1009): [2048] (range 3): column store error:  [2048] "_SYS_BIC"."sap.is.ddf.udf/SP_SW_MODEL_POS": line 43 col 5 (at pos 1961): [2048] (range 3): column store error: search table error:  [6968] Evaluator: syntax error in expression string;expected TK_RPAREN,parsing 'longdate(2013-07-31 [here]23:59:59.0000000)'Please check lines: 59

It does not look so but the error actually occurs in a graphical calculation view executed from a procedure.

The root cause is that only the standalone server accepts a calculated column of type TIMESTAMP containing the input parameter placeholder of type timestamp: $$P_TimestampFrom$$. The fix was to change $$P_TimestampFrom$$ to '$$P_TimestampFrom$$'.

Now the questions:

1) Why does it sometimes work without single quotes and sometimes not?

    In Detail: What is different when we call procedures via DB proxy from ABAP versus standalone HANA server?

2) How do input parameters - where their placeholders are used - get resolved in graphical calculation views with enforced execution in the SQL engine?

-> We were concerned about too many conversions which would badly influence performance with mass data.

We would like to avoid that the timestamp input parameter is converted to a string because of the quotes: '$$P_TimestampFrom$$' and then converted back to timestamp.

We previously assumed this would not be the case without the single quotes. However, the assumption seems to be wrong anyway as there seem to be implicit conversions when the graphical calculation view is converted into SQL.

Are any input parameters always converted into strings when the placeholders ($$P_TimestampFrom$$) are used, independent of their defined type?

Is there any detailed explanation how those input parameters are resolved in filters or calculated columns (SQL Execution!) ?

Thanks,

Daniel

[lbreddemann · Accepted solution]

Daniel

as you are a SAP employee you do have access to the SAP HANA developers and their information - I can only recommend to reach out to them in cases like this.

Generally there shouldn't be a change in how the parameters work from ABAP or from SQL. As we don't know your code it's hard to pinpoint the actual reason for this different behaviour.

Concerning the parameters in information models: yes, these are always handed over as strings during the model instantiation (when you execute the model).

A simply precaution for too many implicit type casts is to explicitly cast the data type when using the parameter in your model. Then there will be just a single conversion and you shouldn't actually face any performance difference.

Concerning the data type definition for the parameters: these are aimed towards the front-end of the model. When you put in a date, it will show the date picker and accept date as input.

But it will still be handed down to the model execution as a string representation of your input.

That's at least my perspective on things here. As the internals of the calc. engine working are not externally documented, the SAP HANA core developers are the people you want to talk to.

- Lars

[daniel_mangold · Accepted solution]

Hi Lars,

I summerize:

An input parameter is handed over as string independent of the data type selection in HANA Studio.

So there is no difference in having string $$P_TimestampFrom$$ or '$$P_TimestampFrom$$' in the calculated column. As the string without single quotes does not work in some cases (will reach out to HANA develoers), the recommendation is to always use the single quotes.

Thanks and best regards,

Daniel

[lbreddemann · Accepted solution]

Well "the recommendation" is a bit too official - it's just what I think

But other than that: yep!