[jira] [Created] (TRAFODION-1147) LP Bug: 1441932 - TMUDF: setLong() has trouble handling decimal and decimal unsigned

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

Alice Chen created TRAFODION-1147:
-------------------------------------

             Summary: LP Bug: 1441932 - TMUDF: setLong() has trouble handling decimal and decimal unsigned
                 Key: TRAFODION-1147
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1147
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Hans Zeller
            Priority: Critical
             Fix For: 1.1 (pre-incubation)


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.



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