TMUDF: CLOB and BLOB in TMUDF crash sqlci with a core

[Weishiun Tsai]

TMUDF currently can’t handle CLOB and BLOB data types. It crashes sqlci in ExUdrTcb::buildAndSendTmudfInput() with a core indicating that the request buffer is not large enough to hold a single row.

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

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

Here is the execution output:

>>log mytest.log clear;
>>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_clob clob, c_blob blob);

--- SQL operation complete.
>>showddl mytable;

CREATE TABLE TRAFODION.MYTEST.MYTABLE
  (
    C_CLOB VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , C_BLOB VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  )
;

--- SQL operation complete.
>>insert into mytable values ('CLOB_1', 'BLOB_1');

--- 1 row(s) inserted.
>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from mytable;

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

CLOB_1 BLOB_1

--- 1 row(s) selected.
>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from UDF(qa_tmudf(TABLE(select * from mytable)));
Aborted (core dumped)

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

Here is the stack trace of the core file:

(gdb) bt
#0 0x00000039e28328a5 in raise () from /lib64/libc.so.6
#1 0x00000039e283400d in abort () from /lib64/libc.so.6
#2 0x00007ffff5d14810 in assert_botch_abend (
    f=0x7ffff4d2bcab "../executor/ExUdr.cpp", l=4266,
    m=0x7ffff4d2c178 "Request buffer not large enough to hold a single row",
    c=<value optimized out>) at ../export/NAAbort.cpp:282
#3 0x00007ffff4c9be67 in ExUdrTcb::buildAndSendTmudfInput (
    this=<value optimized out>) at ../executor/ExUdr.cpp:4264
#4 0x00007ffff4c9bfc8 in ExUdrTcb::tmudfWork (this=0x7fffe9746050)
    at ../executor/ExUdr.cpp:3548
#5 0x00007ffff4c63e83 in ExScheduler::work (this=0x7fffe9744fc0,
    prevWaitTime=<value optimized out>) at ../executor/ExScheduler.cpp:328
#6 0x00007ffff4b8d9c6 in ex_root_tcb::fetch (this=0x7fffe97467b8,
    cliGlobals=<value optimized out>, glob=0x7fffe975e860, output_desc=
    0x7fffe9760d40, diagsArea=@0x7fffffff2830, timeLimit=-1, newOperation=1,
    closeCursorOnError=@0x7fffffff283c) at ../executor/ex_root.cpp:1875
#7 0x00007ffff5fb2598 in CliStatement::fetch (this=0x7fffe97605b0,
    cliGlobals=0xb82c10, output_desc=0x7fffe9760d40, diagsArea=...,
    newOperation=1) at ../cli/Statement.cpp:4785
#8 0x00007ffff5f5a21f in SQLCLI_PerformTasks(CliGlobals *, ULng32, SQLSTMT_ID *, SQLDESC_ID *, SQLDESC_ID *, Lng32, Lng32, typedef __va_list_tag __va_list_tag *, SQLCLI_PTR_PAIRS *, SQLCLI_PTR_PAIRS *) (cliGlobals=0xb82c10, tasks=4900,
    statement_id=0x19d1cd0, input_descriptor=0x0, output_descriptor=0x17271f0,
    num_input_ptr_pairs=-378139328, num_output_ptr_pairs=0, ap=0x7fffffff29d0,
    input_ptr_pairs=0x0, output_ptr_pairs=0x0) at ../cli/Cli.cpp:3382
#9 0x00007ffff5f5ac3d in SQLCLI_Fetch(CliGlobals *, SQLSTMT_ID *, SQLDESC_ID *, Lng32, typedef __va_list_tag __va_list_tag *, SQLCLI_PTR_PAIRS *) (
    cliGlobals=<value optimized out>, statement_id=<value optimized out>,
    output_descriptor=<value optimized out>,
    num_ptr_pairs=<value optimized out>, ap=<value optimized out>,
    ptr_pairs=<value optimized out>) at ../cli/Cli.cpp:3820
#10 0x00007ffff5fbe57b in SQL_EXEC_Fetch (statement_id=0x19d1cd0,
    output_descriptor=0x17271f0, num_ptr_pairs=0) at ../cli/CliExtern.cpp:2737
#11 0x00007ffff79c9362 in SqlCmd::doFetch (sqlci_env=0xb819c0,
    stmt=<value optimized out>, prep_stmt=<value optimized out>,
    firstFetch=<value optimized out>, handleError=1, prepcode=0)
    at ../sqlci/SqlCmd.cpp:1727
#12 0x00007ffff79cd574 in SqlCmd::do_execute (sqlci_env=0xb819c0,
    prep_stmt=0x1761320, numUnnamedParams=0, unnamedParamArray=0x0,
    unnamedParamCharSetArray=<value optimized out>, prepcode=0)
    at ../sqlci/SqlCmd.cpp:2055
#13 0x00007ffff79ce46d in DML::process (this=0x29b57f0, sqlci_env=0xb819c0)
    at ../sqlci/SqlCmd.cpp:2816
#14 0x00007ffff79b4a84 in Obey::process (this=0x23de4e0,
    sqlci_env=<value optimized out>) at ../sqlci/Obey.cpp:264
#15 0x00007ffff79bcf14 in SqlciEnv::run (this=0xb819c0,
    in_filename=<value optimized out>, input_string=<value optimized out>)
    at ../sqlci/SqlciEnv.cpp:726
#16 0x00000000004019c2 in main (argc=3, argv=0x7fffffff43c8)
    at ../bin/SqlciMain.cpp:326

[Hans Zeller (hans-zeller)]

With the fix, we will issue the following error:

*** ERROR[11151] Unable to use 'type' 'LOB ALLOWS NULLS' in a user-defined routine. Details: unsupported type class.

If we convert LOBs to VARCHAR, which is currently the default, the UDF should be able to handle those data types.

[Hans Zeller (hans-zeller)]

To test it both ways, with real LOBs and VARCHARs, set CQD TRAF_BLOB_AS_VARCHAR to ON and OFF, respectively. Currently, the default is OFF, which will probably change in the future.

[Hans Zeller (hans-zeller)]

Fix has been checked in on March 20 as part of https://review.trafodion.org/#/c/1329 (sorry for not mentioning it in the checkin message)

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0330 build installed on a workstation. Tried both CQD TRAF_BLOB_AS_VARCHAR 'ON' and 'OFF', the behavior is now as designed:

>>create schema mytest3;

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

--- SQL operation complete.
>>
>>cqd TRAF_BLOB_AS_VARCHAR 'ON';

--- 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_clob clob, c_blob blob);

--- SQL operation complete.
>>showddl mytable;

CREATE TABLE TRAFODION.MYTEST3.MYTABLE
  (
    C_CLOB VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , C_BLOB VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  )
;

--- SQL operation complete.
>>insert into mytable values ('CLOB_1', 'BLOB_1');

--- 1 row(s) inserted.
>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from mytable;

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

CLOB_1 BLOB_1

--- 1 row(s) selected.
>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from UDF(qa_tmudf(TABLE(select * from mytable)));

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

CLOB_1 BLOB_1

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

--- SQL operation complete.
>>
>>create schema mytest4;

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

--- SQL operation complete.
>>
>>cqd TRAF_BLOB_AS_VARCHAR 'OFF';

--- 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_clob clob, c_blob blob);

--- SQL operation complete.
>>showddl mytable;

CREATE TABLE TRAFODION.MYTEST4.MYTABLE
  (
    C_CLOB CLOB DEFAULT NULL
  , C_BLOB BLOB DEFAULT NULL
  )
;

--- SQL operation complete.
>>insert into mytable values ('CLOB_1', 'BLOB_1');

*** ERROR[4039] Column C_CLOB is of type LOB, incompatible with the value's type, CHAR(6).

*** ERROR[8822] The statement was not prepared.

>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from mytable;

*** ERROR[4035] Type LOB cannot be cast to type CHAR(10).

*** ERROR[8822] The statement was not prepared.

>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[11151] Unable to use 'type' 'LOB ALLOWS NULLS' in a user-defined routine. Details: unsupported type class.

*** ERROR[8822] The statement was not prepared.

>>
>>drop schema mytest4 cascade;

*** ERROR[1069] Schema TRAFODION.MYTEST4 could not be dropped.

--- SQL operation failed with errors.