Using new features not supported?!

A recent question in the HANA Q&A forum brought up an error message that I recently encountered myself.

Modification of table variable is not supported in current SQLScript configuration

The way to produce this error is to

• use a SAP HANA 2 SPS 03 system,
• model a graphical calculation view,
• include a table functions in said calc. view
  and then
• modify an array or table variable.

All in all, this is not a ridiculous list of preconditions, especially if you follow the current modelling advice about table functions and calc. views.

Looking for the exact error message does not yield any result (except for the question that now includes my answer) but there is another SAP note that looks very much related:
2857606 – Error “MAP_MERGE operator is not supported in current SQLScript configuration when running a calculation view”.

What causes this issue

The gist of the issue is that since HANA 1 SPS 09 or so, HANA has the option to try and unroll calculation views into a SQL equivalent query structure. Doing this allows for using the same overall query optimizer and rewriting functions for all sub-calc-views in a bigger query which then opens up the potential for better optimization.
Without this feature, the optimizer more or less has to cope with one calc-view at a time and cannot look across the whole query.

So, instead of silently stopping the unfolding and keeping the “non-unfoldable” table functions as is in the query plan (which is what HANA 2 SPS 04 seems to be doing – there is no error for the same scenario), HANA 2 SPS 03 spits out the error message when activating or running the view.

The solution, err, workaround

The workaround to this, mentioned in the SAP note, is to use a HINT that instructs HANA to, well, silently stop the unfolding and to keep the “non-unfoldable” table functions as is…

That hint is :

NO_CALC_VIEW_UNFOLDING()

It’s probably worth noting that this hint actually takes parameters to target specific calculation views in a SQL query:

WITH HINT( NO_CALC_VIEW_UNFOLDING(<the name of the fancy table function>) )

Out of the frying pan, into the fire

Using this hint does away with the error message but introduces the same technical debt that all hint-based solutions bring with them.

Sometime in the future, with a newer HANA version (say HANA 2 SPS 04) the hint becomes unnecessary and effectless at best.
But it could also lead to problems when it blocks features that would allow the view to work well in a newer HANA version or even lead to new errors.

On top of that, it’s just more code that requires some form of documentation (at least as comments either in the table function or the calc view) to explain why this hint is there and what it is supposed to achieve.

Going back and finding all the places you used this hint in the future will be rather painful, since there is no easy global “review, disable and remove outdated hints” facility in HANA.

There you go, now you know.