[
https://issues.apache.org/jira/browse/TRAFODION-1154?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14646446#comment-14646446
]
Hans Zeller commented on TRAFODION-1154:
----------------------------------------
The fix was checked in as part of
https://github.com/apache/incubator-trafodion/commit/8b59bb981efdc841aad59d4049e49626904adccf
> LP Bug: 1442774 - TMUDF: Compiling a TPCH query with TMUDF returns internal
> assertion in BaseTypes.cpp:118
> ----------------------------------------------------------------------------------------------------------
>
> Key: TRAFODION-1154
> URL: https://issues.apache.org/jira/browse/TRAFODION-1154
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Weishiun Tsai
> Assignee: Hans Zeller
> Priority: Critical
> Fix For: 2.0-incubating
>
>
> As an attempt to test TMUDF with sizable real-life data, TPCH queries are
> used for this exercise. The TMUDF are implemented using
> addPassThroughColumns() and copyPassThruData(), so that the output table is
> exactly the same as the input table. When running all tables through such a
> TMUDF in a TPCH query, the query result is also expected to be the same as
> the original query without TMUDFs.
> Most queries run fine except for this one. As shown in the execution
> output, x1 is the original query with TMUDF, and it compiles fine. x2 is the
> query with TMUDF, and the compiler returns internal assertion failure in
> BaseType.cpp, line 118:
> *** ERROR[2006] Internal error: assertion failure () in file
> ../common/BaseTypes.cpp at line 118.
> This is seen on the v0410 build installed on a workstation. It requires the
> QA tpch2x tables to reproduce it. Unfortunately, it requires the actual
> data, and is not reproducible with just an empty set of tpch2x tables. The
> python scripts to populate tpch2x tables on a workstation are available upon
> request. (To run the scripts will require DCS up and running with a minimum
> of 3 mxosrvrs).
> Once the tpch2x tables have been populated on the instance, 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>/qaTmu
> dfTest.so';
> --- SQL operation complete.
> >>
> >>create table_mapping function qaTmudfGeneral ()
> +>external name 'QA_TMUDF'
> +>language cpp
> +>library qaTmudfLib;
> --- SQL operation complete.
> >>
> >>set schema g_tpch2x;
> --- SQL operation complete.
> >>
> >>showddl part;
> CREATE TABLE TRAFODION.G_TPCH2X.PART
> (
> P_PARTKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , P_NAME VARCHAR(55) CHARACTER SET ISO88591
> COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , P_MFGR CHAR(25) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , P_BRAND CHAR(10) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , P_TYPE VARCHAR(25) CHARACTER SET ISO88591
> COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , P_SIZE INT NO DEFAULT NOT NULL NOT DROPPABLE
> , P_CONTAINER CHAR(10) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , P_RETAILPRICE NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , P_COMMENT VARCHAR(23) CHARACTER SET ISO88591
> COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (P_PARTKEY ASC)
> )
> SALT USING 8 PARTITIONS
> ;
> --- SQL operation complete.
> >>showddl supplier;
> CREATE TABLE TRAFODION.G_TPCH2X.SUPPLIER
> (
> S_SUPPKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , S_NAME CHAR(25) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , S_ADDRESS VARCHAR(40) CHARACTER SET ISO88591
> COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , S_NATIONKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , S_PHONE CHAR(15) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , S_ACCTBAL NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , S_COMMENT VARCHAR(101) CHARACTER SET ISO88591
> COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (S_SUPPKEY ASC)
> )
> SALT USING 8 PARTITIONS
> ;
> --- SQL operation complete.
> >>showddl lineitem;
> CREATE TABLE TRAFODION.G_TPCH2X.LINEITEM
> (
> L_ORDERKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_PARTKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SUPPKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_LINENUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_QUANTITY NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_EXTENDEDPRICE NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_DISCOUNT NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_TAX NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_RETURNFLAG CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_LINESTATUS CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_COMMITDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_RECEIPTDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPINSTRUCT CHAR(25) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPMODE CHAR(10) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_COMMENT VARCHAR(44) CHARACTER SET ISO88591
> COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (L_SHIPDATE ASC, L_ORDERKEY ASC, L_LINENUMBER ASC)
> )
> SALT USING 8 PARTITIONS
> ;
> --- SQL operation complete.
> >>showddl partsupp;
> CREATE TABLE TRAFODION.G_TPCH2X.PARTSUPP
> (
> PS_PARTKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , PS_SUPPKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , PS_AVAILQTY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , PS_SUPPLYCOST NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , PS_COMMENT VARCHAR(199) CHARACTER SET ISO88591
> COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (PS_PARTKEY ASC, PS_SUPPKEY ASC)
> )
> SALT USING 8 PARTITIONS
> ;
> --- SQL operation complete.
> >>showddl orders;
> CREATE TABLE TRAFODION.G_TPCH2X.ORDERS
> (
> O_ORDERKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , O_CUSTKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , O_ORDERSTATUS CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , O_TOTALPRICE NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , O_ORDERDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , O_ORDERPRIORITY CHAR(15) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , O_CLERK CHAR(15) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , O_SHIPPRIORITY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , O_COMMENT VARCHAR(79) CHARACTER SET ISO88591
> COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (O_ORDERKEY ASC)
> )
> SALT USING 8 PARTITIONS
> ;
> --- SQL operation complete.
> >>showddl nation;
> CREATE TABLE TRAFODION.G_TPCH2X.NATION
> (
> N_NATIONKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , N_NAME CHAR(25) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , N_REGIONKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , N_COMMENT VARCHAR(152) CHARACTER SET ISO88591
> COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (N_NATIONKEY ASC)
> )
> SALT USING 8 PARTITIONS
> ;
> --- SQL operation complete.
> >>
> >>select count(*) from part;
> (EXPR)
> --------------------
> 400000
> --- 1 row(s) selected.
> >>select count(*) from supplier;
> (EXPR)
> --------------------
> 20000
> --- 1 row(s) selected.
> >>select count(*) from lineitem;
> (EXPR)
> --------------------
> 11997996
> --- 1 row(s) selected.
> >>select count(*) from partsupp;
> (EXPR)
> --------------------
> 1600000
> --- 1 row(s) selected.
> >>select count(*) from orders;
> (EXPR)
> --------------------
> 3000000
> --- 1 row(s) selected.
> >>select count(*) from nation;
> (EXPR)
> --------------------
> 25
> --- 1 row(s) selected.
> >>
> >>prepare x1 from select
> +>nation,
> +>o_year,
> +>cast(sum(amount) as numeric(18,2)) as sum_profit
> +>from
> +>(
> +>select
> +>n_name as nation,
> +>extract(year from o_orderdate) as o_year,
> +>l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
> +>from
> +>part,
> +>supplier,
> +>lineitem,
> +>partsupp,
> +>orders,
> +>nation
> +>where
> +>s_suppkey = l_suppkey
> +>and ps_suppkey = l_suppkey
> +>and ps_partkey = l_partkey
> +>and p_partkey = l_partkey
> +>and o_orderkey = l_orderkey
> +>and s_nationkey = n_nationkey
> +>and p_name like '%maroon%'
> +>) as profit
> +>group by
> +>nation,
> +>o_year
> +>order by
> +>nation,
> +>o_year desc;
> *** WARNING[6007] Multi-column statistics for columns (L_PARTKEY, L_SUPPKEY)
> from table TRAFODION.G_TPCH2X.LINEITEM were not available. The columns were
> being used by Join operator. As a result, the access path chosen might not be
> the best possible.
> *** WARNING[6007] Multi-column statistics for columns (PS_PARTKEY,
> PS_SUPPKEY) from table TRAFODION.G_TPCH2X.PARTSUPP were not available. The
> columns were being used by Join operator. As a result, the access path chosen
> might not be the best possible.
> --- SQL command prepared.
> >>
> >>
> >>prepare x2 from select
> +>nation,
> +>o_year,
> +>cast(sum(amount) as numeric(18,2)) as sum_profit
> +>from
> +>(
> +>select
> +>n_name as nation,
> +>extract(year from o_orderdate) as o_year,
> +>l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
> +>from
> +>UDF(trafodion.mytest.qaTmudfGeneral(TABLE(select * from part))),
> +>UDF(trafodion.mytest.qaTmudfGeneral(TABLE(select * from supplier))),
> +>UDF(trafodion.mytest.qaTmudfGeneral(TABLE(select * from lineitem))),
> +>UDF(trafodion.mytest.qaTmudfGeneral(TABLE(select * from partsupp))),
> +>UDF(trafodion.mytest.qaTmudfGeneral(TABLE(select * from orders))),
> +>UDF(trafodion.mytest.qaTmudfGeneral(TABLE(select * from nation)))
> +>where
> +>s_suppkey = l_suppkey
> +>and ps_suppkey = l_suppkey
> +>and ps_partkey = l_partkey
> +>and p_partkey = l_partkey
> +>and o_orderkey = l_orderkey
> +>and s_nationkey = n_nationkey
> +>and p_name like '%maroon%'
> +>) as profit
> +>group by
> +>nation,
> +>o_year
> +>order by
> +>nation,
> +>o_year desc;
> *** ERROR[2006] Internal error: assertion failure () in file
> ../common/BaseTypes.cpp at line 118.
> *** ERROR[8822] The statement was not prepared.
> >>
> >>set schema mytest;
> --- SQL operation complete.
> >>
> >>drop function qaTmudfGeneral cascade;
> --- SQL operation complete.
> >>drop library qaTmudfLib cascade;
> --- SQL operation complete.
> >>drop schema mytest cascade;
> --- SQL operation complete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
