TMUDF: Unsigned numeric is mapped to TypeInfo::NUMERIC

[Weishiun Tsai]

Unsigned numeric data type is mapped to TypeInfo::NUMERIC internally, as opposed to TypeInfo::NUMERIC_UNSIGNED. This may not have been an issue if the mapping is only used internally within the implementation of TMUDF, since both signed numeric and unsigned numeric are generally handled the same way. However, the ramification of this mapping is that the user of a TMUDF can actually return a negative number for a numeric unsigned output column, as shown here.

The following example takes 2 columns from the input table and returns the data types in character strings as the first 2 columns of the output table. The 3rd column o3 in the output table is declared at the define function time as a numeric unsigned column:

void QATmudf::processData(UDRInvocationInfo &info, UDRPlanInfo &plan)
{
  while (getNextRow(info))
  {
    for (int i = 0; i < info.in().getNumColumns(); i++)
    {
      char val[25];
      sprintf(val, "c[%d]: %s", i+1,
             ((info.in().getColumn(i).getType().getSQLType()
                   == TypeInfo::NUMERIC) ?
               "TypeInfo::NUMERIC" : "TypeInfo::NUMERIC_UNSIGNED"));
      info.out().setString(i, val);
    }
    // the last output column is a numeric unsigned
    info.out().setLong(2, -3333);
    emitRow(info);
  }
}

As shown in the execution output, the data types for both c1 and c2 are mapped to TypeInfo::NUMERIC, even though c2 should be mapped to TypeInfo::NUMERIC_UNSIGNED instead. With this wrong data type, the user can also return a negative numeric number -33.33 for o3 even though o3 is declared as numeric unsigned.

This is seen on the v0324 build installed on a workstation.

To reproduce this problem:

(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) Change the line "create library qaTmudfLib file '<mydir>/qaTmudfTest.so'; " in mytest.sql and fill in <mydir>
(4) 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 mytable (
+>c1 numeric(9,2),
+>c2 numeric(9,2) unsigned
+>);

--- SQL operation complete.
>>
>>insert into mytable values (-11.11, 22.22);

--- 1 row(s) inserted.
>>
>>create table_mapping function qaTmudfGeneral()
+>returns (
+>o1 char(25),
+>o2 char(25),
+>o3 numeric(9,2) unsigned
+>)
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>select * from mytable;

C1 C2
------------ -----------

      -11.11 22.22

--- 1 row(s) selected.
>>
>>select * from UDF(qaTmudfGeneral(TABLE(select * from mytable)));

O1 O2 O3
------------------------- ------------------------- ------------

c[1]: TypeInfo::NUMERIC c[2]: TypeInfo::NUMERIC -33.33

--- 1 row(s) selected.
>>
>>drop schema mytest cascade;

--- SQL operation complete.

[Hans Zeller (hans-zeller)]

A fix was committed to the R1.1 branch on 3/31/15.
https://review.trafodion.org/1415

[Weishiun Tsai (wei-shiun-tsai)]

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

With the line in qaTmudfTest.cpp remains as info.out().setLong(2, -3333); the execution returns error 11252, as a numeric unsigned output can't take a negative value -3333:

>>create schema mytest;

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

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

--- SQL operation complete.
>>
>>create table mytable (
+>c1 numeric(9,2),
+>c2 numeric(9,2) unsigned
+>);

--- SQL operation complete.
>>
>>insert into mytable values (-11.11, 22.22);

--- 1 row(s) inserted.
>>
>>create table_mapping function qaTmudfGeneral()
+>returns (
+>o1 char(50),
+>o2 char(50),
+>o3 numeric(9,2) unsigned
+>)
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>select * from mytable;

C1 C2
------------ -----------

      -11.11 22.22

--- 1 row(s) selected.
>>
>>select * from UDF(qaTmudfGeneral(TABLE(select * from mytable)));

*** ERROR[11252] Trying to assign a negative value to an INT UNSIGNED type (SQLSTATE 38900)

--- 0 row(s) selected.
>>
>>drop schema mytest cascade;

--- SQL operation complete.

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

With the line in qaTmudfTest.cpp changed to info.out().setLong(2, 3333); the execution output indicates that the input data type for column c2 is now correctly mapped to TypeInfo::NUMERIC_UNSIGNED

>>create schema mytest;

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

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

--- SQL operation complete.
>>
>>create table mytable (
+>c1 numeric(9,2),
+>c2 numeric(9,2) unsigned
+>);

--- SQL operation complete.
>>
>>insert into mytable values (-11.11, 22.22);

--- 1 row(s) inserted.
>>
>>create table_mapping function qaTmudfGeneral()
+>returns (
+>o1 char(50),
+>o2 char(50),
+>o3 numeric(9,2) unsigned
+>)
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>select * from mytable;

C1 C2
------------ -----------

      -11.11 22.22

--- 1 row(s) selected.
>>
>>select * from UDF(qaTmudfGeneral(TABLE(select * from mytable)));

O1 O2 O3
-------------------------------------------------- -------------------------------------------------- -----------

c[1]: TypeInfo::NUMERIC c[2]: TypeInfo::NUMERIC_UNSIGNED 33.33

--- 1 row(s) selected.
>>
>>drop schema mytest cascade;

--- SQL operation complete.