[jira] [Created] (TRAFODION-1140) LP Bug: 1441343 - UDF: alter function for a scalar UDF is handled poorly

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

Alice Chen created TRAFODION-1140:
-------------------------------------

             Summary: LP Bug: 1441343 - UDF: alter function for a scalar UDF is handled poorly
                 Key: TRAFODION-1140
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1140
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Roberta Marton
            Priority: Critical
             Fix For: 2.0-incubating


The original design intended to support alter function for scalar UDFs.  It’s not clear if this is still true for Trafodion.  Right now, it is handled poorly.  As shown in the following examples, alter a UDF that does not exist ‘doesnotexist’ does not even return an error.  Alter a UDF to ‘not deterministic’ or ‘state area size 2048’ says ‘SQL operation complete’, but showddl afterwards shows that they had no effect at all.

If Trafodion intends to support alter function for a scalar UDF, this needs to be fixed.  If it does not want to support it, a proper error should be returned to inform the user.

This is seen on the v0407 build installed on a workstation.  To reproduce it:

(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) run build.sh
(4) Change the line “create library qa_udf_lib file '<mydir>/myudf.so';”; in mytest.sql and fill in <mydir>
(5) From sqlci, obey mytest.sql

--------------------------------------------------------------------------------------------------

Here is the execution output:

>>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 table mytable (c1 int);

--- SQL operation complete.
>>insert into mytable values (1),(2),(3);

--- 3 row(s) inserted.
>>
>>create function qa_udf_integer
+>(INVAL integer)
+>returns (c_integer integer)
+>language c
+>parameter style sql
+>external name 'qa_func_int32'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>alter function doesnotexist not deterministic;

--- SQL operation complete.
>>alter function qa_udf_integer not deterministic;

--- SQL operation complete.
>>showddl function qa_udf_integer;

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

--- SQL operation complete.
>>alter function qa_udf_integer state area size 2048;

--- SQL operation complete.
>>showddl function qa_udf_integer;

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

--- SQL operation complete.
>>
>>select qa_udf_integer(c1) from mytable;

C_INTEGER
-----------

          1
          2
          3

--- 3 row(s) selected.
>>
>>drop table mytable cascade;

--- SQL operation complete.
>>drop function qa_udf_integer cascade;

--- SQL operation complete.
>>drop library qa_udf_lib cascade;

--- SQL operation complete.
>>drop schema mytest cascade;

--- SQL operation complete.



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