[TRAFODION-1902] Showddl continues to show function after it's dropped

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0-incubating
• Component/s: sql-cmu
• Assignee: Roberta Marton
• Reporter: Weishiun Tsai
• Created: 21/Mar/16 22:55
• Updated: 05/Apr/16 17:04
• Resolved: 05/Apr/16 17:04

Description

As shown in the following execution output, showddl continues to show the function even after 'drop library cascade', 'drop schema cascade', or even 'drop function' itself. Any of the 3 statements should have dropped the function.

>>create schema mytest200;

— SQL operation complete.
>>set schema mytest200;

— SQL operation complete.
>>
>>create library MYLIB file '/home/trafodion/QALibs/UDF/qaUdfTest.so';

— SQL operation complete.
>>
>>create function MYFUNC
+>(INVAL integer)
+>returns (c_integer integer)
+>language c
+>parameter style sql
+>external name 'qa_func_int32'
+>library MYLIB
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

— SQL operation complete.
>>
>>showddl function MYFUNC;

CREATE FUNCTION TRAFODION.MYTEST200.MYFUNC
(
IN INVAL INTEGER SIGNED
)
RETURNS
(
OUT C_INTEGER INTEGER SIGNED
)
EXTERNAL NAME 'qa_func_int32'
LIBRARY TRAFODION.MYTEST200.MYLIB
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
STATE AREA SIZE 1024
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;

— SQL operation complete.
>>
>>drop library MYLIB cascade;

— SQL operation complete.
>>showddl function MYFUNC;

CREATE FUNCTION TRAFODION.MYTEST200.MYFUNC
(
IN INVAL INTEGER SIGNED
)
RETURNS
(
OUT C_INTEGER INTEGER SIGNED
)
EXTERNAL NAME 'qa_func_int32'
LIBRARY TRAFODION.MYTEST200.MYLIB
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
STATE AREA SIZE 1024
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;

— SQL operation complete.
>>
>>drop schema mytest200 cascade;

— SQL operation complete.
>>showddl function MYFUNC;

CREATE FUNCTION TRAFODION.MYTEST200.MYFUNC
(
IN INVAL INTEGER SIGNED
)
RETURNS
(
OUT C_INTEGER INTEGER SIGNED
)
EXTERNAL NAME 'qa_func_int32'
LIBRARY TRAFODION.MYTEST200.MYLIB
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
STATE AREA SIZE 1024
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;

— SQL operation complete.
>>
>>drop function MYFUNC;

• • • ERROR[1389] Object TRAFODION.MYTEST200.MYFUNC does not exist in Trafodion.

— SQL operation failed with errors.
>>showddl function MYFUNC;

CREATE FUNCTION TRAFODION.MYTEST200.MYFUNC
(
IN INVAL INTEGER SIGNED
)
RETURNS
(
OUT C_INTEGER INTEGER SIGNED
)
EXTERNAL NAME 'qa_func_int32'
LIBRARY TRAFODION.MYTEST200.MYLIB
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
STATE AREA SIZE 1024
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;

— SQL operation complete.

=========================================================

Here is the entire script tor reproduce this problem:

log mytest.log clear;

create schema mytest200;
set schema mytest200;

create library MYLIB file '/home/trafodion/QALibs/UDF/qaUdfTest.so';

create function MYFUNC
(INVAL integer)
returns (c_integer integer)
language c
parameter style sql
external name 'qa_func_int32'
library MYLIB
deterministic
state area size 1024
allow any parallelism
no sql;

showddl function MYFUNC;

drop library MYLIB cascade;
showddl function MYFUNC;

drop schema mytest200 cascade;
showddl function MYFUNC;

drop function MYFUNC;
showddl function MYFUNC;