Drop library cascade and drop schema cascade fail to drop UDFs

[Weishiun Tsai]

This bug report documents 2 problems that are somewhat related:

(1) Drop library cascade does not drop UDFs in the library automatically. It returns error 1366 complaining that dependent procedures exist.
(2) Drop schema cascade does not drop libraries or UDFs. It does not return any error, but both libraries and UDFs created in that schema remain in the _MD_ tables afterwards.

These 2 problems have made writing tests for UDF difficult as the tests have to keep track of the libraries and UDFs created during the testing and drop them individually afterwards. This is seen on the v1115_0830 build installed on a workstation.

To reproduce this problem, you need to install UDF first by following the instructions bellow:

(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 2nd line ‘create library myudflib file '<mydir>'; in mytest.sql and fill in <mydir>
(4) From sqlci, obey mytest.sql

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

Here is the execution output of mytest.sql showing that drop library cascade returns error 1366 and drop schema cascade has no effect. Queries to the _MD_ tables show both the library and the procedure after 'drop schema cascade'.

>>obey mytest.sql;
>>set schema MYUDFTEST;

--- SQL operation complete.
>>
>>create library myudflib file '<mydir>’;

--- SQL operation complete.
>>
>>create function MY_ENCRYPT_C
+>(INVAL varchar(32))
+>returns (OUTVAL varchar(32))
+>language c
+>parameter style sql
+>external name 'my_encrypt_c'
+>library myudflib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>drop library MYUDFTEST.myudflib cascade;

*** ERROR[1366] Request failed. One or more dependent procedures exist.

--- SQL operation failed with errors.
>>drop schema MYUDFTEST cascade;

--- SQL operation complete.
>>
>>create table my_tmp_schema.UID (object_uid largeint no default not null not droppable primary key);

--- SQL operation complete.
>>insert into my_tmp_schema.UID (select OBJECT_UID from "_MD_".OBJECTS where SCHEMA_NAME='MYUDFTEST');

--- 2 row(s) inserted.
>>select LIBRARY_UID, CAST(LIBRARY_FILENAME as CHAR(40)) from "_MD_".LIBRARIES where LIBRARY_UID in (select * from my_tmp_schema.UID);

LIBRARY_UID (EXPR)
-------------------- ----------------------------------------

  123337000166723502 /opt/home/wtsai/udf_bug1

--- 1 row(s) selected.
>>select USING_LIBRARY_UID, USED_UDR_UID from "_MD_".LIBRARIES_USAGE where USING_LIBRARY_UID in (select * from my_tmp_schema.UID) or USED_UDR_UID in (select * from my_tmp_schema.UID);

USING_LIBRARY_UID USED_UDR_UID
-------------------- --------------------

  123337000166723502 123337000166725900

--- 1 row(s) selected.
>>select UDR_UID, CAST(EXTERNAL_NAME as CHAR(40)), LIBRARY_UID from "_MD_".ROUTINES where UDR_UID in (select * from my_tmp_schema.UID) or LIBRARY_UID in (select * from my_tmp_schema.UID);

UDR_UID (EXPR) LIBRARY_UID
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------

  123337000166725900 my_encrypt_c 123337000166723502

--- 1 row(s) selected.
>>drop table my_tmp_schema.UID cascade;

--- SQL operation complete.
>>
>>drop function MYUDFTEST.MY_ENCRYPT_C cascade;

--- SQL operation complete.
>>drop library MYUDFTEST.myudflib cascade;

[Cliff Gray (cliff-gray)]

Fix committed in 954.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0117 build. This problem is now fixed:

================================
===== drop library cascade =====
================================

>>create schema MYUDFTEST;

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

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

--- SQL operation complete.
>>
>>create library myudflib file '/opt/home/trafodion/v0117/sql/scripts/udf_bug1';

--- SQL operation complete.
>>
>>create function MY_ENCRYPT_C
+>(INVAL varchar(32))
+>returns (OUTVAL varchar(32))
+>language c
+>parameter style sql
+>external name 'my_encrypt_c'
+>library myudflib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>drop library MYUDFTEST.myudflib cascade;

--- SQL operation complete.
>>-- drop schema MYUDFTEST cascade;
>>
>>create table my_tmp_schema.UID (object_uid largeint no default not null not droppable primary key);

--- SQL operation complete.
>>
>>insert into my_tmp_schema.UID (select OBJECT_UID from "_MD_".OBJECTS where SCHEMA_NAME='MYUDFTEST');

--- 1 row(s) inserted.
>>
>>select LIBRARY_UID, CAST(LIBRARY_FILENAME as CHAR(40)) from "_MD_".LIBRARIES where LIBRARY_UID in (select * from my_tmp_schema.UID);

--- 0 row(s) selected.
>>
>>select USING_LIBRARY_UID, USED_UDR_UID from "_MD_".LIBRARIES_USAGE where USING_LIBRARY_UID in (select * from my_tmp_schema.UID) or USED_UDR_UID in (select * from my_tmp_schema.UID);

--- 0 row(s) selected.
>>
>>select UDR_UID, CAST(EXTERNAL_NAME as CHAR(40)), LIBRARY_UID from "_MD_".ROUTINES where UDR_UID in (select * from my_tmp_schema.UID) or LIBRARY_UID in (select * from my_tmp_schema.UID);

--- 0 row(s) selected.
>>
>>drop table my_tmp_schema.UID cascade;

--- SQL operation complete.
>>
>>drop function MYUDFTEST.MY_ENCRYPT_C cascade;

*** ERROR[1389] Object TRAFODION.MYUDFTEST.MY_ENCRYPT_C does not exist in Trafodion.

--- SQL operation failed with errors.
>>
>>drop library MYUDFTEST.myudflib cascade;

*** ERROR[1389] Object TRAFODION.MYUDFTEST.MYUDFLIB does not exist in Trafodion.

--- SQL operation failed with errors.
>>
>>drop schema MYUDFTEST cascade;

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

--- SQL operation complete.

===============================
===== drop schema cascade =====
===============================

>>obey mytest1.sql;
>>create schema MYUDFTEST;

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

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

--- SQL operation complete.
>>
>>create library myudflib file '/opt/home/trafodion/v0117/sql/scripts/udf_bug1';

--- SQL operation complete.
>>
>>create function MY_ENCRYPT_C
+>(INVAL varchar(32))
+>returns (OUTVAL varchar(32))
+>language c
+>parameter style sql
+>external name 'my_encrypt_c'
+>library myudflib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>-- drop library MYUDFTEST.myudflib cascade;
>>drop schema MYUDFTEST cascade;

--- SQL operation complete.
>>
>>create table my_tmp_schema.UID (object_uid largeint no default not null not droppable primary key);

--- SQL operation complete.
>>
>>insert into my_tmp_schema.UID (select OBJECT_UID from...