SAP HANA PAL Search table error: "Column doesn't exist"

[karolina_dymek]

Hello Community! 😄

When I was working with SAP HANA Predictive Analysis Library (SAP HANA 2.0 SPS05), I noticed an error in the procedure _SYS_AFL.PAL_LINEAR_REGRESSION_PREDICT. I’m wondering if any of you also got it.

According to the documentation columns of the parameter table are: „PARAM_NAME”, „INT_VALUE”, „DOUBLE_VALUE” and „STRING_VALUE”. Indeed, a table with such columns usually works and results are calculated correctly:

However, sometimes I get an error "Column ':PARAM_NAME' does not exist":

The same error is raised when the procedure is called with Python hana_ml library, but the names of columns that “do not exist” are different (without colons):

ERROR:hana_ml.algorithms.pal.linear_model:(256, 'sql processing error: "KDYMEK"."(DO statement)": line 13 col 1 (at pos 1233): search table error: _SYS_AFL.AFLPAL:FORECASTWITHLR_ANY: [102] (range 1) TypeMismatch exception: Column \'PARAM_NAME\' does not exist')
Traceback (most recent call last):
File "C:\Users\kdymek\AppData\Local\Programs\Python\Python310\lib\site-packages\hana_ml\algorithms\pal\linear_model.py", line 1069, in predict
self._call_pal_auto(conn,
File "C:\Users\kdymek\AppData\Local\Programs\Python\Python310\lib\site-packages\hana_ml\algorithms\pal\pal_base.py", line 859, in _call_pal_auto 
self.execute_statement = call_pal_auto_with_hint(conn_context,       
File "C:\Users\kdymek\AppData\Local\Programs\Python\Python310\lib\site-packages\hana_ml\algorithms\pal\pal_base.py", line 1365, in call_pal_auto_with_hint
 if try_exec(cur, sql, conn):
File "C:\Users\kdymek\AppData\Local\Programs\Python\Python310\lib\site-packages\hana_ml\algorithms\pal\pal_base.py", line 1323, in try_exec      
cur.execute(sql)
hdbcli.dbapi.Error: (256, 'sql processing error: "KDYMEK"."(DO statement)": line 13 col 1 (at pos 1233): search table error: _SYS_AFL.AFLPAL:FORECASTWITHLR_ANY: [102] (range 1) TypeMismatch exception: Column \'PARAM_NAME\' does not exist')

Workaround:

1. renaming columns of the parameter table: ":PARAM_NAME, ":INT_VALUE", ":DOUBLE_VALUE", ":STRING_VALUE" (with colons) – doesn’t work in Python,
2. or running the code once again as the error isn’t raised every time.

Do you know what causes this problem and how I can avoid it?

[ChristophMorgen]

Hello @karolina_dymek , 

the pattern of error you experience I noted often caused by use of local temporary tables, and possible connect-session disconnect. If that's the case please verify if the local temporary table and corresponding mentioned column name still exists in the  EXACT the same connection/session where the PAL procedure is called. Since local temporary table is bound to session, once the session is gone, so does the local temporary table.

Happy to further help, please reach out!

Best regards, 

Christoph