UDF: UDF remains accessible after it has been dropped

[Weishiun Tsai]

As shown here in the following example, select from the UDF myudf(a) continues to work even after myudf() has been dropped. The 2nd attempt to drop the UDF returns error 1389 indicating that the UDF does not exist. But another select afterwards still is able to access the UDF. This is seen on the v0210 build installed on a workstation.

To reproduce this problem:

(1) Download the attached tar file and untar it to get the 4 files in there. Put the 4 files in any directory <mydir>
(2) Run build.sh from <mydir> to build the UDF so file.
(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:

>>obey mytest.sql;
>>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.
>>drop function myudf;

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

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

          1

--- 1 row(s) selected.
>>drop function myudf;

*** ERROR[1389] Object TRAFODION.MYTEST.MYUDF does not exist in Trafodion.

--- SQL operation failed with errors.
>>select myudf(a) from mytable;

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

          1

--- 1 row(s) selected.
>>
>>drop schema mytest cascade;

--- SQL operation complete.

[Cliff Gray (cliff-gray)]

Fixed in change 1296.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0324 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.
>>drop function myudf;

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

*** ERROR[4450] Function TRAFODION."_UDF_".MYUDF is not a built-in function or registered user-defined function.

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

>>drop function myudf;

*** ERROR[1389] Object TRAFODION.MYTEST.MYUDF does not exist in Trafodion.

--- SQL operation failed with errors.
>>select myudf(a) from mytable;

*** ERROR[4450] Function TRAFODION."_UDF_".MYUDF is not a built-in function or registered user-defined function.

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

>>
>>drop schema mytest cascade;

--- SQL operation complete.