TMUDF: processData() fails to handle several data types
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
Fix Released
|
High
|
Hans Zeller |
Bug Description
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:
{
// loop over input rows and return the same rows
while (getNextRow(info))
{
// copy all columns and emit the row
info.
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: UDRInvocationIn
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>
(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>
--- 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_
+>c_char_varying char varying(15),
+>c_char_
+>c_char_
+>c_varchar varchar(15),
+>c_varchar_upshift varchar(15) upshift,
+>c_varchar_
+>c_nchar nchar(15),
+>c_nchar_upshift nchar(15) upshift,
+>c_nchar_
+>c_nchar_varying nchar varying(15),
+>c_nchar_
+>c_nchar_
+>-- 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_
+>c_largeint largeint,
+>c_smallint smallint,
+>c_smallint_
+>c_float float(10),
+>c_real real,
+>c_double_
+>-- 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_
C_CHAR C_CHAR_UPSHIFT C_CHAR_
AR_VARYING_UPSHIFT C_CHAR_
UPSHIFT C_VARCHAR_
HIFT C_NCHAR_
C_
C_INTEGER_
_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
NCHARVAR_1 ncharvar_1
-1 1 -1 -1 1 -
1.1100000000000
--- 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_
*** 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_
*** 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_
*** ERROR[11249] User-defined function QA_TMUDF completed with an error with SQL
STATE . Details: TypeInfo:
*** 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_
*** ERROR[11249] User-defined function QA_TMUDF completed with an error with SQL
STATE . Details: TypeInfo:
*** 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_
*** 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_
*** 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_
*** 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_
*** 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_
*** 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_
*** ERROR[11249] User-defined function QA_TMUDF completed with an error with SQL
STATE . Details: UDRInvocationIn
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
Changed in trafodion: | |
assignee: | nobody → Hans Zeller (hans-zeller) |
Changed in trafodion: | |
status: | New → In Progress |
Timestamp input columns are truncated when they are seen inside the TMUDF.
Use the attached cpp file to create a .so library file. ssubbiah/ trafodion/ core/sql/ regress/ udr/libsessioni zeETL.so'
create a library, change name of .so file in comment below.
create library sessionize_etl file '/opt/home/
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 * etl(TABLE( SELECT vehicle_id, gps_timestamp, alert_code
FROM clicks
PARTITION BY vehicle_id
ORDER BY gps_timestamp)));
FROM UDF(sessionize_
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.