TMUDF: getScale() returns a wrong scale for the TIME column

[Weishiun Tsai]

In the following example, the TMUDF takes the value and the scale of each column from the input table using getString() and getScale(). It then returns the value and the scale in one string for each column using setString():

void QATmudf::processData(UDRInvocationInfo &info, UDRPlanInfo &plan)
{
  while (getNextRow(info))
  {
    for (int i = 0; i < info.in().getNumColumns(); i++)
    {
      char val[80];
      sprintf (val, "c[%d] (val: %s scale %d)", i,
               info.in().getString(i).c_str(),
               info.in().getColumn(i).getType().getScale());
      info.out().setString(i, val);
   }
    emitRow(info);
  }
}

As shown in the execution output, with a table defined as the following:

create table mytable (
c1 time,
c2 time(5),
c3 timestamp,
c4 timestamp(5)
);

getScale() returns the correct value for c1 (default scale is 0), c3 (default scale is 6), and c4 (defined scale is 5). But it returns 0 for c2, whose defined scale is 5.

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 time,
+>c2 time(5),
+>c3 timestamp,
+>c4 timestamp(5)
+>);

--- SQL operation complete.
>>
>>insert into mytable values (
+>time '01:01:28',
+>time '01:01:29.12345',
+>timestamp '2030-01-01 01:01:30.123456',
+>timestamp '2031-01-01 01:01:31.12345'
+>);

--- 1 row(s) inserted.
>>
>>create table_mapping function qaTmudfGeneral()
+>returns (
+>c1_str char(100),
+>c2_str char(100),
+>c3_str char(100),
+>c4_str char(100))
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

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

C1 C2 C3 C4
-------- -------------- -------------------------- -------------------------

01:01:28 01:01:29.12345 2030-01-01 01:01:30.123456 2031-01-01 01:01:31.12345

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

C1_STR C2_STR C3_STR C4_STR
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------

c[0] (val: 01:01:28 scale 0) c[1] (val: 01:01:29.12345 scale 0) c[2] (val: 2030-01-01 01:01:30.123456 scale 6) c[3] (val: 2031-01-01 01:01:31.12345 scale 5)

--- 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 v0407 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 mytable (
+>c1 time,
+>c2 time(5),
+>c3 timestamp,
+>c4 timestamp(5)
+>);

--- SQL operation complete.
>>
>>insert into mytable values (
+>time '01:01:28',
+>time '01:01:29.12345',
+>timestamp '2030-01-01 01:01:30.123456',
+>timestamp '2031-01-01 01:01:31.12345'
+>);

--- 1 row(s) inserted.
>>
>>create table_mapping function qaTmudfGeneral()
+>returns (
+>c1_str char(100),
+>c2_str char(100),
+>c3_str char(100),
+>c4_str char(100))
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

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

C1 C2 C3 C4
-------- -------------- -------------------------- -------------------------

01:01:28 01:01:29.12345 2030-01-01 01:01:30.123456 2031-01-01 01:01:31.12345

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

C1_STR C2_STR C3_STR C4_STR
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------

c[0] (val: 01:01:28 scale 0) c[1] (val: 01:01:29.12345 scale 5) c[2] (val: 2030-01-01 01:01:30.123456 scale 6) c[3] (val: 2031-01-01 01:01:31.12345 scale 5)

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

--- SQL operation complete.