[
https://issues.apache.org/jira/browse/TRAFODION-1007?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14714208#comment-14714208
]
Suresh Subbiah commented on TRAFODION-1007:
-------------------------------------------
Verified on the v0330 build installed on a workstation. showddl now works for
UDF and TMUDF. However, some fields contain wrong information. Since this
report only covers whether or not showddl works for UDF and TMUDF, this report
will be closed and separate bug report will be open for the problem of wrong
info.
>>create schema mytest;
--- SQL operation complete.
>>set schema mytest;
--- SQL operation complete.
>>
>>create library qa_udf_lib file
>>'/designs/seaquest/wtsai/udf_bugs/udf_bug_LP1421438/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.
>>
>>showddl function myudf;
CREATE FUNCTION TRAFODION.MYTEST.MYUDF
(
IN INVAL INTEGER SIGNED
)
RETURNS
(
OUT OUTVAL INTEGER SIGNED
)
EXTERNAL NAME 'myudf'
LIBRARY TRAFODION.MYTEST.MYUDF
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
STATE AREA SIZE 1024
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;
--- SQL operation complete.
>>showddl procedure myudf;
CREATE FUNCTION TRAFODION.MYTEST.MYUDF
(
IN INVAL INTEGER SIGNED
)
RETURNS
(
OUT OUTVAL INTEGER SIGNED
)
EXTERNAL NAME 'myudf'
LIBRARY TRAFODION.MYTEST.MYUDF
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
STATE AREA SIZE 1024
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;
--- SQL operation complete.
>>showddl schema mytest;
CREATE PRIVATE SCHEMA "TRAFODION"."MYTEST" AUTHORIZATION "DB__ROOT";
--- SQL operation complete.
>>get functions;
Functions in Schema TRAFODION.MYTEST
====================================
MYUDF
--- SQL operation complete.
>>
>>drop schema mytest cascade;
--- SQL operation complete.
Changed in trafodion:
status: Fix Committed → Fix Released
> LP Bug: 1421438 - UDF: showddl does not work for UDFs
> -----------------------------------------------------
>
> Key: TRAFODION-1007
> URL: https://issues.apache.org/jira/browse/TRAFODION-1007
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmu
> Reporter: Weishiun Tsai
> Assignee: Suresh Subbiah
> Fix For: 1.1 (pre-incubation)
>
>
> Showddl is supposed to be enhanced for supporting UDF. But it does not work
> right now, as shown below. 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 line ‘create library qa_udf_lib file '<mydir>/myudf.so'; in
> mytest.sql and fill in <mydir>
> (4) From sqlci, obey mytest.sql
> -------------------------------------------
> >>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.
> >>
> >>select myudf(a) from mytable;
> OUTVAL
> -----------
> 1
> --- 1 row(s) selected.
> >>
> >>showddl function myudf;
> *** ERROR[1389] Object MYUDF does not exist in Trafodion.
> *** ERROR[4082] Object TRAFODION.MYTEST.MYUDF does not exist or is
> inaccessible.
> --- SQL operation failed with errors.
> >>showddl myudf;
> *** ERROR[1389] Object MYUDF does not exist in Trafodion.
> *** ERROR[4082] Object TRAFODION.MYTEST.MYUDF does not exist or is
> inaccessible.
> --- SQL operation failed with errors.
> >>showddl schema mytest;
> CREATE PRIVATE SCHEMA "TRAFODION"."MYTEST" AUTHORIZATION "DB__ROOT";
> --- SQL operation complete.
> >>get functions;
> Functions in Schema TRAFODION.MYTEST
> ====================================
> MYUDF
> --- SQL operation complete.
> >>
> >>drop schema mytest cascade;
> --- SQL operation complete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
