Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-1147

LP Bug: 1441932 - TMUDF: setLong() has trouble handling decimal and decimal unsigned

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.1 (pre-incubation)
    • Component/s: sql-exe
    • Labels:
      None

      Description

      In this example, the TMUDF is a very simple one. The function proessData() calls getLong() to get the input column and passes the value to the output column using setLong():

      void QATmudf::processData(UDRInvocationInfo &info, UDRPlanInfo &plan)
      {
      while (getNextRow(info))

      { for (int i = 0; i < info.in().getNumColumns(); i++) info.out().setLong(i, info.in().getLong(i)); emitRow(info); }

      }

      2 TMUDFs are defined. One is designed to take decimal as the input/output. The other is to take decimal unsigned as the input/output:

      create table_mapping function qaTmudfGeneral1()
      returns (o1 decimal(9,2))
      external name 'QA_TMUDF'
      language cpp
      library qaTmudfLib;

      create table_mapping function qaTmudfGeneral2()
      returns (o2 decimal(9,2) unsigned)
      external name 'QA_TMUDF'
      language cpp
      library qaTmudfLib;

      But as shown in the execution output, setLong() has trouble handling both types. The decimal column passed in was 17.17, but the output column became 17.71. The decimal unsigned column passed in was 18.18, but the output column became 18.81. On top of that, the select statement handling the decimal column also returned a warning 8411 complaining about numeric overflow.

      This is seen on the v0407 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
      (4) Change the line “create library qa_udf_lib file '<mydir>/qaTmudfTest.so';”; in mytest.sql and fill in <mydir>
      (5) 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 mytable1 (a decimal(9,2));

      — SQL operation complete.
      >>create table mytable2 (a decimal(9,2) unsigned);

      — SQL operation complete.
      >>insert into mytable1 values (17.17);

      — 1 row(s) inserted.
      >>insert into mytable2 values (18.18);

      — 1 row(s) inserted.
      >>select * from mytable1;

      A
      -----------

      17.17

      — 1 row(s) selected.
      >>select * From mytable2;

      A
      ----------

      18.18

      — 1 row(s) selected.
      >>
      >>create table_mapping function qaTmudfGeneral1()
      +>returns (o1 decimal(9,2))
      +>external name 'QA_TMUDF'
      +>language cpp
      +>library qaTmudfLib;

      — SQL operation complete.
      >>
      >>create table_mapping function qaTmudfGeneral2()
      +>returns (o2 decimal(9,2) unsigned)
      +>external name 'QA_TMUDF'
      +>language cpp
      +>library qaTmudfLib;

      — SQL operation complete.
      >>
      >>select * from UDF(qaTmudfGeneral1(TABLE(select * from mytable1)));

          • WARNING[8411] A numeric overflow occurred during an arithmetic computation or data conversion.

      O1
      -----------

      17.71

      — 1 row(s) selected.
      >>select * from UDF(qaTmudfGeneral2(TABLE(select * from mytable2)));

      O2
      ----------

      18.81

      — 1 row(s) selected.
      >>
      >>drop function qaTmudfGeneral1 cascade;

      — SQL operation complete.
      >>drop function qaTmudfGeneral2 cascade;

      — SQL operation complete.
      >>drop library qaTmudfLib cascade;

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

      — SQL operation complete.

        Attachments

          Activity

            People

            • Assignee:
              hzeller Hans Zeller
              Reporter:
              WTsai Weishiun Tsai
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: