UDF: drop function restrict|cascade does not work as designed

[Weishiun Tsai]

With the original design of UDF, drop function restrict is supposed to return an error if there is an object referencing the UDF. On the other hand, drop function cascade is supposed to drop the object. But as shown here in the 1st part of the output, drop function myudf restrict fails to return an error even when the view myview references the UDF. The 2nd part of the output shows that drop function myudf cascade fails to drop the view myview. The view myview can still be invoked. (There is a different LP Bug report https://bugs.launchpad.net/trafodion/+bug/1421007 documenting the other fact that the UDF remains accessible after it has been dropped.)

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 2 lines ‘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.
>>create view myview as select myudf(a) from mytable;

--- SQL operation complete.
>>drop function myudf restrict;

--- SQL operation complete.
>>select * from myview;

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

          1

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

--- SQL operation complete.
>>
>>create schema mytest1;

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

--- 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.
>>create view myview as select myudf(a) from mytable;

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

--- SQL operation complete.
>>select * from myview;

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

          1

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

--- SQL operation complete.

[Cliff Gray (cliff-gray)]

Fixed as of change 1296.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0324 build installed on a workstation. The 2 particular problems mentioned in the bug report have been fixed. However, a new problem shows up now. Notice that drop schema cascade fails with error 1069 in the first part of the output. Since it is a different problem, we will close this case as 'fix released' and open a new one for the schema problem:

>>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.
>>create view myview as select myudf(a) from mytable;

--- SQL operation complete.
>>drop function myudf restrict;

*** ERROR[1047] Request failed. Dependent view TRAFODION.MYTEST.MYVIEW exists.

--- SQL operation failed with errors.
>>select * from myview;

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

          1

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

*** ERROR[1069] Schema TRAFODION.MYTEST could not be dropped.

--- SQL operation failed with errors.
>>
>>create schema mytest1;

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

--- 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.
>>create view myview as select myudf(a) from mytable;

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

--- SQL operation complete.
>>select * from myview;

*** ERROR[4082] Object TRAFODION.MYTEST1.MYVIEW does not exist or is inaccessible.

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

>>drop schema mytest1 cascade;

--- SQL operation complete.