TMUDF: copyPassThruData() fails to pad nchar data properly

[Weishiun Tsai]

The following TMUDF is a very simple one that uses addPassThruColumns() to add all input table columns to the output table, and then uses copyPassThruData() to copy the rows from the input table to the output table as well.

void QATmudf::describeParamsAndColumns(UDRInvocationInfo &info)
{
  info.addPassThruColumns(0);
}

void QATmudf::processData(UDRInvocationInfo &info, UDRPlanInfo &plan)
{
  while (getNextRow(info))
  {
    info.copyPassThruData();
    emitRow(info);
  }
}

But as shown in the execution output here, convertohex(c1) shows that the nchar column c1 is not padded properly in the output table. The value for c1 is different between the input table and the output table. A select query that uses the predicate <input table>.c1 = <input table>.c2 gets 0 row back as well.

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 nchar(10), c2 nchar varying(10));

--- SQL operation complete.
>>insert into mytable values (_ucs2'aaa', _ucs2'bbb');

--- 1 row(s) inserted.
>>
>>create table_mapping function qaTmudfGeneral()
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>select char_length(c1), converttohex(c1), char_length(c2), converttohex(c2) from mytable;

(EXPR) (EXPR) (EXPR) (EXPR)
---------- ---------------------------------------- ---------- ----------------------------------------

        10 0061006100610020002000200020002000200020 3 006200620062

--- 1 row(s) selected.
>>
>>select char_length(c1), converttohex(c1), char_length(c2), converttohex(c2) from
+>UDF(qaTmudfGeneral(TABLE(select * from mytable)));

(EXPR) (EXPR) (EXPR) (EXPR)
---------- ---------------------------------------- ---------- ----------------------------------------

        10 0061006100610020002000000000000000000000 3 006200620062

--- 1 row(s) selected.
>>
>>select * from
+>UDF(qaTmudfGeneral(TABLE(select * from mytable))) x1, mytable x2
+>where x1.c1 = x2.c1;

--- 0 row(s) selected.
>>
>>select * from
+>UDF(qaTmudfGeneral(TABLE(select * from mytable))) x1, mytable x2
+>where x1.c2 = x2.c2;

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

aaa bbb aaa bbb

--- 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 nchar(10), c2 nchar varying(10));

--- SQL operation complete.
>>insert into mytable values (_ucs2'aaa', _ucs2'bbb');

--- 1 row(s) inserted.
>>
>>create table_mapping function qaTmudfGeneral()
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>select char_length(c1), converttohex(c1), char_length(c2), converttohex(c2) from mytable;

(EXPR) (EXPR) (EXPR) (EXPR)
---------- ---------------------------------------- ---------- ----------------------------------------

        10 0061006100610020002000200020002000200020 3 006200620062

--- 1 row(s) selected.
>>
>>select char_length(c1), converttohex(c1), char_length(c2), converttohex(c2) from
+>UDF(qaTmudfGeneral(TABLE(select * from mytable)));

(EXPR) (EXPR) (EXPR) (EXPR)
---------- ---------------------------------------- ---------- ----------------------------------------

        10 0061006100610020002000200020002000200020 3 006200620062

--- 1 row(s) selected.
>>
>>select * from
+>UDF(qaTmudfGeneral(TABLE(select * from mytable))) x1, mytable x2
+>where x1.c1 = x2.c1;

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

aaa bbb aaa bbb

--- 1 row(s) selected.
>>
>>select * from
+>UDF(qaTmudfGeneral(TABLE(select * from mytable))) x1, mytable x2
+>where x1.c2 = x2.c2;

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

aaa bbb aaa bbb

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

--- SQL operation complete.