[jira] [Created] (TRAFODION-771) LP Bug: 1393888 - Drop library cascade and drop schema cascade fail to drop UDFs

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-771:
------------------------------------

             Summary: LP Bug: 1393888 - Drop library cascade and drop schema cascade fail to drop UDFs
                 Key: TRAFODION-771
                 URL: https://issues.apache.org/jira/browse/TRAFODION-771
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Cliff Gray
            Priority: Critical
             Fix For: 1.0 (pre-incubation)


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;



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)