UDF: Lack of checking for scalar UDF input/output values

[Weishiun Tsai]

Ideally, input/output values for a scalar UDF should be verified at the create function time. But this check is not in place right now. As a result, a lot of ill-constructed input/output values are left to be handled at the run time. And the behavior at the run time is haphazard at best.

Here shows 3 examples of such behavior:

(a) myudf1 defines 2 input values with the same name. Create function does not return an error. But the invocation at the run time returns a perplexing 4457 error indicating internal out-of-range index error.
(b) myudf2 defines an input value and an output value with the same name. Create function does not return an error. But the invocation at the run time returns a perplexing 4457 error complaining that there is no output value.
(c) myudf3 defines 2 output values with the same name. Create function does not return an error. The invocation at the run time simply ignores the 2nd output value, as well as the fact that the C function only defines 1 output value. It returns one value as if the 2nd output value was never defined at all.

This is seen on the v0407 build installed on a workstation. To reproduce it:

(1) Download the attached tar file and untar it to get the 3 files in there. Put the files in any directory <mydir>.
(2) Make sure that you have run ./sqenv.sh of your Trafodion instance first as building UDF needs $MY_SQROOT for the header files.
(3) run build.sh
(4) Change the line “create library qa_udf_lib file '<mydir>/myudf.so';”; in mytest.sql and fill in <mydir>
(5) From sqlci, obey mytest.sql

--------------------------------------------------------------------------------

Here is the execution output:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>
>>create library qa_udf_lib file '<mydir>/myudf.so';

--- SQL operation complete.
>>
>>create table mytable (a int, b int);

--- SQL operation complete.
>>insert into mytable values (1,1),(2,2),(3,3);

--- 3 row(s) inserted.
>>
>>create function myudf1
+>(INVAL int, INVAL int)
+>returns (OUTVAL int)
+>language c
+>parameter style sql
+>external name 'qa_func_int32'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>select myudf1(a, b) from mytable;

*** ERROR[4457] An error was encountered processing metadata for user-defined function TRAFODION.MYTEST.MYUDF1. Details: Internal error in setInOrOutParam(): index position out of range..

*** ERROR[8822] The statement was not prepared.

>>
>>create function myudf2
+>(INVAL int)
+>returns (INVAL int)
+>language c
+>parameter style sql
+>external name 'qa_func_int32'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>select myudf2(a) from mytable;

*** ERROR[4457] An error was encountered processing metadata for user-defined function TRAFODION.MYTEST.MYUDF2. Details: User-defined functions must have at least one registered output value.

*** ERROR[8822] The statement was not prepared.

>>
>>create function myudf3
+>(INVAL int)
+>returns (OUTVAL int, OUTVAL int)
+>language c
+>parameter style sql
+>external name 'qa_func_int32'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>select myudf3(a) from mytable;

OUTVAL
-----------

          1
          2
          3

--- 3 row(s) selected.
>>
>>drop function myudf1 cascade;

--- SQL operation complete.
>>drop function myudf2 cascade;

--- SQL operation complete.
>>drop function myudf3 cascade;

--- SQL operation complete.
>>drop library qa_udf_lib cascade;

--- SQL operation complete.
>>drop schema mytest cascade;

--- SQL operation complete.