UDF: Arithmetic operations on multi-value UDFs return internal assertions

[Weishiun Tsai]

Most arithmetic operations on multi-value UDFs are not allowed. But instead of returning a proper error, they return internal assertions in ../common/BaseTypes.cpp at line 118 right now.

As shown in the 2 examples bellow, ENCRYPT_I_MVF() returns 2 integer outputs, and ENCRYPT_DATE_MVF() returns 2 DATE outputs. A ‘-‘ operation on ENCRYPT_I_MVF() and an ADD_MONTHS() operation on ENCRYPT_DATE_MVF() both return such internal assertions. 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

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

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 ENCRYPT_I_MVF
+>(INVAL int)
+>returns (OUTVAL1 int, OUTVAL2 int)
+>language c
+>parameter style sql
+>external name 'encrypt_i_mvf'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>create function ENCRYPT_DATE_MVF
+>(INVAL date)
+>returns (OUTVAL1 date, OUTVAL2 date)
+>language c
+>parameter style sql
+>external name 'encrypt_date_mvf'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>
>>create table myonerowtable (a int, b int);

--- SQL operation complete.
>>insert into myonerowtable values (6,6);

--- 1 row(s) inserted.
>>
>>create table mydatetimetable (a int not null not droppable primary key, c_date date);

--- SQL operation complete.
>>insert into mydatetimetable values(1, date '1999-01-01');

--- 1 row(s) inserted.
>>
>>select encrypt_i_mvf(a) from myonerowtable;

OUTVAL1 OUTVAL2
----------- -----------

          6 6

--- 1 row(s) selected.
>>select encrypt_i_mvf(a)-encrypt_i_mvf(a) from myonerowtable;

*** ERROR[2006] Internal error: assertion failure () in file ../common/BaseTypes.cpp at line 118.

*** ERROR[8822] The statement was not prepared.

>>
>>select encrypt_date_mvf(c_date) from mydatetimetable;

OUTVAL1 OUTVAL2
---------- ----------

1999-01-01 1999-01-01

--- 1 row(s) selected.
>>select ADD_MONTHS(encrypt_date_mvf(c_date), 1, 0) from mydatetimetable;

*** ERROR[2006] Internal error: assertion failure () in file ../common/BaseTypes.cpp at line 118.

*** ERROR[8822] The statement was not prepared.

>>
>>drop function ENCRYPT_I_MVF;

--- SQL operation complete.
>>drop function ENCRYPT_DATE_MVF;

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

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

--- SQL operation complete.

[Suresh Subbiah (suresh-subbiah)]

An error is raised now for arithmetic operators that take two arguments. ADD_MONTHS falls into this category as it is implemented using "+".

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0330 build installed on a workstation. This problem is now fixed with a proper 4479 error returned:

>>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_LP1420530/myudf.so';

--- SQL operation complete.
>>
>>create function ENCRYPT_I_MVF
+>(INVAL int)
+>returns (OUTVAL1 int, OUTVAL2 int)
+>language c
+>parameter style sql
+>external name 'encrypt_i_mvf'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>create function ENCRYPT_DATE_MVF
+>(INVAL date)
+>returns (OUTVAL1 date, OUTVAL2 date)
+>language c
+>parameter style sql
+>external name 'encrypt_date_mvf'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;

--- SQL operation complete.
>>
>>
>>create table myonerowtable (a int, b int);

--- SQL operation complete.
>>insert into myonerowtable values (6,6);

--- 1 row(s) inserted.
>>
>>create table mydatetimetable (a int not null not droppable primary key, c_date date);

--- SQL operation complete.
>>insert into mydatetimetable values(1, date '1999-01-01');

--- 1 row(s) inserted.
>>
>>select encrypt_i_mvf(a) from myonerowtable;

OUTVAL1 OUTVAL2
----------- -----------

          6 6

--- 1 row(s) selected.
>>select encrypt_i_mvf(a)-encrypt_i_mvf(a) from myonerowtable;

*** ERROR[4479] Function - accepts only 2 operand(s), 4 were provided due to a multi-output Function or Subquery.

*** ERROR[8822] The statement was not prepared.

>>
>>select encrypt_date_mvf(c_date) from mydatetimetable;

OUTVAL1 OUTVAL2
---------- ----------

1999-01-01 1999-01-01

--- 1 row(s) selected.
>>select ADD_MONTHS(encrypt_date_mvf(c_date), 1, 0) from mydatetimetable;

*** ERROR[4479] Function + accepts only 2 operand(s), 3 were provided due to a multi-output Function or Subquery.

*** ERROR[8822] The statement was not prepared.

>>
>>drop function ENCRYPT_I_MVF;

--- SQL operation complete.
>>drop function ENCRYPT_DATE_MVF;

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

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

--- SQL operation complete.