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
- modify an array or table variable.
All in all, this is not a ridiculous list of preconditions, especially if you follow the current modeling 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.
SAP Infos about this
- 1857202 – SQL Execution of calculation views
- 2223597 – Implicit SQL optimization of SAP HANA Calculation Views
- 2291812 – SAP HANA DB: Disable/Enable CalculationEngine Feature – CalcView Unfolding
- 2618790 – Graphical View Modeling in SAP HANA – How to Avoid Unexpected Results
- When to use Execute in SQL Engine for Calculation Views
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 :
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 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.