TMUDF: processData() fails to handle several data types

[Weishiun Tsai]

In the following example, QA_TMUDF() is defined as the TMUDF class QATmudf() that takes a table input and returns the exact same columns and rows back. In its processData(), it goes through the following loop to copy the input rows and emit them:

void QATmudf::processData(UDRInvocationInfo &info, UDRPlanInfo &plan)
{
  // loop over input rows and return the same rows
  while (getNextRow(info))
  {
    // copy all columns and emit the row
    info.copyPassThruData();

    emitRow(info);
  }
}

But as shown here, TMUDF has trouble handing several data types in such a loop. It returns various errors, such as 8413, 8415, or 11249 when processing the following data types:

NUMERIC
DECIMAL
DATE
TIME
TIMESTAMP
INTERVAL

*** ERROR[8413] The string argument contains characters that cannot be converted.
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted.
*** ERROR[11249] User-defined function QA_TMUDF completed with an error with SQLSTATE . Details: UDRInvocationInfo::copyPassThruData not yet supported for type subclass <num>

This is seen on the v0305 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 from <mydir> to build the UDF so file.
(4) Change the line create library qaTmudfLib file '<mydir>/qaTMUdfTest.so'; in mytest.sql and fill in <mydir>
(5) From sqlci, obey mytest.sql

As shown in the execution output, the first table and query in mytest.sql show several data types that work, followed by a list of tables and queries containing data types that return errors.

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

Here is the execution output:

>>drop schema mytest cascade;

*** ERROR[1003] Schema TRAFODION.MYTEST does not exist.

--- SQL operation failed with errors.
>>create schema mytest;

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

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

--- SQL operation complete.
>>
>>create table_mapping function qa_tmudf()
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>create table mytable (
+>c_char char(15),
+>c_char_upshift char(15) upshift,
+>c_char_not_casespecific char(15) not casespecific,
+>c_char_varying char varying(15),
+>c_char_varying_upshift char varying(15) upshift,
+>c_char_varying_not_casespecific char varying(15) not casespecific,
+>c_varchar varchar(15),
+>c_varchar_upshift varchar(15) upshift,
+>c_varchar_not_casespecific varchar(15) not casespecific,
+>c_nchar nchar(15),
+>c_nchar_upshift nchar(15) upshift,
+>c_nchar_not_casespecific nchar(15) not casespecific,
+>c_nchar_varying nchar varying(15),
+>c_nchar_varying_upshift nchar varying(15) upshift,
+>c_nchar_varying_not_casespecific nchar varying(15) not casespecific,
+>-- c_numeric numeric(9,2),
+>-- c_numeric_unsigned numeric(9,2) unsigned,
+>-- c_decimal decimal(9,2),
+>-- c_decimal_unsigned decimal(9,2) unsigned,
+>c_integer integer,
+>c_integer_unsigned integer unsigned,
+>c_largeint largeint,
+>c_smallint smallint,
+>c_smallint_unsigned smallint unsigned,
+>c_float float(10),
+>c_real real,
+>c_double_precision double precision
+>-- c_date date,
+>-- c_time time,
+>-- c_time6 time(6),
+>-- c_timestamp timestamp,
+>-- c_timestamp6 timestamp(6),
+>-- c_interval interval year to month
+>);

--- SQL operation complete.
>>
>>insert into mytable values (
+>'CHAR_1',
+>'char_1',
+>'char_1',
+>'CHARVAR_1',
+>'charvar_1',
+>'charvar_1',
+>'VARCHAR_1',
+>'varchar_1',
+>'varchar_1',
+>'NCHAR_1',
+>'nchar_1',
+>'nchar_1',
+>'NCHARVAR_1',
+>'ncharvar_1',
+>'ncharvar_1',
+>-- -1,
+>-- 1,
+>-- -1.11,
+>-- 1.11,
+>-1,
+>1,
+>-1,
+>-1,
+>1,
+>-1.11,
+>-1.11,
+>-1.11
+>-- date '2001-01-01',
+>-- time '01:01:01',
+>-- time '01:01:01.111111',
+>-- timestamp '2001-01-01 01:01:01',
+>-- timestamp '2001-01-01 01:01:01.111111',
+>-- interval '01-01' year to month
+>);

--- 1 row(s) inserted.
>>
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

C_CHAR C_CHAR_UPSHIFT C_CHAR_NOT_CASESPECIFIC C_CHAR_VARYING C_CH
AR_VARYING_UPSHIFT C_CHAR_VARYING_NOT_CASESPECIFIC C_VARCHAR C_VARCHAR_
UPSHIFT C_VARCHAR_NOT_CASESPECIFIC C_NCHAR C_NCHAR_UPS
HIFT C_NCHAR_NOT_CASESPECIFIC C_NCHAR_VARYING
     C_NCHAR_VARYING_UPSHIFT C_NCHAR_VARYING_NOT_CASESPECIFIC C_INTEGER
    C_INTEGER_UNSIGNED C_LARGEINT C_SMALLINT C_SMALLINT_UNSIGNED C
_FLOAT C_REAL C_DOUBLE_PRECISION
--------------- --------------- ----------------------- --------------- ----
------------------ ------------------------------- --------------- ----------
------- -------------------------- ------------------------------ -----------
------------------- ------------------------------ ---------------------------
--- ------------------------------ -------------------------------- ---------
-- ------------------ -------------------- ---------- ------------------- -
------------------------ --------------- -------------------------

CHAR_1 CHAR_1 char_1 CHARVAR_1 CHAR
VAR_1 charvar_1 VARCHAR_1 VARCHAR_1
         varchar_1 NCHAR_1 NCHAR_1
                     nchar_1 NCHARVAR_1
     NCHARVAR_1 ncharvar_1
-1 1 -1 -1 1 -
1.11000000000000016E+000 -1.1100000E+000 -1.11000000000000016E+000

--- 1 row(s) selected.
>>
>>drop table mytable;

--- SQL operation complete.
>>create table mytable (c_numeric numeric(9,2));

--- SQL operation complete.
>>insert into mytable values (-1);

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));
*** ERROR[8413] The string argument contains characters that cannot be converted
.

--- 0 row(s) selected.
>>
>>drop table mytable;

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

--- SQL operation complete.
>>insert into mytable values (1);

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[8413] The string argument contains characters that cannot be converted
.

--- 0 row(s) selected.
>>
>>drop table mytable;
--- SQL operation complete.
>>create table mytable (c_decimal decimal(9,2));

--- SQL operation complete.
>>insert into mytable values (-1.11);

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[11249] User-defined function QA_TMUDF completed with an error with SQL
STATE . Details: TypeInfo::getNumericValue() not supported for SQL type 5.

*** ERROR[2037] $Z000HTY:87: A message from process $Z000I44:90 was incorrectly
formatted and could not be processed.

*** ERROR[8906] An invalid or corrupt MXUDR reply could not be processed, possib
ly due to memory corruption in MXUDR while executing user-defined routines or an
 internal error in SQL.

--- 0 row(s) selected.
>>
>>drop table mytable;
--- SQL operation complete.
>>create table mytable (c_decimal_unsigned decimal(9,2) unsigned);

--- SQL operation complete.
>>insert into mytable values (1.11);

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[11249] User-defined function QA_TMUDF completed with an error with SQL
STATE . Details: TypeInfo::getNumericValue() not supported for SQL type 9.

*** ERROR[2037] $Z000HTY:87: A message from process $Z000I44:90 was incorrectly
formatted and could not be processed.

*** ERROR[8906] An invalid or corrupt MXUDR reply could not be processed, possib
ly due to memory corruption in MXUDR while executing user-defined routines or an
 internal error in SQL.

--- 0 row(s) selected.
>>
>>drop table mytable;
--- SQL operation complete.
>>create table mytable (c_date date);

--- SQL operation complete.
>>insert into mytable values (date '2001-01-01');

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be
 converted.

--- 0 row(s) selected.
>>
>>drop table mytable;

--- SQL operation complete.
>>create table mytable (c_time time);

--- SQL operation complete.
>>insert into mytable values (time '01:01:01');

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be
 converted.

--- 0 row(s) selected.
>>
>>drop table mytable;

--- SQL operation complete.
>>create table mytable (c_time6 time(6));

--- SQL operation complete.
>>insert into mytable values (time '01:01:01.111111');

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[8413] The string argument contains characters that cannot be converted
.

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be
 converted.

--- 0 row(s) selected.
>>
>>drop table mytable;

--- SQL operation complete.
>>create table mytable (c_timestamp timestamp);

--- SQL operation complete.
>>insert into mytable values (timestamp '2001-01-01 01:01:01');

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[8413] The string argument contains characters that cannot be converted
.

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be
 converted.

--- 0 row(s) selected.
>>
>>drop table mytable;

--- SQL operation complete.
>>create table mytable (c_timestamp6 timestamp(6));

--- SQL operation complete.
>>insert into mytable values (timestamp '2001-01-01 01:01:01.111111');

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[8413] The string argument contains characters that cannot be converted
.

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be
 converted.

--- 0 row(s) selected.
>>
>>drop table mytable;

--- SQL operation complete.
>>create table mytable (c_interval interval year to month);

--- SQL operation complete.
>>insert into mytable values (interval '01-01' year to month);

--- 1 row(s) inserted.
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[11249] User-defined function QA_TMUDF completed with an error with SQL
STATE . Details: UDRInvocationInfo::copyPassThruData not yet supported for type
subclass 7.

*** ERROR[2037] $Z000HTY:87: A message from process $Z000I44:90 was incorrectly
formatted and could not be processed.

*** ERROR[8906] An invalid or corrupt MXUDR reply could not be processed, possib
ly due to memory corruption in MXUDR while executing user-defined routines or an
 internal error in SQL.

--- 0 row(s) selected.
>>
>>drop table mytable;

--- SQL operation complete.
>>
subclass 7.

*** ERROR[2037] $Z000HTY:87: A message from process $Z000I44:90 was incorrectly
formatted and could not be processed.

*** ERROR[8906] An invalid or corrupt MXUDR reply could not be processed, possib
ly due to memory corruption in MXUDR while executing user-defined routines or an
 internal error in SQL.

--- 0 row(s) selected.
>>
>>drop table mytable;

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

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

End of MXCI Session

[Suresh Subbiah (suresh-subbiah)]

Timestamp input columns are truncated when they are seen inside the TMUDF.

Use the attached cpp file to create a .so library file.
create a library, change name of .so file in comment below.
create library sessionize_etl file '/opt/home/ssubbiah/trafodion/core/sql/regress/udr/libsessionizeETL.so'

create table_mapping function sessionize_etl()
returns (vehicle_id char(25),
         begin_utc char(19),
         end_utc char(19),
  alarm_code char(2))
language cpp
external name 'SESSIONIZEETL'
library sessionize_etl;

CREATE TABLE clicks
  (
    VEHICLE_ID CHAR(25) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , GPS_TIMESTAMP TIMESTAMP(0) NO DEFAULT NOT NULL NOT DROPPABLE
  , ALERT_CODE VARCHAR(200) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL)
   STORE BY (VEHICLE_ID ASC, GPS_TIMESTAMP ASC);

insert into clicks values ('12345', timestamp '2011-11-11 01:01:01', '01,02,03,04,05') ;

SELECT *
FROM UDF(sessionize_etl(TABLE(SELECT vehicle_id, gps_timestamp, alert_code
                          FROM clicks
                          PARTITION BY vehicle_id
                          ORDER BY gps_timestamp)));

VEHICLE_ID BEGIN_UTC END_UTC ALARM_CODE
------------------------- ------------------- ------------------- ----------

12345 2011-11 2011-11 01
12345 2011-11 2011-11 02
12345 2011-11 2011-11 03
12345 2011-11 2011-11 04
12345 2011-11 2011-11 05

--- 5 row(s) selected.
>>

-- the timestamp output is truncated. This is true in the debugger when we look at the input rows.

[Suresh Subbiah (suresh-subbiah)]

This version of sessioneETL.cpp is slightly different from the version attached in another LP bug. It fixes a small issue with returning the last set of rows in the UDF body.

[Hans Zeller (hans-zeller)]

Fix was delivered on March 18 as part of https://review.trafodion.org/1329

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0330 build installed on a workstation. Most data types are now processed properly. Minor problems regarding the handling of some individual data types will be filed as separate bug reports. This bug report will be closed now.

>>create schema mytest;

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

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

--- SQL operation complete.
>>
>>create table_mapping function qa_tmudf()
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>create table mytable (
+>c_char char(50),
+>c_char_upshift char(50) upshift,
+>c_char_not_casespecific char(50) not casespecific,
+>c_char_varying char varying(50),
+>c_char_varying_upshift char varying(50) upshift,
+>c_char_varying_not_casespecific char varying(50) not casespecific,
+>c_varchar varchar(50),
+>c_varchar_upshift varchar(50) upshift,
+>c_varchar_not_casespecific varchar(50) not casespecific,
+>c_nchar nchar(50),
+>c_nchar_upshift nchar(50) upshift,
+>c_nchar_not_casespecific nchar(50) not casespecific,
+>c_nchar_varying nchar varying(50),
+>c_nchar_varying_upshift nchar varying(50) upshift,
+>c_nchar_varying_not_casespecific nchar varying(50) not casespecific,
+>c_numeric numeric(9,2),
+>c_numeric_unsigned numeric(9,2) unsigned,
+>c_decimal decimal(9,2),
+>c_decimal_unsigned decimal(9,2) unsigned,
+>c_integer integer,
+>c_integer_unsigned integer unsigned,
+>c_largeint largeint,
+>c_smallint smallint,
+>c_smallint_unsigned smallint unsigned,
+>c_float float(10),
+>c_real real,
+>c_double_precision double precision,
+>c_date date,
+>c_time time,
+>c_time6 time(6),
+>c_timestamp timestamp,
+>c_timestamp6 timestamp(6),
+>c_interval interval year to month
+>);

--- SQL operation complete.
>>
>>insert into mytable values (
+>'CHAR_1',
+>'char_1',
+>'char_1',
+>'CHARVAR_1',
+>'charvar_1',
+>'charvar_1',
+>'VARCHAR_1',
+>'varchar_1',
+>'varchar_1',
+>'NCHAR_1',
+>'nchar_1',
+>'nchar_1',
+>'NCHARVAR_1',
+>'ncharvar_1',
+>'ncharvar_1',
+>-1,
+>1,
+>-1.11,
+> 1.11,
+>-1,
+>1,
+>-1,
+>-1,
+>1,
+>-1.11,
+>-1.11,
+>-1.11,
+>date '2001-01-01',
+>time '01:01:01',
+>time '01:01:01.111111',
+>timestamp '2001-01-01 01:01:01',
+>timestamp '2001-01-01 01:01:01.111111',
+>interval '01-01' year to month
+>);

--- 1 row(s) inserted.
>>
>>select * from UDF(qa_tmudf(TABLE(select * from mytable)));

C_CHAR C_CHAR_UPSHIFT
                        C_CHAR_NOT_CASESPECIFIC C_CH
AR_VARYING C_CHAR_VARYING_UPSHIFT
                    C_CHAR_VARYING_NOT_CASESPECIFIC C_VARCHA
R C_VARCHAR_UPSHIFT
                C_VARCHAR_NOT_CASESPECIFIC C_NCHAR

          C_NCHAR_UPSHIFT
                                C_NCHAR_NOT_CASESPECIFIC
                                                      C_NCHAR_VARYING
                                                                            C_NC
HAR_VARYING_UPSHIFT
                  C_NCHAR_VARYING_NOT_CASESPECIFIC
                                        C_NUMERIC ...