Question Re: Cannot create scalar function with SUBSTR_REGEXPR in Technology Q&A

Cannot create scalar function with SUBSTR_REGEXPR

roland_bouman — Wed, 19 Oct 2016 08:51:52 GMT

Hi all,

when I run this create function statement:

create function f_test_regexp(
  p_txt      NVARCHAR(5000)
, p_pattern  NVARCHAR(64)
)
returns found NVARCHAR(5000)
as
begin
    found := substr_regexpr(:p_pattern in :p_txt);
end;

I get this error message:

Started: 2016-10-19 10:25:34
Could not execute 'create function f_test_regexp( p_txt NVARCHAR(5000) , p_pattern NVARCHAR(64) ) returns found ...' in 64 ms 267 µs . 
SAP DBTech JDBC: [8]: invalid argument: exception: CompilationFailedException: No details
Program : 

#pragma function "\"NLRBVD\".\"F_TEST_REGEXP\""
export Void main(NullString "P_TXT" sP_TXT, NullString "P_PATTERN" sP_PATTERN, NullString "FOUND" & sFOUND)
{
    RSHeap heap = RSHeap();
    RSEnv env = RSEnv(heap);
    Int32 ucnt = 0;
    // scalar input parameter: P_TXT
    RSNString vP_TXT = RSNString( sP_TXT , env );
    // scalar input parameter: P_PATTERN
    RSNString vP_PATTERN = RSNString( sP_PATTERN , env );
    RSNString vFOUND = expr::null_<RSNString>(); // scalar output parameter
    RSNString vFOUND___2; // FOUND_2: intermediate def-variable
    #pragma location " line 8 col 5 (at pos 129)"
    {
        RSNString tFOUND = expr::substr_regex_<RSNString>(env,vP_TXT,vP_PATTERN,expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(0),expr::typecast_<RSNString>(expr::constructor_<RSString>("",0)));
    tFOUND.checkLengthLEAndThrow(5000z);
        vFOUND = tFOUND;
    }
    sFOUND = NullString(vFOUND);
    return;
}
line 16 ("NLRBVD"."F_TEST_REGEXP": line 8 col 5 (at pos 129)): Error: Function 'expr::substr_regex_<RSNString>(RSEnv, RSNString, RSNString, RSInteger, RSInteger, RSInteger, RSNString)' not found
line 16 ("NLRBVD"."F_TEST_REGEXP": line 8 col 5 (at pos 129)): RSNString tFOUND = expr::substr_regex_<RSNString>(env,vP_TXT,vP_PATTERN,expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(0),expr::typecast_<RSNString>(expr::constructor_<RSString>("",0)));
                                                                                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

But when I rewrite it like this:

create function f_test_regexp(
  p_txt      NVARCHAR(5000)
, p_pattern  NVARCHAR(64)
)
returns found NVARCHAR(5000)
as
begin
    select substr_regexpr(:p_pattern in :p_txt)<br>    into found<br>    from dummy;
end;

I only get a warning:

java.sql.SQLWarning: Not recommended feature: Using SELECT INTO in Scalar UDF

but the function seems to work fine.

So, how come? SAP/HANA, have you been drinking?

Re: Cannot create scalar function with SUBSTR_REGEXPR

lbreddemann — Thu, 20 Oct 2016 03:53:48 GMT

Ok, SQL Script is not a superset of SQL, which also means that not all SQL functions will just work in SQL Script statements (like the assignment you used).

SELECT however is a supported command and it provides the SQL context which in turn gives access to all SQL functions.

The warning really is just that: a warning about possible unacknowledged side effects (worse performance and higher resource consumption in this case).

Re: Cannot create scalar function with SUBSTR_REGEXPR

roland_bouman — Thu, 20 Oct 2016 13:04:28 GMT

Hi Lars,

Thanks for taking the time to respond and to share your insights.

I understand the point you are making. I guess I would expect a more descriptive error message.

It's also quite different from what I am used to of procedural extensions in other RDBMS-es - there, calling built-in scalars inside procedural code is typically transparent. And in HANA too, for example, SUBSTR gives none of these issues. To me, SUBSTR_REGEXPR is more or less a fancy SUBSTR, It does not strike me as intuitive that it would behave so differently.

Re: Cannot create scalar function with SUBSTR_REGEXPR

lbreddemann — Thu, 20 Oct 2016 13:08:12 GMT

I agree. It's not intuitive.

Re: Cannot create scalar function with SUBSTR_REGEXPR

RichHeilman — Thu, 20 Oct 2016 13:51:14 GMT

Development is aware of this particular issue and is working to implement a solution in an upcoming release.

Cheers,

Rich Heilman

SAP HANA Product Management

Re: Cannot create scalar function with SUBSTR_REGEXPR

roland_bouman — Thu, 20 Oct 2016 14:36:57 GMT

Thank you! Much obliged.

Re: Cannot create scalar function with SUBSTR_REGEXPR

roland_bouman — Sun, 23 Oct 2016 13:08:02 GMT

Hi Rich,

sorry to bother you again.

I'm assuming you and the dev team is already aware of it, but just to be clear: other %_REGEXPR functions suffer from the same issue. It'd be great if all of the REGEXPR functions would be transparently available in sqlscript.

BTW - I realize you might not have this info at this point, but if it is: can you perhaps give me an indication in which future version the fix will be available? If not, what method would you recommend for me to stay up to date and find out when this will be fixed?

Best regards,

Roland.

Re: Cannot create scalar function with SUBSTR_REGEXPR

RichHeilman — Tue, 25 Oct 2016 20:36:16 GMT

We do rollout blogs for every SPS around the time of the release. Features like this would be included in that rollout. I do not have any information as to when these particular issues will be resolved.

Cheers,

Rich Heilman