TMUDF: setLong() has trouble handling decimal and decimal unsigned

[Weishiun Tsai]

In this example, the TMUDF is a very simple one. The function proessData() calls getLong() to get the input column and passes the value to the output column using setLong():

void QATmudf::processData(UDRInvocationInfo &info, UDRPlanInfo &plan)
{
  while (getNextRow(info))
  {
    for (int i = 0; i < info.in().getNumColumns(); i++)
      info.out().setLong(i, info.in().getLong(i));

    emitRow(info);
  }
}

2 TMUDFs are defined. One is designed to take decimal as the input/output. The other is to take decimal unsigned as the input/output:

create table_mapping function qaTmudfGeneral1()
returns (o1 decimal(9,2))
external name 'QA_TMUDF'
language cpp
library qaTmudfLib;

create table_mapping function qaTmudfGeneral2()
returns (o2 decimal(9,2) unsigned)
external name 'QA_TMUDF'
language cpp
library qaTmudfLib;

But as shown in the execution output, setLong() has trouble handling both types. The decimal column passed in was 17.17, but the output column became 17.71. The decimal unsigned column passed in was 18.18, but the output column became 18.81. On top of that, the select statement handling the decimal column also returned a warning 8411 complaining about numeric overflow.

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>/qaTmudfTest.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 qaTmudfLib file '<mydir>/qaTmudfTest.so';

--- SQL operation complete.
>>
>>create table mytable1 (a decimal(9,2));

--- SQL operation complete.
>>create table mytable2 (a decimal(9,2) unsigned);

--- SQL operation complete.
>>insert into mytable1 values (17.17);

--- 1 row(s) inserted.
>>insert into mytable2 values (18.18);

--- 1 row(s) inserted.
>>select * from mytable1;

A
-----------

      17.17

--- 1 row(s) selected.
>>select * From mytable2;

A
----------

     18.18

--- 1 row(s) selected.
>>
>>create table_mapping function qaTmudfGeneral1()
+>returns (o1 decimal(9,2))
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>create table_mapping function qaTmudfGeneral2()
+>returns (o2 decimal(9,2) unsigned)
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>select * from UDF(qaTmudfGeneral1(TABLE(select * from mytable1)));

*** WARNING[8411] A numeric overflow occurred during an arithmetic computation or data conversion.

O1
-----------

      17.71

--- 1 row(s) selected.
>>select * from UDF(qaTmudfGeneral2(TABLE(select * from mytable2)));

O2
----------

     18.81

--- 1 row(s) selected.
>>
>>drop function qaTmudfGeneral1 cascade;

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

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

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

--- SQL operation complete.

[Hans Zeller (hans-zeller)]

Fix committed on 4/9/15, see https://review.trafodion.org/1483

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0410 build installed on a workstation. This problem has been fixed:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>
>>create library qaTmudfLib file '<mydir>/qaTmudfTest.so';

--- SQL operation complete.
>>
>>create table mytable1 (a decimal(9,2));

--- SQL operation complete.
>>create table mytable2 (a decimal(9,2) unsigned);

--- SQL operation complete.
>>insert into mytable1 values (17.17);

--- 1 row(s) inserted.
>>insert into mytable2 values (18.18);

--- 1 row(s) inserted.
>>select * from mytable1;

A
-----------

      17.17

--- 1 row(s) selected.
>>select * From mytable2;

A
----------

     18.18

--- 1 row(s) selected.
>>
>>create table_mapping function qaTmudfGeneral1()
+>returns (o1 decimal(9,2))
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>create table_mapping function qaTmudfGeneral2()
+>returns (o2 decimal(9,2) unsigned)
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>select * from UDF(qaTmudfGeneral1(TABLE(select * from mytable1)));

O1
-----------

      17.17

--- 1 row(s) selected.
>>select * from UDF(qaTmudfGeneral2(TABLE(select * from mytable2)));

O2
----------

     18.18

--- 1 row(s) selected.
>>
>>drop function qaTmudfGeneral1 cascade;

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

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

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

--- SQL operation complete.
>>
>>exit;

End of MXCI Session