Cannot create scalar function with SUBSTR_REGEXPR

[roland_bouman]

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?

[RichHeilman · Accepted solution]

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

[roland_bouman · Accepted solution]

Thank you! Much obliged.

[roland_bouman · Accepted solution]

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.

[RichHeilman · Accepted solution]

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

[lbreddemann]

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).

[lbreddemann]

I agree. It's not intuitive.

[roland_bouman]

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.