UDF: Showddl shows wrong library name

[Weishiun Tsai]

Showddl was fixed to address the launch bug report https://bugs.launchpad.net/trafodion/+bug/1421438 ‘UDF: showddl does not work for UDFs’. It now supports both UDF and TMUDF.

However, the library field in the showddl output is wrong. It is showing the function name, instead of the library name. As shown in the following example, the library field shows TRAFODION.MYTEST.MYUDF which is really the function name. The real library name should be TRAFODION.MYTEST.QA_UDF_LIB

This is seen on the v0331 build installed on a workstation.

(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) Change the line “create library qa_udf_lib file '<mydir>/myudf.so';”; in mytest.sql and fill in <mydir>
(4) 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 function MYUDF
+>(INVAL int)
+>returns (OUTVAL int)
+>language c
+>parameter style sql
+>external name 'myudf'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

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

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

--- 1 row(s) inserted.
>>
>>select myudf(a) from mytable;

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

          1

--- 1 row(s) selected.
>>
>>get libraries;

Libraries in Schema TRAFODION.MYTEST
====================================

QA_UDF_LIB

--- SQL operation complete.
>>showddl function myudf;

CREATE FUNCTION TRAFODION.MYTEST.MYUDF
  (
    IN INVAL INTEGER SIGNED
  )
  RETURNS
  (
    OUT OUTVAL INTEGER SIGNED
  )
  EXTERNAL NAME 'myudf'
  LIBRARY TRAFODION.MYTEST.MYUDF
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  NO FINAL CALL
  STATE AREA SIZE 1024
  ALLOW ANY PARALLELISM
  DETERMINISTIC
  SAFE EXECUTION MODE
  ;

--- SQL operation complete.
>>showddl procedure myudf;

CREATE FUNCTION TRAFODION.MYTEST.MYUDF
  (
    IN INVAL INTEGER SIGNED
  )
  RETURNS
  (
    OUT OUTVAL INTEGER SIGNED
  )
  EXTERNAL NAME 'myudf'
  LIBRARY TRAFODION.MYTEST.MYUDF
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  NO FINAL CALL
  STATE AREA SIZE 1024
  ALLOW ANY PARALLELISM
  DETERMINISTIC
  SAFE EXECUTION MODE
  ;

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

--- SQL operation complete.

[Cliff Gray (cliff-gray)]

Reported problem now fixed.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0609 build installed on a workstation. This problem has been fixed:

>>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 function MYUDF
+>(INVAL int)
+>returns (OUTVAL int)
+>language c
+>parameter style sql
+>external name 'myudf'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

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

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

--- 1 row(s) inserted.
>>
>>select myudf(a) from mytable;

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

          1

--- 1 row(s) selected.
>>
>>get libraries;

Libraries in Schema TRAFODION.MYTEST
====================================

QA_UDF_LIB

--- SQL operation complete.
>>showddl function myudf;

CREATE FUNCTION TRAFODION.MYTEST.MYUDF
  (
    IN INVAL INTEGER SIGNED
  )
  RETURNS
  (
    OUT OUTVAL INTEGER SIGNED
  )
  EXTERNAL NAME 'myudf'
  LIBRARY TRAFODION.MYTEST.QA_UDF_LIB
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  NO FINAL CALL
  STATE AREA SIZE 1024
  ALLOW ANY PARALLELISM
  DETERMINISTIC
  SAFE EXECUTION MODE
  ;

--- SQL operation complete.
>>showddl procedure myudf;

CREATE FUNCTION TRAFODION.MYTEST.MYUDF
  (
    IN INVAL INTEGER SIGNED
  )
  RETURNS
  (
    OUT OUTVAL INTEGER SIGNED
  )
  EXTERNAL NAME 'myudf'
  LIBRARY TRAFODION.MYTEST.QA_UDF_LIB
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  NO FINAL CALL
  STATE AREA SIZE 1024
  ALLOW ANY PARALLELISM
  DETERMINISTIC
  SAFE EXECUTION MODE
  ;

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

--- SQL operation complete.