UDF: Drop schema fails when the schema has a view referencing a UDF

[Weishiun Tsai]

As shown in the execution output below. The first part using mytest1 and second part using mytest2 have similar operations. The only difference is that the second part creates a view referencing the UDF. Drop mytest1 cascade works fine when there is no view. But drop schema mytest2 cascade fails with error 1069.

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

To reproduce this problem:

(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 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:

-bash-4.1$ sqlci -i mytest.sql
Trafodion Conversational Interface 1.1.0
(c) Copyright 2014 Hewlett-Packard Development Company, LP.
>>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.
>>select myudf(a) from mytable;

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

          1

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

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

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

--- 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.
>>select * from myview;

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

          1

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

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

--- SQL operation failed with errors.
>>
>>exit;

End of MXCI Session

[Cliff Gray (cliff-gray)]

Fix delivered in change 1425.

[Weishiun Tsai (wei-shiun-tsai)]

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

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

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

          1

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

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

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

--- 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.
>>select * from myview;

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

          1

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

--- SQL operation complete.