[jira] [Created] (TRAFODION-1006) LP Bug: 1421418 - UDF: drop function restrict|cascade does not work as designed

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

Alice Chen created TRAFODION-1006:
-------------------------------------

             Summary: LP Bug: 1421418 - UDF: drop function restrict|cascade does not work as designed
                 Key: TRAFODION-1006
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1006
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Cliff Gray
            Priority: Critical
             Fix For: 1.1 (pre-incubation)


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.



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